7 Replies Latest reply on Feb 13, 2017 3:10 PM by waldronmg

    FMP15 SELECT with JOIN causes SELECT * COUNT

    waldronmg

      When A SELECT is executed for 1 table, it retrieves records quickly (table has 500,000 records).

       

      When a JOIN is included for 2 tables, it creates a SELECT * COUNT ........ which causes the first page display to go from 3 seconds to 36 seconds.

       

      Trace shows this.

       

      I know everyone will want to know a lot of things, but I just have a basic question:

       

      Is there anyway to prevent the SELECT * COUNT when doing a JOIN?

       

      Thanks

       

      Mike

        • 1. Re: FMP15 SELECT with JOIN causes SELECT * COUNT
          beverly

          It may be beneficial to complete these:

          ====

          Please use this space to report issues with any products on the FileMaker Platform. Be sure to follow this template:

           

          Product and version (e.g. FileMaker Pro 14.0.3)

          OS and version

          Browser and version (for WebDirect only)

          Hardware

          Description

          How to replicate

          Workaround (if any)

          ====

           

          In addition: what script steps or calculations are you using and can you post screen shot(s)?

          • 2. Re: FMP15 SELECT with JOIN causes SELECT * COUNT
            waldronmg

            Beverly,

             

            I appreciate the need for the info you requested, but I only had a question.

             

            I have been testing 8 ODBC products over the past couple months.

            With the following scripts, 7 of the products score very high on SQL SELECT

            single table and SQL SELECT with Join.

            FMP 15, is the only one with response time problem.  THere are 500k records

            on the main file and the JOIN is one to one.

            All other products display a table list in around 3 seconds, FMP 15 is

            between 36 and 45 seconds.

             

            I found the problem to be the inserted SELECT * COUNT that I can't control

            and that was my question.

             

            I have to make my recommendation this week so I am short on time, here are

            2 scripts single/join.

             

            SELECT a.intr_intake_type,

                   b.work_last_name,

              b.work_first_name,

              a.intr_intake_dt,

              a.intr_intake_time,

              a.intr_screen_appr_dt,

              a.intr_intake_status

            FROM ADABAS_10_12_8_33.dbo.MW_int_report_flat as a   inner

              join ADABAS_10_12_8_33.dbo.mW_worker as b

                on a.intr_curr_worker_id = b.work_worker_id

              where a.intr_intake_type = 'ANE'

            limit 32

             

            SELECT a.intr_intake_type,

                a.intr_intake_dt,

                a.intr_intake_time,

                a.intr_screen_appr_dt,

                a.intr_intake_status

            FROM ADABAS_10_12_8_33.dbo.MW_int_report_flat as a

               where a.intr_intake_type = 'ANE'

            limit 32

             

            MW

            • 3. Re: FMP15 SELECT with JOIN causes SELECT * COUNT
              waldronmg

              PS I have taken out the LIMIT and the WHERE, results are the same.

               

              MW

              • 4. Re: FMP15 SELECT with JOIN causes SELECT * COUNT
                beverly

                FMP as SQL source?

                FMP looking at a SQL source?

                What are you comparing? FM is not a SQL database. A little more context on what you are doing (script & function IN fm), please?

                 

                Sent from miPhone

                • 5. Re: FMP15 SELECT with JOIN causes SELECT * COUNT
                  waldronmg

                  Simple question so I guess were done here.

                   

                  The work I am doing cannot be disclosed.

                   

                  Windows 7 to IBM Mainframe.

                   

                  Thanks for your assistance

                   

                  Mike Waldron

                  • 6. Re: FMP15 SELECT with JOIN causes SELECT * COUNT
                    beverly

                    not so simple, as you did not specify a FM query to an external SQL source (as appears below).

                     

                    nothing "revealed" is stating it's a SELECT query (using ESS or Import) to an ODBC source, using a particular driver.

                     

                    perhaps TS will contact you via PM to help you with "off-list" assistance?

                    beverly

                    • 7. Re: FMP15 SELECT with JOIN causes SELECT * COUNT
                      waldronmg

                      Okay.

                       

                      It's a 'import', odbc source, sign on to source and typed script (one above

                      with the JOIN), execute and accept records into new database.

                      Either with trace on or off.

                       

                      Thanks

                       

                      Mike