1 2 3 Previous Next 33 Replies Latest reply on Jan 18, 2017 7:05 AM by siplus

    Best performance for ExecuteSQL 'SELECT COUNT()...'?

    skywillmott

      Hello,

       

      So I am putting together a complex 'dashboard' feature which requires many ExecuteSQL 'SELECT COUNT()...' script steps.

       

      For best performance, I think I am right in using 'Perform Script on Server' so that all the ExecuteSQL calculations are done there, rather than the FileMaker Pro client. However, I'm curious to know if using for example 'SELECT COUNT(*) FROM tableName WHERE fieldName1=? AND fieldName2=?' is faster or slower than 'SELECT COUNT("PrimaryID") FROM tableName WHERE fieldName1=? AND fieldName2=?'... That is, does using '*' or specifying the primary ID field name in the COUNT part make much difference in performance?

       

      The dashboard screen has a ridiculous number of fields to display - something like 300 numeric 'COUNTS', so performance is definitely an issue... At the moment, it actually only takes a few seconds to do, but would be great to get it working as fast as possible.

       

      Any ideas gratefully received :-)

        • 1. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
          fmpdude

          I believe from a SQL parser point of view "*" requires a fetch of the entire row and could be slower than just the PK.

           

          FMP might also have an index on the PK also so if the SQL engine takes that into account, that might also help.

           

          You'll probably need to benchmark with your actual data and server env.

          1 of 1 people found this helpful
          • 2. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
            siplus

            in my book Select Count() looses every match against ValueCount(Select())

            4 of 4 people found this helpful
            • 3. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
              skywillmott

              Thanks siplus, that does look like an interesting method to try. So my script step would be something like:

               

              Set Field [ myResultField; ValueCount( ExecuteSQL ( "SELECT primaryID FROM myTable WHERE fieldName=? AND fieldName2=?" ; "," ; "¶" ; fieldValue1; fieldValue2 )  )  ]

               

              Once I've got it all ready I'll do some benchmarking and see what the performance differences between methods is and report back here :-)

              • 4. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                siplus

                yes, that's what I'm suggesting as a first improvement.

                 

                To be compared with

                 

                - define a summary field "myCount" in myTable, defined as Count(primaryID)

                - define a relationship "myResults" with 2 globals in your case, gFn1, gFn2 related to FieldName1 and FieldName2 respectively

                 

                - look at SetField [myresultField; myFResults::myCount] after setting gFn1 and gFn2.

                 

                Here's something you can paste in your data viewer, correct references and then evaluate a couple of times successively:

                 

                 

                Let ([

                 

                t1s = Get ( CurrentTimeUTCMilliseconds );

                q1 = ExecuteSQL("SELECT COUNT(*) FROM RandomPeople WHERE GivenName =? AND Surname = ?";"";""; query::gF1; query::gF2);

                t1e = Get ( CurrentTimeUTCMilliseconds ) - t1s;

                 

                t2s = Get ( CurrentTimeUTCMilliseconds );

                q2 = ExecuteSQL("SELECT UUID FROM RandomPeople WHERE GivenName =? AND Surname = ?";"";""; query::gF1; query::gF2);

                r1 = ValueCount (q2);

                t2e = Get ( CurrentTimeUTCMilliseconds ) - t2s;

                 

                t3s = Get ( CurrentTimeUTCMilliseconds );

                r2 = Check::myCount;

                t3e = Get ( CurrentTimeUTCMilliseconds ) - t3s

                 

                ];

                 

                 

                "ms: " & t1e & Char(9) & q1 & ¶ &

                "ms: " & t2e & Char(9) & r1 & ¶ &

                "ms: " & t3e & Char(9) & r2

                 

                 

                )

                 

                do it while sitting on a layout of table query having 2 fields, gF1 and gF2 globals text with a rel named Check to the RandomPeople table, based upon gF1 = GivenName and gF2 = Surname.

                 

                 

                ------------------------

                added bonus

                ------------------------

                 

                don't know if it's your case, but SQL is quite strict, so the found results might differ depending - for example - on special characters.

                 

                In german,the relationship finds Müller and Weiß when you input Muller or Weiss; the SQL does not.

                1 of 1 people found this helpful
                • 5. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                  bigtom

                  siplus has good advice on ValueCount(). 

                   

                  Perform script on server is the way to go. For speed the usual eSQL things like running the script while on a layout that is not from the query table.

                   

                  You need 300 count summaries? Think about how you might get them in one query. PSOS is single threaded. You can run multiple PSOS calls if you don't wait for a result. Think about how you send the data back to the client In either case And how it gets into the proper place on the dashboard.

                   

                  Anything after the first query runs runs faster than the first, but multiple queries take a lot of time. You might be better off with a simgle query with multiple Counts if possible.

                   

                  The best option in my opinion would be a table with eSQL calcs in auto enter fields that is unrelated and only accessed via script from PSOS.

                   

                  With auto enter calcs you simply go the the layout and create a new record. The server client automatically evaluates all fields at once. That's nice if you have 300. You just script the data back to the client and delete the record when done. I usually have the calc generate the full text needed for NVPs and do an eSQL Select* to get everything in a nice list back to the client as a parameter.

                   

                  When the list is back to the client you just loop a GetValue() and quickly store to global variables for display on the layout.  I have been doing this as a preferred method of quickly updating language labels in multi language solutions On remote mobile device with not so good connection. I spent a lot of time to test various ways to do this as quickly as possible.

                   

                  I will get to posting an example in a couple days maybe.

                  1 of 1 people found this helpful
                  • 6. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                    siplus

                    PSOS has no cache to rely upon, so it really has to be carefully planned in order to gain benefit from it - and in any case benchmarked - because it's absolutely not the definitive cure to performance problems. I have mixed results and as of now there's only one situation where I use it. And I might as well stop using it, thanks to "truncate table"...

                     

                    PSOS's benefit also has to be evaluated as a whole impact on performance, i.e. (extreme example) slowing down 40 users for 2 minutes several times an hour just because the big boss wants to see some statistics on sales and he wants them faster and he has a good time asking for them every hour is not the best solution as far as I'm concerned.

                    1 of 1 people found this helpful
                    • 7. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                      skywillmott

                      Hi bigtom,

                       

                      Thanks for this... Hmm, yes, having auto-enter calcs being triggered by record creation on the server sounds like a good possibility and one I may try out.. Sounds like it will make the best use of the Server to actually generate the data, i.e. could give best performance. Will have to come back to that though as already a long way down the road of scripting a whole load of ExecuteSQL calcs so will need to redo them all as auto-enter calcs... does sound worth it though - thank you :-)

                      • 8. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                        electon

                        siplus wrote:

                         

                        in my book Select Count() looses every match against ValueCount(Select())

                        +1

                        Just looked at this method on a remote server and it's an order of magnitude faster.

                         

                        Interesting.

                        • 9. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                          wimdecorte

                          skywillmott wrote:

                           

                          Hello,

                           

                           

                          The dashboard screen has a ridiculous number of fields to display - something like 300

                           

                          Asking a different question: why?

                           

                          No one among us can look at a screen with 300 numbers and take it all in.  So why not break it up into sections from a UX point of view so that you only show the data that the user is after.  That way you only have to expend resources to get that, not use resources to collect the 290+ things that the user will not look at right that moment.

                          • 10. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                            fmpdude

                            +1

                            I also have a dashboard type application. In fact, that's one of FMP's best uses I think.


                            For me, I use lots of graphs to boil down all the numbers. A picture is worth ...

                             

                            Users like/need aggregation of data even "counts" in this case.

                            • 11. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                              siplus

                              While I fully agree with you on the UX point, I think that there's plenty of people looking at a screen with 300+ numbers - it's only 30 rows with 10 columns in excel

                              • 12. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                                wimdecorte

                                If I really had to display that amount of data on a dashboard then I would work really hard to have those numbers updated as part of whatever workflow is relevant so that I would only have to display them, not calculate-on-the-fly and then display.

                                • 13. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                                  siplus

                                  Agreed, but unfortunately in a multi-user situation where many users add / delete / amend data you can't easily do that.

                                  • 14. Re: Best performance for ExecuteSQL 'SELECT COUNT()...'?
                                    wimdecorte

                                    I guess I don't agree on that.

                                     

                                    Even if you are reluctant to build it into the workflow you can always have a server-side schedule update the data and minimize the on-the-fly-aspect.

                                    1 2 3 Previous Next