1 2 Previous Next 18 Replies Latest reply on Nov 7, 2015 4:24 PM by ayescas

    WAN Portal with filters slow. Improvements technique ?

    ayescas

      Hello FM Folks,

       

      I have a file with 30k records and a tab with a portal, no filter. Over the WAN it takes about 30-40 seconds to load.  I've tried removing the sort and still get the same performance.  Is there a technique or design to improve the load time or is my relationship not set-up correctly?

       

      Thank you !!!

       

      Alex

       

      Screen Shot 2015-11-05 at 8.42.00 PM.png

        • 1. Re: WAN Portal with filters slow. Improvements technique ?
          mikebeargie

          I'm assuming you're showing school name from Schools_FilterLocation. That could cause slow downs since you're loading from two tables to display one table's records. Could be better to cache the data you need in the highlighted join table. Other things to check off the bat would be indexing settings for all fields involved.

          • 2. Re: WAN Portal with filters slow. Improvements technique ?
            mikebeargie

            Also, over WAN, you need to check your servers upload connection speed, and see if there is anything else vying for your network traffic on the same server. EG shared servers are going to be slower than colo servers.

            • 3. Re: WAN Portal with filters slow. Improvements technique ?
              dtcgnet

              In addition to the things Mike's mentioned, take a look at your scripting on the affected layouts. With older systems, developers frequently made use of "Refresh Window [Flush Cached Join Results]" in scripts in order to make sure their portals properly refreshed. Over a WAN, that is a performance killer, and with FileMaker 14, it is almost never necessary since you can use "Refresh Portal" instead.

              • 4. Re: WAN Portal with filters slow. Improvements technique ?
                ayescas

                Wow ...I'm guilty of using the refresh window and commit record after I run a script.  Going to delete those steps and use refresh portal...hopefully this is the issue.  thank you.

                • 5. Re: WAN Portal with filters slow. Improvements technique ?
                  dtcgnet

                  Name your portal, then use "Refresh Portal "PortalName"". With 30,000 records, it'll make a difference over a WAN, for sure. Hopefully you'll notice a difference with it.

                  • 6. Re: WAN Portal with filters slow. Improvements technique ?
                    ayescas

                    going to try it now.  thank you.

                    • 7. Re: WAN Portal with filters slow. Improvements technique ?
                      ayescas

                      No luck with the "refresh portal"...I think Mike is on the right path...I think the slowness is from the pulling of related data from two tables and I also have a value list that reference related data.  Going to make a copy of the file and start deleted stuff to find the bottle neck.  grrrr.   thanks all for the feedback.  here is the script just in case:

                       

                      Screen Shot 2015-11-06 at 9.21.43 AM.png

                      • 8. Re: WAN Portal with filters slow. Improvements technique ?
                        dtcgnet

                        The refresh portal instead of refresh window WILL make a big difference. Every time the cached join results are flushed, they must all be downloaded again.

                         

                        BUT, the reason for the slowness of your script is probably due to the fact that from a layout with the STUDENTS table as the base, you are doing a search for a value from a related table (Teacher::_fk_Teacher_EmployeeNumber). Finds (and sorts) based on fields from related tables tend to be pretty slow.

                        • 9. Re: WAN Portal with filters slow. Improvements technique ?
                          ayescas

                          Thanks dtcgnet,   going to look into that  later today.  thanks for the pointer !!!

                          • 10. Re: WAN Portal with filters slow. Improvements technique ?
                            BruceRobertson

                            I'd suggest going back a step here and clarify the problem and describe the design a bit more.

                            It isn't clear to me what the design is here; but maybe I missed something, and it is clear to everybody else.

                             

                            Anyway: what layout is this filtered portal on and what is the TO of this layout?

                            Is this really a filtered portal?

                            Filtered portals are just about the worst thing you can to for FileMaker performance. Moreso for WAN.

                             

                            A filtered portal says "send me ALL the data for ALL the records this relationship points to. Let me look at each record, one at a time, and apply your filter calculation to it while I dry my nails. OK. Pass. Fail. Pass pass pass. Fail. Yawn. OK, we're done, you had 30,000 records and 23 records passed. Here are your 23 records. Oooh, you bumped the screen size or did anything else to do cause a refresh? Let me try that again."

                            Better to use a filtered relationship, and one that constrains the record count as much as possible.

                            • 11. Re: WAN Portal with filters slow. Improvements technique ?
                              dtcgnet

                              BruceRobertson wrote:

                               

                               

                              A filtered portal says "send me ALL the data for ALL the records this relationship points to. Let me look at each record, one at a time, and apply your filter calculation to it while I dry my nails. OK. Pass. Fail. Pass pass pass. Fail. Yawn. OK, we're done, you had 30,000 records and 23 records passed. Here are your 23 records. Oooh, you bumped the screen size or did anything else to do cause a refresh? Let me try that again."

                              Better to use a filtered relationship, and one that constrains the record count as much as possible.

                               

                              I agree that filtered portals can sometimes cause performance issues, but the only time a filtered portal actually needs to check ALL of the records is when the cached join data is flushed. When the cache is flushed, THEN FileMaker must re-examine every record from the table in the portal to determine which are related, but the cache flush necessitates that slowdown whether a portal is filtered or not. AFTER the relationship has been re-established and new join results have been cachd, then FM looks only at the related records to determine which need to be filtered out. If a relationship has constrained the record count down to relatively small numbers already, filtering a portal based on that relationship doesn't take long.

                               

                              According to FileMaker:

                              Filtering portals utilizes the calculation engine in FileMaker Pro to determine whether a related record in a portal should be displayed or not.


                              The filtering calculation is applied ONLY to related records, NOT to all records in the table. Flushing cached join results should be avoided whenever possible, because that DOES demand that FileMaker re-evaluate all 30,000 records in order to determine which are related.

                              • 12. Re: WAN Portal with filters slow. Improvements technique ?
                                BruceRobertson

                                You present this statement as if it disagrees with what I said.

                                But it doesn't.

                                It says exactly the same thing.

                                Design the RELATIONSHIP to limit the related record count as much as possible.

                                A portal filter calculation works on every record of the relationship.

                                • 13. Re: WAN Portal with filters slow. Improvements technique ?
                                  dtcgnet

                                  I 100% agree that relationships should be designed to limit the related record count as much as possible.

                                   

                                  Where I disagree with you is: "Filtered portals are just about the worst thing you can do for FileMaker performance." Filtering a portal with 30,000 related records would cause a performance hit. Filtering a portal with 100 related records won't, UNLESS the table being related to has 30,000 records and the cache has been flushed BEFORE FileMaker can begin to determine which of the 30,000 records is related and should be evaluated to see if the filter criteria is met.

                                   

                                  The portal filter calculation is evaluated for ONLY related records. If only 100 out of 1,000,000 records are related to a given record, then the filter calculation is evaluated ONLY 100 times, and that evaluation is done on the client machine without any data being passed back to the server. Flushing the cache, however, causes FileMaker to say, "I think I'll throw all of this perfectly good data out and ask the server to send it to me again since one of the 1,000,000 records has changed."

                                  • 14. Re: WAN Portal with filters slow. Improvements technique ?
                                    ayescas

                                    thank you all...going to explore all the feedback and get back to you.

                                    1 2 Previous Next