1 2 Previous Next 27 Replies Latest reply on Jul 2, 2014 9:17 AM by DamianKelly

    FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?

    Vincent_L

      Hi,

       

      This is fast, 0,9s

       

      SELECT DISTINCT A."key" FROM checklogs_records A

      JOIN products_to_categories B

      ON A."key"=B.Product_ERP and B.cat_id IN (4,412) AND B.master=1

      LEFT OUTER JOIN products_matching C

      ON B.Product_ERP=C.product_erp

       

      WHERE

      A.d_val001='28/06/2014' AND

      A.checklog_type_id=5

       

       

      This is super slow, 226 s

       

      SELECT DISTINCT A."key" FROM checklogs_records A

      JOIN products_to_categories B

      ON A."key"=B.Product_ERP and B.cat_id IN (4,412) AND B.master=1

      LEFT OUTER JOIN products_matching C

      ON B.Product_ERP=C.product_erp AND C.matching_strength=1 AND C.flux_id=1

       

      WHERE

      A.d_val001='28/06/2014' AND

      A.checklog_type_id=5

       

       

      The fields C.matching_strength=1 AND C.flux_id=1 are totally indexed. In fact, all the fields involved are indexed

       

      I don't understand at all why is so much slower, any ideas ?

        • 1. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
          DamianKelly

          Two ideas, both punts though:

           

          Try using a field instead of the constant 1. So a calc number field with the formula 1

           

          SELECT DISTINCT A."key" FROM checklogs_records A

          JOIN products_to_categories B

          ON A."key"=B.Product_ERP and B.cat_id IN (4,412) AND B.master=1

          LEFT OUTER JOIN products_matching C

          ON B.Product_ERP=C.product_erp AND c.matching_strength=b.C_1 AND C.flux_id=b.C_1

           

          WHERE

          A.d_val001='28/06/2014' AND

          A.checklog_type_id=5

           

          Second move the join clauses into the WHERE clause although this does subtly change the results of the query

           

          SELECT DISTINCT A."key" FROM checklogs_records A

          JOIN products_to_categories B

          ON A."key"=B.Product_ERP and B.cat_id IN (4,412) AND B.master=1

          LEFT OUTER JOIN products_matching C

          ON B.Product_ERP=C.product_erp

           

          WHERE

          A.d_val001='28/06/2014' AND

          A.checklog_type_id=5 AND

          c.matching_strength=1 AND

          C.flux_id=1

          • 2. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
            beverly

            Did you try putting the extra matches in the WHERE clause instead in the ON?

             

            I've never used more than the primary match in ON and put the rest of the criteria in the WHERE (including with MS SQL & MySQL).

             

            Just a thought & may or may not make a difference - your database, you have to test.

             

            Just curious. If you set this up as relationship(s) in the graph, what would it look like? And/or would some of the criteria actually be in a portal's filter calc? And yes, the point of eSQL is to avoid the graph - but sometimes it's the best way and sometimes the graph (+/- filters) is the best (i.e. fastest).

             

             

            -- sent from myPhone --

            Beverly Voth

            --

            • 3. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
              Vincent_L

              Yes, in the Where it's fast. But that's not at all the result I want.

               

              I want all products taht are in A AND B, plus their status in C

               

              so

               

              SKU          C.Status

               

              Prod 1     1

              Prod 2    NULL

              Prod 3    1

               

              If I put the condition in the where, I only get Prod 1 & Prod 3.

              • 4. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                DamianKelly

                Yeah I guessed that would be the case.

                 

                Are the matching strength and flux_id fields both numbers? Wondering if its a casting issue?

                 

                Damian

                • 5. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                  Vincent_L

                  Ok, now I think there's something really wrong, a BUG ?

                   

                   

                  SELECT DISTINCT A."key" FROM checklogs_records A

                  JOIN products_to_categories B

                  ON A."key"=B.Product_ERP and B.cat_id IN (4,412) AND B.master=1

                  LEFT OUTER JOIN products_matching C

                  ON B.Product_ERP=C.product_erp AND 1=1

                   

                  WHERE

                  A.d_val001='28/06/2014' AND

                  A.checklog_type_id=5

                   

                  Is as slow as with my condition. So If there more than one criterai the LEFT OUTER JOIN goes nuts. I can't explain that. Seems like a bug to me.

                   

                  BTW : I tried recovering and droping index, It wasn't currupt and didn't change anything.

                  • 6. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                    Vincent_L

                    I just create a file from scratch to be able to reproduce the problem.

                    The database is fully open with guest account, you can snoop into it. It's very simple

                     

                    https://www.dropbox.com/s/kt8ykt8783se7qy/QueryTesting.zip

                     

                    In that testing solution

                     

                    This takes 37s

                     

                    SELECT A.product_id FROM products A

                    LEFT OUTER JOIN competitors_prices B

                    ON A.product_id=B.product_id AND B.matching=1 AND B.flux_id=1

                     

                     

                    This takes 33s

                     

                    SELECT A.product_id FROM products A

                    LEFT OUTER JOIN competitors_prices B

                    ON A.product_id=B.product_id AND 1=1

                     

                    This takes 0,07s

                     

                    SELECT A.product_id FROM products A

                    LEFT OUTER JOIN competitors_prices B

                    ON A.product_id=B.product_id

                     

                    Of course everything number and indexed

                     

                    I guess there's a serious problem here

                     

                    I only tested in 13, if someone could test it in 12, that'd be nice. There's no 13 only feature in this file

                    Thanks

                     

                    Edit : Tried on 12, same thing

                    • 7. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                      wimdecorte

                      Probably not a bug, just that the db engine or the function is not optimized for much complexity.  Simple SELECTs are blazingly fast but as yo add more complexity (such as multiple joins, complex joins, using sql functions,..) performance drops off a cliff.

                       

                      Sometimes you can improve the performance by combining multiple SELECTs and some plain old FM processing.

                      • 8. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                        beverly

                        Ah? You need an OR?

                         

                        -- sent from myPhone --

                        Beverly Voth

                        --

                        • 9. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                          erolst

                          Beverly Voth wrote:

                           

                          Ah? You need an OR?

                           

                          Would you lend him your OR?

                          • 10. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                            beverly

                            Ok, I think because you are doing OUTER JOIN, the "NULL" record is not included. You may have to test for something in the field OR IS NULL.

                            • 11. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                              TimDietrich

                              Vincent --

                               

                              I've run into this kind of thing before, and it can be very frustrating.

                               

                              From what I've been able to tell, when FileMaker builds a plan to resolve the query, it doesn't always do so in the most optimal and efficient way. In some cases, it appears to completely ignore indexes. And I think that's what you are seeing here.

                               

                              What you can do is to try to make FileMaker's job as easy as possible when it comes to resolving the query. For example, in your sample database, you are using this query:

                               

                              SELECT A.product_id FROM products A

                              LEFT OUTER JOIN competitors_prices B

                              ON A.product_id=B.product_id AND B.matching=1 AND B.flux_id=1

                               

                              When I run that query, I get 1755 rows returned in 33.5s.

                               

                              This modified version of the query moves some of the join criteria to the WHERE clause (and does it in a way that your LEFT OUTER JOIN criteria are enforced):

                               

                              SELECT A.product_id FROM products A

                              LEFT OUTER JOIN competitors_prices B

                              ON A.product_id=B.product_id

                              WHERE (B.matching IS NULL or  B.matching=1 )

                              AND ( B.flux_id IS NULL or B.flux_id=1 )

                               

                              With that query, I get back 1755 rows in .103s.

                               

                              Going back to the original query that you posted, you could rewrite in a similar way:

                               

                              SELECT DISTINCT

                                        A."key"

                              FROM

                                        checklogs_records A

                                        JOIN products_to_categories B

                                                  ON ( ( A."key" = B.Product_ERP ) AND ( B.cat_id IN (4,412) ) AND ( B.master=1 ) )

                                        LEFT OUTER JOIN products_matching C

                                                  ON ( B.Product_ERP = C.product_erp )

                              WHERE

                                        ( ( C.matching_strength IS NULL ) OR ( C.matching_strength = 1 ) )

                                        AND ( ( C.flux_id IS NULL ) OR ( C.flux_id = 1 ))

                                        AND ( A.d_val001='28/06/2014' )

                                        AND ( A.checklog_type_id=5 )

                               

                              So what we're doing is making it easy for FileMaker to resolve the joins, and then moving as much os the logic to the WHERE clause as possible. From what I've been able to tell, the less complicated joins are resolved using indexes, and are therefore much faster. FileMaker then applies the WHERE clause to filter the result.

                               

                              I hope this helps.

                               

                              -- Tim

                               

                               


                              1 of 1 people found this helpful
                              • 12. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                                Vincent_L

                                Thanks A LOT Tim, that's awesome.

                                 

                                It will probably make my solution work, but I'll have to be very focussed because actually, those examples are just half of the query. It involves 5 tables, with the 3 first in join, and the other in outer join. But I think your clever trick will nail it. Thanks again.

                                 

                                As a side note, I feel that this terrible performance loss is either not normal and/or not acceptable. At the very least, it should be written in the documentation. Therefore I'll report it as an issue, as it's one : I spend way too much time to diagnose this, doing something the documentation didn't warn me against it, and hence that was perfectly logic and had no predictable ill effects.

                                Not to mention it adds considerable complexity.

                                • 13. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                                  TimDietrich

                                  I'm glad the technique helped. If you need additional help when it comes to applying the technique to your larger query, let me know.

                                   

                                  I agree that sometimes FQL can be frustrating. As someone who comes from a heavy SQL background, there are times where I want to do something that is extremely easy in SQL Server or Oracle, but impossible in FQL. And other times, the speed (or lack thereof) of even the most basic query can be surprising.

                                   

                                  Back in October I blogged about some "weirdness" that I was seeing with a query for a maximum value, and how it seemed like FQL wasn't using an index to resolve it. In another example, it did seem to use an index to resolve a "distinct" query. You can read about the weird behavior that I was seeing - and how I resolved it - here: http://www.timdietrich.me/blog/filemaker-executesql-weirdness/

                                   

                                  What I have learned from using ExecuteSQL is that you sometimes have to help FQL do what it does - just as we're doing with the technique that I posted earlier. It can take time to do that, but the results are typically worth the effort.

                                   

                                  In any case, I'm still thrilled that we have ExecuteSQL available to us. I can honestly say that it has been a game changer for me, and there are some projects that I've worked on that, without ExecuteSQL, would have been much more difficult to complete.

                                   

                                  -- Tim

                                  • 14. Re: FQL LEFT OUTER JOIN very very slow if more than one criteria in the JOIN ?
                                    Vincent_L

                                    In any case, I'm still thrilled that we have ExecuteSQL available to us. I can honestly say that it has been a game changer for me, and there are some projects that I've worked on that, without ExecuteSQL, would have been much more difficult to complete.

                                     

                                    -- Tim

                                     

                                    I completly agree, this is absolutely a game chnging tech. I'm just gestting started. For that projet that lead to this post, I started doing the script the regular way, it would have make me drw 5 relationship, and at least 2 scripts with lots of loops. I realize I could get everything with one, moderably, complex query.

                                     

                                    The sad part is that due to that slowness I encountered, I ultimately ended up wasting a colossal amount of time, much more than creating the script the old way. But that's for learning.

                                    I just hope my experience will help more people to use FQL, and since FQL is new, I think it's our reponsibility to tell FMI ou much it's important, usefull, and needs attention, optimisation.

                                    1 2 Previous Next