1 2 Previous Next 15 Replies Latest reply on Sep 18, 2015 1:26 AM by user19752

    Filemaker's SQL GROUP BY is unacceptably and pathetically slow !

    Vincent_L

      Hello,

       

      Here's a very simple file, 29000 records of 4 fields (all fully indexed)

       

      Dropbox - slow_products_to_cat.zip

       

      The only think that solution does is

       

      ExecuteSQL("SELECT Product_ERP from fast_products_to_cat GROUP BY Product_ERP";"";"")

       

      My Macbook Pro Retina takes 4 minutes 50 seconds !

      My 2010 Core i7 Macbook Pro : 7 min 48 seconds

       

      This is pathetic. FMI should fix this, as well as many thinks in their FQL implementation which needs a complete rewrite imho).

       

      P.S : This test solution is derived from actual use. Default log pass : Admin / blank

      FMPa 14v2 Mac

        • 1. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
          erolst

          Maybe the implementation is slow, maybe it isn't – but why are you using GROUP BY anyway?

           

          The only thing you seem to want to “aggregate” is the text in Product_ERP, and you can do that much faster with

           

          ExecuteSQL ( "

            SELECT DISTINCT Product_ERP
            FROM fast_products_to_cat

            " ; "" ; ""

          )

           

          which on my 2010 i5 Mac mini takes about 6 seconds.

          • 2. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
            Vincent_L

            Thanks, but this is an extreme  simplification of a real world case, where i use Group by to count

            GROUP BY is very usefull and use in many queries. It shouldn't be slow.

             

            This is more like what I need (but my query is even more complex than that)

             

            SELECT Product_ERP,count(*) from fast_products_to_cat GROUP BY Product_ERP


            Witch takes 8"27 on My 2010 Mac

            • 3. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
              CamelCase_data

              ExecuteSQL certainly has some serious performance issues, but I'm not sure this is the best example, since the SQL isn't optimized. I believe you can get the exact same result by using:

              ExecuteSQL( " SELECT DISTINCT ( Product_ERP ) from fast_products_to_cat";"";"")

              which takes 1 second.


              • 4. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                Vincent_L
                ExecuteSQL certainly has some serious performance issues

                 

                Exactly, my point is to highly those in broad light, cause it's so slow it becomes unusable in many case (in other case it's very helpful) but we shouldn't accept that. Group by is such a useful function that fixing it would arrange a lot of things.

                The fact that SQL is so badly optimized is a serious issue.

                • 5. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                  jrenfrew

                  As FQL is a meta-layer above the FileMaker Draco engine, why not use the tools you already have to build a relationship to do the same thing?

                  Yes, we get to see table values out of context, but no one who has written seriously about it has not done so without saying that there are places where the speed of results outweighs the generic usefulness.

                  • 6. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                    Vincent_L

                     

                    As FQL is a meta-layer above the FileMaker Draco engine, why not use the tools you already have to build a relationship to do the same thing?

                     

                     

                    Well, yes, I know that FQL is a layer above Draco.

                    And I regret it. I think Draco2 should be a layer above SQL. If that was the case in FMP 15/16 is would be incredible for the platform, speed will be finally there, with a slew of other niceties. Unfortunately, as the tepid evolution of filemaker features shows, I think that FMI nowadays is incapable to do bold moves, and that would kill the platform. The last great update was FMP 7. Since then, they'll lost it (and if i recall correctly FM7 architect was axed or leaved).

                     

                    Back to the case why I wanted to use FQL in the first place :

                     

                    - Contextless : want to be able to use same code with any table

                    - It's overly complicated to do the same thing with traditional relationship, + unstorred calcs + looping scripts whereas it's possible with one FQL query. Moreover it's slow itself because of unstirred calc needed (whereas FQL let you only deal with stored values if you want, but of course it's dreadful implementation kills that advantage).

                     

                    But anyways, the point of this thread is to give a reproductible case showing the insanely lame speed of FQL when dealing with queries beyond select from. Here we've GROUP BY, which FMI implemented, but this implementation is so slow that it's shameful and mostly useless. Having a feature so slow shouldn't be acceptable, and should be classified as a bug.

                    • 7. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                      carlosilvia0

                      I agree that the query into FileMaker are not performing compared to other db, such as WS access. But try to create a summary field = Count of Product_ERP and a new format with a sub-summary by Product_ERP. Enter the fields Product_ERP and  count_product_ERP. Then Order By Product_ERP. The time to generate the list with the groupings is a few seconds .... this is a great time!

                      • 8. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                        Vincent_L

                        Well, thanks for the tip. But may I say that few seconds, which are indeed much better, is not that great for only 29000 records. In MySQL that's milliseconds.

                         

                        Besides, the actual solution needs a more complex query, that's hard to replicate in standard draco, in which the group by creates much slowness.

                         

                        That below takes 9 seconds, for 1 item, and there's 1000 items, so it would take 9000 seconds to get the result I want

                         

                        SELECT C.caract_id,C.caract_id_as_attribute,T.caract_id,P."caract_value_export just french Cache", count(*) FROM caract_to_caract C

                        JOIN caracteristics_tree T ON C.caract_id_as_attribute=T.parent_caract_id OR C.caract_id_as_attribute=T.caract_id

                        JOIN products_to_caracteristics_FMP P ON P.caract_id=T.caract_id

                        JOIN products_to_caracteristics_FMP P2 ON P.product_erp=P2.product_erp

                        WHERE C.caract_id IN(4601) AND P.caract_id=T.caract_id AND P2.caract_id=C.caract_id GROUP BY C.caract_id,C.caract_id_as_attribute,T.caract_id,P."caract_value_export just french Cache" Having count(*)>1

                         

                        But resolving this is not the goal of that thread.

                        • 10. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                          carlosilvia0

                          The seconds were compared to 4 and 7 minutes.

                          Filemaker is not a native sql db and is certainly slower than others such as MySQL, SQL Server, PostGreSQL, Oracle, ..

                          However I using the following script and I measured a time of about 13 seconds.

                          snip_20150913014725.png

                           

                          These times can be further reduced by executing the script on the server by using the "Perform script on the server."

                          • 11. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                            Vincent_L

                            Hi Carlos,

                            Thanks but it's more problematic than just getting the count of. It's a SQL function that's pretty useless. And this preclude many useful query to be used.

                            For the count, I'm pretty sure that a ListOf + a javascript function called with Base Element plugin 3.1 would be the fastest.

                             

                            P.S : Your script would perhaps be faster with a the infamous Freeze script step, and on a Blank form layout. Because sadly, FM is still coupled with the graph engine, which is nonsense (just legacy code)

                            • 12. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                              carlosilvia0

                              Hi, I agree with you ... I hope the next version introduces these improvements. P.S. you mentioned BaseLements plugin 3.1, have you ever used the function BE_SMTP_Server?

                              • 13. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                                gdurniak

                                Is this documented anywhere ?

                                 

                                It was explained to me that the SQL engine was completely separate, and not a "layer", and therefore might have different performance

                                 

                                greg

                                 

                                > As FQL is a meta-layer above the FileMaker Draco engine, why not use the tools you already have to build a relationship to do the same thing?

                                • 14. Re: Filemaker's SQL GROUP BY is unacceptably and pathetically slow !
                                  user19752

                                  I agree this IS bug.

                                  Adding WHERE clause to limit source records,

                                  WHERE cat_id < 100

                                  results 3 seconds on 8842 records.

                                  WHERE cat_id > 0

                                  results 9 minutes on 29395 records.

                                  Taking over 180 times for under 4 times of records shows bug on there, it should be at most about 4^2=16 times.

                                  (3 seconds can be bad performance of FM, but when the bug will fixed, it can be faster.)

                                   

                                  I thought SQL engine don't use index is the cause, but changing index to unicode had no effect. (And changing index to unicode takes only a few seconds, if there is not, SQL engine should make it temporary.)

                                  1 2 Previous Next