1 2 Previous Next 23 Replies Latest reply on Aug 13, 2016 12:42 PM by gdaquino

    How to speed execute sql on a very large recordset?

    gdaquino

      Hello experts,

       

      Is there a way to run an execute sql on a very large recordset faster?

       

      I'm now working on a remote db on a server and the main table is half a million records. Is there a way to let this sql be faster?

       

      Thanks,

        • 1. Re: How to speed execute sql on a very large recordset?
          beverly

          Please specify:

          Execute SQL (script step)

          or

          ExecuteSQL (function)

           

          Post your query here (calculation and/or script)

           

          Sent from my miPhone

          • 2. Re: How to speed execute sql on a very large recordset?
            gdaquino

            Oh! Ok: ExecuteSQL (function)

             

            select o.article_nr, o.qty

            from ordersline o

            inner join customers c

                on o.customerid = c.customerid

             

             

             

            • 3. Re: How to speed execute sql on a very large recordset?
              gdaquino

              I'm running it and it's taking a lot!

              • 4. Re: How to speed execute sql on a very large recordset?
                twelvetens

                ...Also please specify the data source for the ExecuteSQL Statement - especially if it's an external data source - also useful to know the total number of records in the tables being referenced - not just the number of records being found....

                • 5. Re: How to speed execute sql on a very large recordset?
                  gdaquino

                  Ok,

                   

                  The ds is external and is based on a remote connection on a db put on a server.

                   

                  on orderlines there are almost 460k records

                  and on customers 6000.

                  • 6. Re: How to speed execute sql on a very large recordset?
                    twelvetens

                    In my (humble) experience, querying an external data source of that size can indeed be quite slow (depending on a number of factors, both within the filemaker environment and the external data source environment).

                     

                    Could you post the ExecuteSQL statement you're using?

                     

                    Is the remote data source SQL / MySQL? If so, do you need to query against all 640K records? Could ask the DB admin to create a 'view' of the subset of records you need to query against, and then use that as a source?

                     

                    Also, just to clarify, are you using the ExecuteSQL calculation, or the Execute SQL script step (note the space)...?

                    • 7. Re: How to speed execute sql on a very large recordset?
                      beverly

                      There is no context with the ExecuteSQL (or eSQL as I call it!) function. However, I'm assuming you are performing the evaluation based on the current record (even if every record!)

                       

                      So, rather than JOIN, I would do this (in Customers):

                       

                      ExecuteSQL( " SELECT article_nr, qty

                      FROM ordersline

                      WHERE customerid = ? "

                      ; "" ; ""

                      ; Customer::customerID )

                       

                      That way you are passing the current record customerID and not JOINing in the query.

                       

                      This is the SAME as the relationship (on the graph)

                      Customer::customerID = ordersline::customerID

                      and showing a portal of the relationship with the two fields: article_nr, qty. By default the relationship (JOIN) is an 'outer join', meaning that blank rows can be in the portal if there are no related ordersline.

                       

                      And since you are not returning any customerid, you don't really need an INNER JOIN in the query.

                       

                      May I ask why you are using the eSQL query for this data instead of the portal relationship? are you "gathering the data" for a report or export in a way that the article_nr and qty can be used (as a block of TEXT result, remember from eSQL)?

                       

                      I would never have this as a calculation as it needs to recalc every time the result field is shown. Rather, I would have a TEXT field and script setting it with the eSQL result when needed.

                       

                      The advice above would help speed the eSQL on large datasets. To recap:

                      1. remove the JOIN, add the parameter

                      2. script with Set Field, instead of calculate

                      beverly

                      3 of 3 people found this helpful
                      • 8. Re: How to speed execute sql on a very large recordset?
                        Mike_Mitchell

                        To add to Beverly's excellent advice, be sure the record is in a committed state when you fire the query. When you do this, the query is processed on the server and only the result is returned. OTOH, if you have any open records, FileMaker will send all records down from the server to the client and process the query there. Over a remote connection, that's going to be really slow with 460K records.

                        2 of 2 people found this helpful
                        • 9. Re: How to speed execute sql on a very large recordset?
                          beverly

                          If external SQL, can you have a 'view' that returns this as you may need it? Let the SQL server do the work!

                          • 10. Re: How to speed execute sql on a very large recordset?
                            philmodjunk

                            With FileMaker's current architecture, ExecuteSQL is not going to be particularly fast. FileMaker's system translates the SQL into equivalent instructions and then executes them to produce the result.

                             

                            Since this is a very simple query, using a non SQL method, and thus eliminating the "translation" step may be worth testing on your set up to see if it has better performance.

                            • 11. Re: How to speed execute sql on a very large recordset?
                              beverly

                              Right!

                              • if native FM functionality is possible, use it rather than ExecuteSQL().
                              • If the source is external (SQL), try to get a view (pre-set query) to return the result - let the SQL server do the work!
                              • if ExecuteSQL() is used for a good reason, there are ways to try and optimize.

                               

                              beverly

                              • 12. Re: How to speed execute sql on a very large recordset?
                                wimdecorte

                                philmodjunk wrote:

                                 

                                With FileMaker's current architecture, ExecuteSQL is not going to be particularly fast. F

                                 

                                I want to be a little more nuanced than this.  ExeucteSQL() can be blazingly fast even on really big tables.

                                See my test file from Devcon a few years ago:

                                Using ExecuteSQL() calls in FileMaker - Soliant Consulting

                                 

                                A SELECT query on a 1.5 million record table hosted in the cloud:

                                less than a 3rd of a second for the first run, down to a tenth of a second for subsequent runs.  I would call that fast, for a context-free way of querying.  Obviously it is even faster on the LAN.

                                 

                                2016-08-12_08-41-22.png

                                 

                                The things that will slow ExecuteSQL() down are:

                                - open records by the client's session in the target able (that's the point of the demo, try it - it's jaw-dropping)

                                - complex queries with multiple JOINs and  SQL functions (very often that complexity is not needed in the SQL call and can be handled differently)

                                6 of 6 people found this helpful
                                • 13. Re: How to speed execute sql on a very large recordset?
                                  fmpdude

                                  Sure, just add an index!

                                   

                                  In MySQL with a recent test, I reduced the query time from 2 seconds to 0.09 seconds with a simple b-tree index. The same basic query in FMP took almost 8 seconds!

                                  1 of 1 people found this helpful
                                  • 14. Re: How to speed execute sql on a very large recordset?
                                    twelvetens

                                    Thanks for that Wim, haven't seen that file before - very enlightening! I presume that the performance penalty of having an open record in the target table when the table source is an external data source would be even more pronounced, which is certainly what I've seen whilst connected to MySQL sources over ODBC / ESS.

                                    1 2 Previous Next