1 2 Previous Next 16 Replies Latest reply on Jan 14, 2014 10:14 PM by user19752

    ExecuteSQL is slow - or is it me?

    davehob

      I'm putting a "spotlight search" field on a layout. The layout is based on a table of Sessions, related to Programmes, which is related to Programme Titles. The search will eventually be on several fields - but for testing, I'm just using the "title" field in Programme Titles. It's a text field, indexed.

       

      I'm hoping to use Execute SQL function, and I've got it working, but very slowly. To do the query on 24,000 (approx) Session records, it's taking 8 seconds, which is a lot slower than I hoped for. Or am I being over-optimistic about what I hoped for? (An equivalent Filemaker Find is a bit quicker, but not much - 10% quicker on average).

       

      This is the ExecuteSQL function that I'm using:

       

      ExecuteSQL("SELECT S.id FROM SSN s, PGM p, PTI t

      WHERE s.id_PGM = p.id

      AND p.id_PTI = t.id

      AND LOWER(t.title) LIKE ? " ; "" ; ""; SSN::FILTERINPUT & "%" )

       

      (where SSN, PGM and PTI are the Sessions, Programmes and Programme Titles tables, and SSN::FILTERINPUT is the global field in which the user enters the search string.)

       

      I would love to be told that I'm doing something wrong (I'm still on the ExecuteSQL learning curve).

       

      Dave.

        • 1. Re: ExecuteSQL is slow - or is it me?
          wimdecorte

          Make sure you have no uncommitted records at the time you do the search.  Otherwise FMS will send you ALL the data.

           

          Also, the use of LOWER() is going to slow things down.  If you want to see the speed difference, try it without it (perhaps on a temp field that has the data already in lowercase so that you'll get the same found set)

          • 2. Re: ExecuteSQL is slow - or is it me?
            LSNOVER

            Also, I hate to say, but Filemaker is often slow when doing cross relation searches.  It may be worth denormalizing your data bit if possible to get things up to speed.   LIKE searches are typically slower as well (in conjunction with the Lower() function Wim mentioned, this may be reducing your apps ability to use indexes.

            • 3. Re: ExecuteSQL is slow - or is it me?
              beverly

              I'm with Lee on this. Sometimes 'denormalized' is the fasted way to go with FileMaker. As long as you are aware of why lookup or copied data exists in more than one table, then you can use this method effectively.

               

              + if doing a native FM find is faster than eSQL, I'm prone to do that, too.

              Beverly

              • 4. Re: ExecuteSQL is slow - or is it me?
                DavidJondreau

                Is a properly structured ExecuteSQL() statement ever ever significantly faster than a properly structured Find?

                 

                The same data has to come down from the server either way.

                • 5. Re: ExecuteSQL is slow - or is it me?
                  beverly

                  if it's slower and the avoidance of additional relationships is the goal, then is it really slower to use eSQL?

                   

                  Beverly

                  • 6. Re: ExecuteSQL is slow - or is it me?
                    LSNOVER

                    My guess is no.  It adds an extra layer to what Filemaker is already doing.

                     

                    The benefits are in aggregation, and getting values from tables that don't have established relationships in the graph.   The downside a slight speed penalty.  Keep in mind that with grabbing aggregate data, you can maybe see a benefit over traditional Filemaker scripts because you can often reduce the number of steps to get your final answer with SQL.  Sometimes the syntax to get an answer is "easier" in SQL (at least if you know SQL), the the comparable steps and functions in traditional Filemaker.   Your mileage WILL vary. 

                     

                    For smaller data sets and simple queries, the performance hit is negligible most of the time.  For more complicated queries and larger data sets, the hit gets more noticable. 

                     

                    Cheers!

                    Lee

                    • 7. Re: ExecuteSQL is slow - or is it me?
                      wimdecorte

                      The benefit is in flexibility.  It allows me to write generic "fetch" scripts that work from every context.  So it allows for faster development and ultimately a cleaner, leaner graph.

                      So the speed difference may not be in the one query equivalent but it is definitely there.

                      • 8. Re: ExecuteSQL is slow - or is it me?
                        davehob

                        Thanks for the helpful responses.  I must say, I'm disappointed.  When I first looked into ExecuteSQL, I anticipated super-fast querying and minimalist relationship graphs, but realise now that my expectations were unrealistic (although Wim has explained how any-context fetching is useful a because of its flexibility).

                         

                        I've tried Wim's suggestions, i.e. made sure that everything was committed before the query, and removed the LOWER bit, and the performance improvement was about 10%.

                         

                        I then "denormalised" the data, i.e. duplicated the "title" field in the SSN table, and, of course, the performance improvement was huge.  1 second (which I suspect was > 0.5 and < 1) instead of 6-7 secs.

                         

                        So I guess that's the way to go, i.e. for fields that I need to search on, duplicate relevant data as indexed fields in the parent table, make sure they're updated when the related field changes (via scripted updates?), and use that data, either for eSQL or Find.  I have to say that the duplication bit feels wrong, but I think I realise now that I was using the tool in the wrong way in this context.

                         

                        Thanks again,

                         

                        Dave.

                        • 9. Re: ExecuteSQL is slow - or is it me?
                          LSNOVER

                          Hey Dave:

                           

                          Denormalization always feels wrong, but it's a fact of life in the real world, even in "big time" dbs like Oracle and SQL Server.  ;-)

                           

                          Don't feel too bad.   eSQL has it's place, it's just not a panacea for everything.

                           

                          It's still a very new features, so hopefully it will age like fine wine and get better over time.

                           

                          Regards,

                          Lee

                          • 10. Re: ExecuteSQL is slow - or is it me?
                            greglane

                            Hi Dave,

                             

                            Lots of good advice above and denormalizing could be the answer, but you may be able to optimize your query a bit without creating redundant data. For this particular query, if the PTI table is relatively small, a subselect will likely perform much better.

                             

                            SELECT S.id FROM SSN s, PGM p

                            WHERE s.id_PGM = p.id

                            AND  p.id_PTI IN (SELECT t.id FROM PTI t where LOWER(t.title) LIKE ?)

                             

                            Greg

                            • 11. Re: ExecuteSQL is slow - or is it me?
                              PeterWindle

                              I've been building databases a long time now, but never anything beyond FileMaker Pro, can someone please explain what is meant by normalise and de-normalise data? (apologies for the ignorance)

                              • 12. Re: ExecuteSQL is slow - or is it me?
                                Malcolm

                                I've been building databases a long time now, but never anything beyond FileMaker Pro, can someone please explain what is meant by normalise and de-normalise data? (apologies for the ignorance)

                                 

                                It's a concept that is aimed at reducing the repetition of data. In FileMaker it is possible to use field repetitions, field repetitions are a good example of non-normalised data.  In a typical invoice solution you have a table for invoices and a table for line items. This is a good example of normalised data.

                                 

                                I've seen invoice solutions created used repeating fields. The last one I saw allowed 14 line items (defined in the repeating field) because that fit nicely on a single page print out. If they needed more than fourteen lines they created another invoice! Each time you have a 1:n relationship you can see an opportunity for normalisation.

                                 

                                A lot of the theory of normalisation has been developed around relational databases which use the SQL language. This raises a number of issues for FileMaker users because the theory of normalisation is based on ideas of redundancy and re-use which reflect the definition of relations and the tool set of the SQL language. In the FileMaker world things are done differently. It is a different language, it has different tools and provides different constructs.

                                 

                                A highly normalised data structure can be a nightmare in FileMaker, though SQL query has changed some of that. Even so, FileMaker is not an SQL database and the application of the theory has to be done with FileMaker's own strengths and weaknesses uppermost.

                                 

                                Malcolm

                                • 13. Re: ExecuteSQL is slow - or is it me?
                                  davehob

                                  Greg,

                                   

                                  Thanks for this suggestion. Using the subselect is quicker, at least when I enter a long search string, but only a bit (about 1 sec quicker).  And it's slower (in fact much slower - 14-20 secs compared with 7), when I enter just a couple of characters.  I kind of understand why (fewer records to fetch with a longer string?), but don't see why the disparity should be that much greater when using the subselect.

                                   

                                  Thanks again for the suggestion.

                                   

                                  Dave.

                                  • 14. Re: ExecuteSQL is slow - or is it me?
                                    gdurniak

                                    my guess is that the sub select in not indexed

                                     

                                    just curious ... have you tried doing this in a "real" database, e.g. mySQL,  to see how fast your query might be ?

                                     

                                    greg

                                     

                                    > but don't see why the disparity should be that much greater when using the subselect.

                                    1 2 Previous Next