14 Replies Latest reply on Nov 8, 2015 3:08 PM by CarlSchwarz

    SQL vs native filemaker

    CarlSchwarz

      I've noticed more and more developers using SQL in Filemaker.

      Is there an advantage to using SQL to just doing everything in native Filemaker?  I've been developing with Filemaker for about 15 years now and I can't see the need to use it.  I can see that people with experience in SQL might turn to it for there needs, but for an experienced Filemaker developer who knows there way around Filemakers relational model is there any benefit?  Does it cut down on development times?  Is there a performance gain in some circumstances? Or is it about structure and readability?

       

      If there is a benefit to SQL could I please have some examples?

        • 1. Re: SQL vs native filemaker
          Mike_Mitchell

          The primary advantage to SQL in FileMaker space is not adding "one off" TOs to your Relationships Graph. Graphs can become cluttered, and if you only need to fetch something for one particular function, it can be tedious to add several TOs whose only purpose in life is a single function.

           

          Sometimes, there can be advantages in being able to fetch a particular record set in a format that makes it easy to generate, say, a Virtual List. It's very easy to parse out the results of an ExecuteSQL query into a Virtual List, so that can be convenient. It's also convenient to, say, create a quick HTML table (where you use HTML tags as delimiters on the result).

           

          Another area where using SQL shines in in DRY coding. Since the query can be abstracted, you can write one script than that can perform a huge variety of tasks by simply passing in parameters. That's something that's really hard to do on the Graph. In situations where you have a similar function to perform in many areas of the solution, being able to abstract it and repeat the code just by changing a parameter is a maintainability boon.

           

          There generally is no performance advantage. In fact, in many cases, ExecuteSQL is slower than the comparable native functionality. That's because FileMaker's internal query engine (Draco) runs something called FQL, which is a binary language. That means every SQL query has to be translated into FQL before it can be executed, putting SQL at a disadvantage. Some native functions are considerably faster than SQL (for example, if you build a Value List, it's stored and will render many times faster than running the SQL query).

           

          As far as development time, that depends on the developer.      Some developers are very comfortable in SQL space, so they can bang out SQL faster than their native spoken language. Others have difficulty figuring out even simpler SQL queries. So the answer is, "It depends."

           

          HTH

           

          Mike

          • 2. Re: SQL vs native filemaker
            beverly

            Carl, Mike has explained well. I'll give you an example of where "sql" would help in one solution I have for a local Scout application:

             

            I have a table 'CONTACTS' with a one-to-many relationship to 'Contacts_Positions' as each contact can fulfill many positions. This positions "join" table also has a many-to-many relationship to 'UNITS'. Pretty standard here.

             

            I also want to get and display the 'charter_contacts' and 'leader_contacts' in each UNIT. That would be two more one-to-one relationships back to CONTACTS on my graph. Instead of doing that I can use ExecuteSQL to get the values I might need.

             

            Less clutter on the Relationship Graph!

             

            I must note that I don't use ExecuteSQL() in calculated fields. I use SetField to push the value when needed or auto-enter (one time action). This helps greatly in the scheme of things (speed-wise).

             

            You can use 'constants' in the queries which is not possible in relationships. Yes, I've use auto-enter fields as "constants", but if you have a longish set of values, that's ONE field and a corresponding relationship for each value.

             

            In my example, I might need leaders, charterOrgs, schools, (basically every possible position) to get what I need for a Unit report. ExecuteSQL() can help here by getting me a 'table' that doesn't really exist. The values are pushed to a Virtual List and voila I have something that would be so many more "work-arounds".

             

            Another example is where a client needs to 'archive' records, but wants reports based on the current records AND the archive records (for a particular student). Again I can make a query against TWO tables (with a UNION in SQL), get the values and make a report (virtual list or just a tab-delimited list in a field).

             

            beverly

             

            p.s. have you seen the articles on ExecuteSQL()? and there are many 'helper' databases that may get you started.

            • 3. Re: SQL vs native filemaker
              wimdecorte

              CarlSchwarz wrote:

               

              Is there an advantage to using SQL to just doing everything in native Filemaker?

               

              Mike and Bev explained it well.  I just want to add some perspective:  it choice is not an "or" choice.  There is plenty of places where native FM searches make sense.  But you can supplement them with ExecuteSQL() calls easily where they fit.

               

              I would certainly spend some time getting familiar with ExecuteSQL().  It is very easy to get into and very rewarding.

              • 4. Re: SQL vs native filemaker
                CarlSchwarz

                Beverly could you head me in the right direction to those Execute SQL databases and articles?

                Thanks!

                • 5. Re: SQL vs native filemaker
                  CarlSchwarz

                  Mike thanks for the great answer.  My graphs are very messy because of this reason, one off TO's but I live with it (and I guess others do too!)

                  Also glad to hear what you say about performance.  I've been working on a database where ExecuteSQL and virtual lists are used everywhere, including in values lists in portals which can have up to 300 records and it's impossibly slow so I've been taking the SQL and virtual lists away.  But thanks to your explanation I can see the flexibility it adds in functions and there have been times where I've had to repeat the same filemaker TO over and over again so I reckon I need to do some learning just to help out in that situation.

                  I might do some learning thanks Bev!

                  • 6. Re: SQL vs native filemaker
                    stephensexton

                    My experience has also been that SQL can be slower in some cases... however, in other cases it can be many times faster.  It depends on the complexity of the relationships that you are calling on.  An ExecuteSQL that is dependent on only 1 or 2 variables can in some cases reduce the number of TOs required by several... you can set variables and get data from unrelated tables without leaving the current layout.  I've seen huge performance gains by using SQL for this reason, even when retrieving large lists of values.

                    • 7. Re: SQL vs native filemaker
                      BruceRobertson

                      Perhaps It will turn out to still be the case that you need to adopt a different design approach.

                       

                      But when I hear "virtual lists are slow" I wonder which of the several quite different methods you use to capture the data supporting the rows; and how you calculate the rows for your virtual list.

                       

                      GetValue( $$VList; N) is the slowest method.

                      • 8. Re: SQL vs native filemaker
                        Mike_Mitchell

                        BruceRobertson wrote:

                         

                        GetValue( $$VList; N) is the slowest method.

                         

                        Bruce -

                         

                        Can you elaborate on this a bit? Pardon the ignorance, but I've not seen any other method.

                        • 9. Re: SQL vs native filemaker
                          beverly

                          Well...

                           

                          get the pdf and sample files! there are links to other sources as well

                               http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

                           

                          for info on newer functions and other articles:

                               http://www.seedcode.com/tag/filemaker-sql/

                           

                          for other articles not listed the first link:

                               http://filemakerhacks.com/category/executesql/

                           

                          for a forum just about FQL (FileMaker Query Language):

                               http://fmforums.com/forum/155-filemaker-query-language-or-fql/

                          • 10. Re: SQL vs native filemaker
                            BruceRobertson

                            "Can you elaborate..."

                            Maybe I will try once again to prepare a devcon presentation when the time rolls around.

                            Walking a list with getValue( myList; N) is slow, and slower the longer the list.

                            Using repeats is WAY faster, and there are several ways to use repeats.

                            Let your data-gathering script immediately put the results into repeating variables:

                            $$data[1]

                            $$data[2]

                            etc

                            then the VList row display calc is $$data[N]

                            You can do the same for repeating global fields or you can set up a report table that contains repeating fields and populate that.

                            Another advantage of using the repeat format is that the data for any particular cell can contain returns. Or it can be empty - and the results do not "collapse" because of the missing value.

                            • 11. Re: SQL vs native filemaker
                              Mike_Mitchell

                              Very cool. Thanks!

                              • 12. Re: SQL vs native filemaker
                                jbante

                                To elaborate on the relative performance of return-delimited lists vs. repeating variables: the time it takes to use GetValue is proportional to the position in the list, for each call, O(n), so the time for parsing out every value is O(n^2). Retrieving the value from a repeating variable, on the other hand, is O(1), and pulling out all the values is O(n). More details and a demo file are available on FMForums.

                                • 13. Re: SQL vs native filemaker
                                  Mike_Mitchell

                                  Fascinating. I'm making extensive use of Virtual Lists on recent projects, so this gives me something new to play with. Thanks very much!

                                  • 14. Re: SQL vs native filemaker
                                    CarlSchwarz

                                    Bruce - It's not my Virtual list, I'm working on a database that someone else developed.

                                    In his technique the value list is populated from a virtual list via an SQL command.  The SQL command loads the entire table to create the value list.  This calculation is performed once for every row in the portal.

                                     

                                    Thus, every portal row executes SQL that loads the entire table of thousands of records and selects maybe 5 values from that to present in the value list.

                                    I simply replaced it with an appropriate relationship.

                                    TBH I have only used virtual lists once myself IIRC, maybe twice but I do recall it was very handy at the time...

                                     

                                    O_o  when you say getValue do you mean the inbuilt getValue function??? I use that a lot!  I assumed it was optimised so that Filemaker didn't have to walk the list???!!! You have given me something to think about.