1 2 Previous Next 25 Replies Latest reply on Jan 15, 2017 1:58 PM by user19752

    Best Performance for my problem

    NicoChiaro

      Hi together,

       

      I have developed a document management system while using the MBS Plugin. Everything is working perfectly fine except WAN Performance of my Document Browsing.

       

      I have Tags on the documents which help with the browsing: you click a tag and the portals show documents which have this tag.

      You chose a second one: the portal shows only those documents which are tagged with "both" of the Tags.

       

      I have a Field that is called CheckTagOccurence_n and it is calculated to be 1 if the tags chosen are all in the documents tags. So the Portal is filtered by this Field.

       

      I thought it might be the calculated content that took so long but after tests without media filed and indicated fields instead of calculated it took the same time to show them (about 10 seconds with about 1000 Documents in the database)

       

      So I think it must be the CheckTagOccurrence calculation which makes it so slow:

       

      It's a little bit complicated because I totally came up with this on my own:

       

      I have a multilinekey-field which is global and where the tag uuid "choice" is written in. This is List 1

       

      I have a TagList field which contains all the Tag's UUID's of a document. Context of calculation is the documents table for the Portal to show. This is List 2

       

      I have a AntiFilterList which uses FileMaker Custom Function:AntiFilterValues( ListA; ListB )

       

      It is List 2 without List 1 > List 3

       

      The CheckTagOccurence Calculation is:

       

      If (ElementsCount(List2) + ElementsCount(List3) = ElementsCount(List1); 1;0)

       

      With This Calculation it took me 10-15 seconds to get the data I wanted.

       

      The Portals relationship is based on the MultilineKey- List 1. It is filtered by the check Tag occurrence.

       

      Is there a possibility that this might be faster with SQL?

       

      I also Tried something else: instead of the Sum of ListElements Calculation I chose a MBS Function which simply gives Back an "AND" / InnerJoin of the two Lists 1 and 2.

       

      This also worked but was 3-4 times slower. (45-60 seconds)

       

      I highly appreciate any help and I plan to give it back

       

      Thanks

       

      Nico

        • 1. Re: Best Performance for my problem
          erolst

          NicoChiaro wrote:

           

          I have Tags on the documents which help with the browsing: you click a tag and the portals show documents which have this tag.

          You chose a second one: the portal shows only those documents which are tagged with "both" of the Tags.

          Here is another approach of implementing an AND search for any number of tags: go to a blank layout of the Documents table, then use a constraining loop search and (by way of a summary field) collect the resulting IDs (if any) to put them into a field that drives a dedicated relationship.

           

          Set Variable [ $iterationsToDo ; ValueCount ( $$tagsToSearch ) ]

          Go to Layout [ BlankSearchLayout ( Document ) ]

          Set Error Capture [ on ]

          Show all Records

          Loop

            Exit Loop if [ Let ( $i = $i + 1 ; $i > $iterationsToDo or not Get ( FoundCount ) ) ]

            Enter Find Mode

            Set Field [ DocumentTag::TagID ; GetValue ( $$tagsToSearch ; $i ) ]

            Constrain Found Set

          End Loop

          Set Variable [ $resultIDs ; Document::sListOfIDs ]

           

          You could of course adapt the same approach to SQL by adding a tag's search result to the WHERE clause of the next search (ie constrain it).

          • 2. Re: Best Performance for my problem
            wimdecorte

            NicoChiaro wrote:

             

            I have a Field that is called CheckTagOccurence_n and it is calculated to be 1 if the tags chosen are all in the documents tags. So the Portal is filtered by this Field.

             

            Not sure if I follow entirely but if I do then the field you talk about here is in the Documents table?  If it is then it is an unstored calc by nature and those things (in these circumstances) are going to be very very slow.

             

            If you don't have one: you'll need a join table between tags and documents.  Then you can do a very simple ExecuteSQL (or native FM find) in that join table to return a list of the document IDs that have the tags you are after.  With that list in hand you can fire a GTRR to show the documents, purely by document ID.

            • 3. Re: Best Performance for my problem
              NicoChiaro

              Yes I have a join table.

               

              I think this is a case for PSoS ?

               

              I still don't quite get why these calculations are really fast in the LAN but are so slow in WAN. I would like to learn more about where and when those calculations are done.

               

              I recently had a case when I "tested" PSoS. Performance was like 2 Seconds on Server vs. 4 Minutes on the client. I had to learn this by accident.

               

              So I can give the Tag ID's as Parameters and perform the find on server. What I did not understand yet is how I can give back what the server "found out".

               

              I heard SQL is slow sometimes- but why where and when? I am really into the performance aspect since my database is hosted and I want everybody to use it from anywhere without major performance problems.

               

              A lot of questions - I'm sorry for that but this helps me a lot.

               

              Thanks to both of you

              • 4. Re: Best Performance for my problem
                CarlSchwarz

                I have found using network tools like GlassWire can be useful for WAN testing.  It seems in your case that you are pulling the entire set of tables down to the client to do the filter.  Perhaps it is doing it in a 'chatter' sort of a way that requires lots of callbacks to the server to get the data for the filter and that can make it slow.  GlassWire will show application based network traffic.

                If you could change your filter to work at the relationship level then you will not be pulling down all of the records to the client to perform the filter.

                 

                Also AFAIK SQL calls will also pull down the entire table to the client to execute the SQL statement which makes them inefficient over WAN.

                • 5. Re: Best Performance for my problem
                  wimdecorte

                  NicoChiaro wrote:

                   

                  Yes I have a join table.

                   

                  I think this is a case for PSoS ?

                   

                  If I was mentoring you I would insist on understanding the core fundamentals, not moving the execution.  If you have a join table you don't need that 'expensive' calculation.

                   

                  PSoS is a great tool but it is not a panacea for all performance issues.  If you start throwing everything that is expensive performance-wise to FMS then you will reach a point where FMS will be overloaded too.  Be very careful about the performance gain conclusions you draw from PSoS just by testing on your own.  Think what the effect will be if all the users start doing this.  Your server becomes an application server, not a database server and you'll need the resources on the server to handle that (and you will need to start monitoring the resource utilization on the server too - mandatory - to help determine any bottlenecks and plan for how much extra load it can take.

                  Short version: make your solution more efficient, don't just shift the execution.

                   

                   

                  NicoChiaro wrote:

                   

                  What I did not understand yet is how I can give back what the server "found out".

                   

                  Use Exit Script[ <your result> ] in the PSoS script and Get(ScriptResult) in the calling script.

                   

                  NicoChiaro wrote:

                   


                  I heard SQL is slow sometimes- but why where and when?

                   

                  Read up on this. Using ExecuteSQL() calls in FileMaker - Soliant Consulting

                  It describes the most serious performance issue you can have with ExecuteSQL()

                  In addition to that, the more complex you make the SQL query (by using many joins, the LIKE operator, SQL functions,...) the slower it will get, sometimes exponentially so.  The good news is that very often we tend to overthink the SQL query and make it a lot more complex than it really needs to be.

                   

                  The demo file that I use in the blog post shows that a straightforward SQL query, like the one that you can use in your case, is blazingly fast.

                  • 6. Re: Best Performance for my problem
                    wimdecorte

                    CarlSchwarz wrote:

                     

                    Also AFAIK SQL calls will also pull down the entire table to the client to execute the SQL statement

                     

                    No, they don't.  They are actually very efficient and FMS will do them by default and whenever possible.  Except when there is something in the query that can only be resolved by the client because FMS does not have the information.

                     

                    See that blog post I mentioned and the demo file that comes with it.  Run the test and you'll be blown away.  It shows the worst case scenario: the user has an open record in the target table; that makes FMS send all the data down to the client because FMS does not know what the uncommitted data is.

                    But if all the data is properly committed then FMS does the query and returns the result, which makes it extremely fast.

                     

                    For this reason you have to be careful where you use ExecuteSQL().  I tend to avoid it in situations where I have no control over whether there are open records in the client's session.  Like in conditional formats, calc fields, tooltips,...

                    • 7. Re: Best Performance for my problem
                      NicoChiaro

                      Thanks to both of you I solved it with a ServerBased Search. My search seems now to be constantly performing fast. About 1 second.

                       

                      I will try the same with SQL soon and will give you feedback here.

                       

                      I will therefore study your links Wim, thanks a lot

                       

                      Nico

                      • 8. Re: Best Performance for my problem
                        siplus

                        Remember that portal filtering means server sending you all the records matching the relationship and doing the filtering on the local machine.

                         

                        Remember that the Antifilter CF you're using is implemented in MBS: Monkeybread Software - MBS FileMaker Plugin: List.Not

                         

                        Remember that if you're hosting your database on Filemaker Server 15, you can enable Top Call Statistics and look for  whatever is taking too long

                        • 9. Re: Best Performance for my problem
                          Vincent_L

                          wimdecorte a écrit:

                           

                          CarlSchwarz wrote:

                           

                          Also AFAIK SQL calls will also pull down the entire table to the client to execute the SQL statement

                           

                          No, they don't. They are actually very efficient and FMS will do them by default and whenever possible. Except when there is something in the query that can only be resolved by the client because FMS does not have the information.

                           

                          That's certainly not my experience at all, if that'd be true, then second run of same query would'nt be 100 times faster.

                          And I'm talking about queries involving stored fields that have nothing that needs to be resolved by the client

                          • 10. Re: Best Performance for my problem
                            taylorsharpe

                            Also, ExecuteSQL lends itself well to be used with Perform Script on Server and just return the text result and it will usually perform even faster on the server. 

                            • 11. Re: Best Performance for my problem
                              wimdecorte

                              Vincent_L wrote:

                               

                              wimdecorte a écrit:

                               

                              CarlSchwarz wrote:

                               

                              Also AFAIK SQL calls will also pull down the entire table to the client to execute the SQL statement

                               

                              No, they don't. They are actually very efficient and FMS will do them by default and whenever possible. Except when there is something in the query that can only be resolved by the client because FMS does not have the information.

                               

                              That's certainly not my experience at all, if that'd be true, then second run of same query would'nt be 100 times faster.

                              And I'm talking about queries involving stored fields that have nothing that needs to be resolved by the client

                               

                              It's very easy to prove; that was part of that devcon presentation:

                              - host the file that is part of my blog post on one of your servers.

                              - run the query against the table that has 1,500,000 records.

                              - open a record and run the query again

                               

                              Yes, FM and FMS do cache some stuff so that the second query with no open records is faster than the first time.  But FMS does *NOT* send down all 1,500,000 records when it does not have to.  Check the FMS stats log for the time of your first query: there will be no evidence that FMS is sending 1,500,000 records.

                               

                              If you open a record and run the query again you'll be waiting for a few minutes, and the FMS stats log will clearly show in the network KB column that all the data is being sent down.

                              • 12. Re: Best Performance for my problem
                                wimdecorte

                                Vincent_L wrote:

                                 

                                wimdecorte a écrit:

                                 

                                CarlSchwarz wrote:

                                 

                                Also AFAIK SQL calls will also pull down the entire table to the client to execute the SQL statement

                                 

                                No, they don't. They are actually very efficient and FMS will do them by default and whenever possible. Except when there is something in the query that can only be resolved by the client because FMS does not have the information.

                                 

                                That's certainly not my experience at all, if that'd be true, then second run of same query would'nt be 100 times faster.

                                And I'm talking about queries involving stored fields that have nothing that needs to be resolved by the client

                                 

                                Here's a screenshot:

                                 

                                First run against a hosted file, local network, 1.5 million records in the table = 12ms

                                Subsquent runs take a third to half the time in relative terms; in absolute terms you won't notice the difference unless you do a lot of iterations of this.

                                 

                                2017-01-14_12-44-40.png

                                 

                                Obviously the more complex you make the query the slower it will get.  The point of this demo is to show the speed difference with and without open records in the target table.

                                • 13. Re: Best Performance for my problem
                                  Vincent_L

                                  Well, in my experience I encounter a lot of simple queries where first run is 50 seconds, and second run is 0,05s

                                  • 14. Re: Best Performance for my problem
                                    wimdecorte

                                    On a local network?  That would seem to indicate that you do have something in the query that is unique to the user's session and that forces FMS to send all the data down.  Should be easy enough to verify by using at the FMS stats log and top call log.

                                    1 2 Previous Next