1 2 Previous Next 20 Replies Latest reply on Feb 18, 2016 2:52 PM by siplus

    What areas can using SQL within FileMaker scripting yield significant performance gains?

    SMGreenfield

      What are some FileMaker operations that could be significantly improved by using SQL operations?  Primarily we're interested in single user local databases, but if there are big wins for server-side operations, we'd be interested in that, too.

       

      Best Regards,

       

      Stephen

        • 1. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
          beverly

          What might you have in mind? define "SQL operations", please.

          beverly

          • 2. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
            fitch

            Probably none. Not saying don't use it, but speed is not the reason to use it.

            • 3. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
              CarlSchwarz

              Yes as far as I'm aware SQL runs through a translator in Filemaker, so you get that overhead at least.  Then I believe that it's running operations on a Filemaker engine anyway and not on an SQL engine.

              AFAIK using SQL can save development time, and means that you don't have to add extra occurrences to the relationship graph for single operations.  There's probably other useful reasons to use it on FM data also.

              • 4. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                SMGreenfield

                Sure -- I go through a very detailed set of scripts that iterate through several tables that have a hierarchical relationship (let's call the tables LEVEL1, LEVEL2, and LEVEL3, for simplicity's sake).  I'm constantly inserting new records in each of these tables as I iterate through data I'm using to build them.

                 

                Throughout the process, I've found I need to constantly switch to different layouts to look up information as I'm building the records for these levels.  That process takes a long time (a couple minutes on a FM server, around 10 seconds on a local database).  I believe everything that can be indexed is indexed.  Each of these tables uses a LOT of calculated fields.  There are also a zillion relationships.  We do everything on empty layouts to try and improve performance.

                 

                So while SQL might not be "the" answer to improving performance, my question was trying to find out if there was some native FileMaker operations that applying SQL to might be a specific edge.

                • 5. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                  beverly

                  I wonder if you can set variable(s) to pass along as you do this process (so you are not having to "look up")?

                   

                  beverly

                  • 6. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                    macwombat

                    Hi

                    Throughout the process, I've found I need to constantly switch to different layouts to look up information as I'm building the records for these levels.  That process takes a long time (a couple minutes on a FM server, around 10 seconds on a local database).

                     

                    Couldn't this information be set in building the records by using the ExecuteSQL function in calculations to grab the data without switching layouts etc.  (NB: it is not interacting with SQL databases at all but using a SQL query statement to select data from FM tables).  From what I've read that might speed things up, but I believe Beverly is the expert on that sort of thing

                    Chris

                    • 7. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                      SMGreenfield

                      Well, I do set variables -- when I can.  But for example I often need to look up information in the SAME TABLE.  Each item I iterate through might have a textual name, and I need to look up its ID.  If it can't find it, I need to use a different ID.

                       

                      Rather than doing this by going into Find mode, which switched to a different layout, I used this SQL:

                       

                      ExecuteSQL ( "Select a.\"__pk_SectionID\" FROM \"SECTION\" a WHERE a.\"_fk_ProjectID\" = ?  and a.\"SectionName_DATA\" = ?"; " "; "|*|"; $$paste_projectID; $thisCategorySectionName)

                       

                      I figured (perhaps incorrectly so) that this sort of thing might save some time by not changing out the layout and going in and out of find / browse mode.  It shaved about 3% of time off my long iterative process, and there are other places I can do similar things.

                       

                      I just figured the folks here have "been there, done that" -- best to ask the experts.

                      • 8. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                        bigtom

                        It was explained to me at a DevCon that the ExecuteSQL is not run as SQL directly and is translated into something FM can use natively so it is technically slower. However as you have found switching layouts has a time cost. I am all about speed. It sounds like you are likely already freezing windows to increase speed. Running certain things with PSOS can really speed things up. Although working with the returned data can sometimes use some creativity.

                        • 9. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                          monkeybreadsoftware

                          If you need to sum up values from 1000 records or update 1000 records by setting a field, the SQL will be much faster.

                          Because in a normal script you have to make a loop over all records and perform your action.

                          Switching layouts, moving from record to record in GUI takes long.

                          • 10. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                            SMGreenfield

                            Christian --

                             

                            I haven't even tried using the SQL functions in your incredible MonkeyBread FileMaker plugin (which we of course license) -- might there be any performance advantages to using some of YOUR SQL functions to perform operations on the open FileMaker database?

                            • 11. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                              taylorsharpe

                              SQL in and of itself is sometimes faster and sometimes slower than the native FileMaker methods.  So don't even assume it is always faster because sometimes it is slower, especially with joins.  However, there are certain things that slow FileMaker down a lot.  One thing that is often done in FileMaker scripting is navigating around between layouts to gather information and return to the original layout to do things.  However, every time you change a layout, FileMaker has to think through the context change plus about whatever is on that layout.  Granted we can do some things like have layouts with nothing in them to speed things up.  But if you use SQL to gather information, it is context independent because the context is set in the SQL language.  So while the SQL might not be faster than say a FileMaker Find, if you use SQL and avoid jumping to another layout to search for something and coming back, you avoid two context changes for FileMaker, thereby making SQL feel a lot faster.  More advanced techniques like virtual arrays can speed things up too, but is a whole discussion in and of itself.  Other benefits are that if I am only going to need a relationship once for a report, I will usually get that information via SQL instead of creating an additional table occurrence and join in the layout.  Simplifying the relationship graph by only using joins where you will need them multiple times can make the database faster in that there is less context for FileMaker to think through.  It is a cumulative effect by avoiding extra TOs and joins.  Not that you should avoid them, but if you only need them once, SQL is a better way to go.

                              • 12. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                                siplus

                                SQL can offer significant benefits in multiuser environments, but I'd give away a body part for a function FieldValues(fieldName) which would return me a list of the values in fieldName based upon the current found set.

                                 

                                Right now I go to a layout holding just that field and do a copy all records, but I am ashamed of myself.

                                • 13. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                                  taylorsharpe

                                  The Draco engine that runs FileMaker is a SQL engine plus quite a number of other things too.  Technically all databases translate SQL into their native development language and FileMaker is no different.  SQL is really just an ISO standard that sets rules for if you make a statement in a certain form, you will get specific results that are the same on all SQL engines.  But SQL is not a program itself, it is a standard. 

                                   

                                  I will agree that certain big iron SQL translators are faster than FileMaker, but FileMaker is not adding an additional layer of translation. 

                                  • 14. Re: What areas can using SQL within FileMaker scripting yield significant performance gains?
                                    taylorsharpe

                                    Siplus.... You can create a Summary field that is a list of the primary key and call that field to create a SQL that returns those values.  So if you have a table with a primary key field called TableName::PK (assume it is a number field) and a Summary field with LIST called TableName::PK_List.  Then you do a find and have a found set and do a SQL like this:

                                     

                                    Let ( [

                                     

                                    F1 = Table::PK_List ;

                                    F2 = Substitute ( F1 ; ¶ ; ", " ) ;

                                    F3 = "SELECT

                                              FIeldYouWantAListOf

                                         FROM

                                              TableName

                                         WHERE

                                              PK IN ( " & F2 & " )" ;

                                    F4 = ExecuteSQL ( F3 ; ¶ ; ¶ )

                                     

                                    ] ;

                                     

                                    F4

                                     

                                    )

                                    1 2 Previous Next