1 2 Previous Next 19 Replies Latest reply on Mar 5, 2015 1:31 PM by openspace

    Dashboard Stats: Very slow loading time

    openspace

      Alright! I have an extreme "beginners situation." This is my first database let alone coding project, so bare with me. I created a dashboard for my database that compiles information from 3 or 4 tables into one place using SQL. Works great... fantastic... until I imported over a thousand contacts.... now the thing is so slow to load the dashboard I might as well have created a time machine back to 1989. To say the least, it's a total fail.

      Screen Shot 2015-03-05 at 12.10.59 AM.png

      I have a hunch as to what the problem is... well at least I can identify the elephant in the room which is the fact that I'm guilty of creating a lot of SQL calculations on the dashboard.... a lot (see below). There must be a better way to do this...... ? A way to speed/clean things up?

      Screen Shot 2015-03-05 at 12.12.00 AM.png

       

      I'll post an earlier version of the file without any confidential information so you can take a look at the mess I've created. I'm pretty sure the calculations on the dashboard are the same as the version I'm working with now. i'm running fm 13.

        • 1. Re: Dashboard Stats: Very slow loading time
          openspace

          usrname: Admin

          psswrd: artworks

          • 2. Re: Dashboard Stats: Very slow loading time
            Mike_Mitchell

            You have correctly identified your problem. Grasshopper.  

             

            Well, pretty close, anyway. It's not necessarily SQL that's the issue, but the large number of unstored calculations. Aggregation of statistics using unstored calculations will be a performance killer, especially as the record counts start to climb. You're far better doing your aggregating in a warehouse table as you go, using either Script Triggers or batch jobs run on a schedule. This will allow FileMaker to run the aggregates as stored calculations, which will process much faster.

             

            So for every stat you want to keep, you need to write some code to store the value and then update it as the data change. That's the basic strategy behind warehousing.

             

            HTH

             

            Mike

            • 3. Re: Dashboard Stats: Very slow loading time
              DanielShanahan

              Is your solution hosted?  And how often does the dashboard need to be updated?  If it is hosted, you may want to consider a scheduled script on the server to periodically update the values in a separate table as Mike suggests.

              • 4. Re: Dashboard Stats: Very slow loading time
                intex

                nice modern interface :-)

                 

                Sadly enough you have to keep in mind though, that FileMaker is just damn slow. So a live dashboard like your´s probably is kind of impossible with larger datasets. So indeed the best idea might be to just show some static values and then offer a "Refresh" button together with progress bar.

                • 5. Re: Dashboard Stats: Very slow loading time
                  wimdecorte

                  FM is plenty fast.

                   

                  As Mike indicated, SQL is not the culprit here but the sheer number of unstored calculations.  Instead of calculating these things on the fly, have those totals updated as part of the workflow.  For instance when a new donation is entered, update the totals field.  That way it becomes just a number field that carries no penalty when you display it on the dashboard.

                  • 6. Re: Dashboard Stats: Very slow loading time
                    bhagara

                    i'll agree with Wim.  Filemaker is plenty fast, but a developer can make decisions that will create a slow solution.

                     

                    I'll hope to hear back when the fields referenced in your dashboard are done with stored calcs or static values.  How much faster is an interesting question.  It would be instructive for all of us, to be sure.

                    • 7. Re: Dashboard Stats: Very slow loading time
                      BruceHerbach

                      As others have indicated,  the issue is the number of Un-stored calculations.  Wim gives you a good suggestion for setting this up as stored values that can be indexed.  This will speed the whole thing up.

                       

                      How ofter do you need the display to update?

                       

                      Here are a couple of other options for updating the values.  The first is a script that runs all of the queries,  puts the values into Variables, and then set field,  or Set field with the SQL as the value.  Use an On timer script attached to the window to run the script every X minutes.

                       

                      Another option is to convert all the Un-stored calculations to Auto-Enter Calculations.  Add a field call it ~update and add it to the let statement in every Calculation.  So you would add

                       

                      Let([

                       

                      upd = ~update;

                      .

                      Everything else that is already there ;]

                      Value )

                       

                      Then have your On timer script just has to change the value of the ~update field.  This will force all of the auto enter calculations to update.  In short you are making all of the auto-enter calculations dependent on the ~update field.

                       

                      The result will be a stored data so the screen opens quickly, and the update happens in the background.

                       

                      HTH

                      • 8. Re: Dashboard Stats: Very slow loading time
                        openspace

                        I've just updated the "top 3" calculations to be indexed and I can already see a HUGE performance boost. I think we're on to something!

                         

                        @Mike_Mitchell or wimdecorte

                        How do I force the totals fields to update via a script trigger? More specifically, what script step is capable of doing this. I think this may be the route I'd like to take because I would prefer that the dashboard is updated as the user makes changes.

                        • 9. Re: Dashboard Stats: Very slow loading time
                          openspace

                          intex

                          Thanks! I do love myself some flat design. I'm still working away at the icons...probably the slowest part... I can't take too much credit though, many of the layouts are really just an adapted version of the invoices starter solution.

                          • 10. Re: Dashboard Stats: Very slow loading time
                            Mike_Mitchell

                            Set Field generally works pretty well.

                             

                            You usually have to do some error trapping for record lock (to make sure two users don't collide with each other and attempt to update at the same time), and you have to watch your context, but you can generally just use a Set Field script step.

                            • 11. Re: Dashboard Stats: Very slow loading time
                              wimdecorte

                              Don't automatically start thinking "script triggers", there is usually a flow that you can take data and run button-driven scripts instead of having to carefully piece together a set of independent triggers that can all step over each other.

                               

                              Triggers certainly have their place but they should not be the first go-to here.

                              • 12. Re: Dashboard Stats: Very slow loading time
                                Mike_Mitchell

                                Yes, true. My original mindset was, "Update field, update total." But if you have a scripted workflow process, by all means, incorporating the dashboard updates into that is a much better plan.

                                • 13. Re: Dashboard Stats: Very slow loading time
                                  openspace

                                  Woops, I have to watch my wording. I have some buttons for making a donation or updating a membership that could drive the extra script step and update the totals.

                                   

                                  @Mike_Mitchell

                                  Do you know where I can delve deeper into the mentioned error trapping/record lock? We're only running three accounts of filemaker 13, but I think it would be best to study up and avoid this error.

                                   

                                  If I use the set field step do you select the field you want to update and just set it to it's own calculation already stated in the field calc? 

                                  • 14. Re: Dashboard Stats: Very slow loading time
                                    openspace

                                    Scratch that, I didn't see Bruce Herbach's post until now. I think that answers my question about the set field step.

                                    1 2 Previous Next