1 2 Previous Next 17 Replies Latest reply on Feb 24, 2013 11:38 AM by PaulWebb

    Solution taking over 6 min to perform a find

    PaulWebb

      I've been working with FM Pro 11/12 Adv for the last year. I recently received funding to purchase FM 12 Server Adv for a 3 month beta. My file runs with no issues on my local machine. After uploading it to server I am seeing big delays with the following script.

       

      The script in the image is taking 6+ mins to run. Specifically the last step: Go to Related Record. There are less than 5k records in the DB. I will everntually clear out all records and connect directly to an Oracle DB to show records.

      Screen Shot 2013-01-26 at 11.33.43 AM.png

       

      Server:

      FM 12 Server Adv - 12.0.2.232 - Planning to have this upgrade to 12.0.3 this coming week.

      I increased the DB cache to the max allowed as suggested by the config guide. 2045MB

      Running on Microsoft Windows Server 2008 R2 x64 (6.1.7601, Service Pack 1), with 8 logical processor core(s)

      The only access I have to the server is remotely via the Admin Console.

       

      I created a compact copy which did not help and I tried to recover the file and there were no issues found. Need some direction on where to investigate or things to try. I'm afraid the poor performance could kill my project!

       

      Thanks for reading and any direction you can provide.

        • 1. Re: Solution taking over 6 min to perform a find
          BowdenData

          Paul,

           

          Is the solution all in one file or is it split up?  If split what do your external file ref's look like?

           

          Looking at script, not sure you are going to the dev layout first but if needed, either make sure that layout is in form view or better yet, create a blank layout for the SR table with the only view it can do being form view.  

           

          Make sure the target layout in RMA_Overall is a form view as well. If you don't need to interact with the target layout, create a blank one there as well 

           

          HTH

           

          Doug

           

          Sent from my iPhone

          • 2. Re: Solution taking over 6 min to perform a find
            PaulWebb

            Yes the solution is all in one file.

             

            the problem is centered around the Got to related record step. I created a script with just the one step and started on the dev_sr layout and it is still taking 6+ min. The RMA layout is a list layout.

             

            The solution is 99% a reporting tool. Meaning that users will only correct some field and have a notes field. The SR's are trouble tickets and the RMA's tie to those. the relationship is SR.SR#---->RMAOverall.SR#. I need to double check to make sure I am using the right relationship.

            • 3. Re: Solution taking over 6 min to perform a find
              wimdecorte

              Look at the client stats in the FMS admin console to see which one of the traditional bottlenecks generatest the biggest numbers, that should give you some things to focus on.

               

              Also, try to point the GTRR to a blank layout instead of the list layout and note the difference in speed if any.  If there is huge difference then it is something on your layout (unstored calcs, summary fields, sort order,...)

              • 4. Re: Solution taking over 6 min to perform a find
                DavidJondreau

                Don't use GTRR and Match Found Set. It's notoriously slow.

                • 5. Re: Solution taking over 6 min to perform a find
                  DrewTenenholz

                  Paul --

                   

                  I;ve been following your thread, and have two suggestions I didn't see mentioned yet.  Whenever I see a torturously slow process involving GTRR or a portal, then I usually find that either:

                   

                  1) The relationship I specify in GTRR is 'incorrect'.  There are a couple of ways that can go wrong, including a TO that is not really connected to the starting TO, a very elaborate path from the starting TO to the final TO, a path between the TOs that involves going through a TO that will always match all records in an intermediate TO before going to the intended set, using an intermediate TO from a chain when what you really need to do is create a new TO on the end of the chain, having renamed some relationships or moving around linkages on the graph and forgetting that the path is now horribly wrong or the wrong name was used, and probably a few more.

                   

                  This can sometimes be detected by going to the destination layout, using show all>show omitted to have a zero found set, then going back to the starting layout, hitting the button (waiting six minutes.....) and then seeing that the found set is not precisely what you expect, usually having too many records displayed.

                   

                  2) There is an element on the destination layout which references a TO that is either impossible, difficult, or unrelated to the TO of the destination layout.  This can happen if you copy/paste a portal from some other layout that really needed to be completely re-pointed but have missed an element (look at the portal's native TO, the portal's sort order , and, of course the portal fields).  Having a related field on the layout outside of a portal, but still using a complicated relationship will also cause a huge delay in drawing the screen, as FMPro desperately tries to solve the path for one related field (possibly pulling a LOT of record data) before drawing the layout. 

                   

                  I can sometimes spot these because the delay is apparent with both the GTRR and also navigating from one record to the next in the destination found set, in list view or record view.

                   

                  HTH,

                  Drew Tenenholz

                  • 6. Re: Solution taking over 6 min to perform a find
                    gdurniak

                    With only 5,000 records,  it should not take 6 minutes,  so don't give up

                     

                    are all your match fields indexed ?

                     

                    The fact that it runs faster locally is normal, but server should not be much worse.  Try "pinging" your network connections,  and look for errors.  I once had a bad patch cord that killed performance

                     

                    Does it run better on server 11 than 12 ?  if so,  report it to FileMaker as a "bug"

                     

                    greg

                     

                     

                    > My file runs with no issues on my local machine. After uploading it to server I am seeing big delays with the following script.

                    • 7. Re: Solution taking over 6 min to perform a find
                      PaulWebb

                      No change going to a blank layout. It is something with the relationship. Gonna have to learn more about the script step.

                      • 8. Re: Solution taking over 6 min to perform a find
                        DavidJondreau

                        How many records are in the found set before the GTRR?

                         

                        Think of it this way...GTRR somehow uses the Find functionality. When you have a found set of 5 records and GTRR "matching found set", FM constructs 5 finds. If you have 1,000 records, it's evaluating 1,000 find requests.

                         

                        I've seen this behavior consistently since GTRR match found set was introduced. Changing the relationship won't help and this isn't a Server issue. It's a GTRR issue. You need to find an alternative.

                        • 9. Re: Solution taking over 6 min to perform a find
                          Mike_Mitchell

                          Paul -

                           

                          The fact that the solution runs okay locally but crawls when loaded to Server suggests that the issue is coming from the client-server connection. Assuming your server hardware is okay, and the server isn't overloaded doing other things, I would check network bandwidth issues. Specifically, how "wide" is the targetted (related) table? How many fields are in it? How much data is included in each record? Because GTRR has to evaluate (and load) the entire found set for each record, it has to download the entire related found set - every field in every record.

                           

                          This is not an issue when you're running locally, because there's no network pipe. But when you're pushing data down the wire, it becomes an issue very quickly. So, I would try two things:

                           

                          1) See if you can eliminate extra fields in the targetted table.

                          2) Test the script using a small number of parent records versus a larger number of parent records. Performance should be roughly linear - it should be, say, twice as slow with twice as many related records (assuming the bandwidth issue is a problem).

                           

                          Give that a try and see what happens.

                           

                          Mike

                          • 10. Re: Solution taking over 6 min to perform a find
                            PaulWebb

                            Only one record in the found set. All accounts are based on a portfolio number that contains contracts and so on. So I search for the portfolio they are working on.

                            • 11. Re: Solution taking over 6 min to perform a find
                              PaulWebb

                              Thanks Mike. I'll give it a shot.

                              • 12. Re: Solution taking over 6 min to perform a find
                                PaulWebb

                                I don't have any access to the server machine. I can ping it though and will try that and see what happens.

                                 

                                I never had it on 11. I just got server for the first time last week so this is the first solution I have ever had on a server.

                                 

                                As a workaround I disabled the GTRR. I now have it going to my developer layout directly and performing a find then back to the layout I need to be on. This happens in a blink of an eye which leads me to believe the problem is my understanding of GTRR and the relationship.

                                 

                                Boss man said I could go to DevCon this year so long as my project does not get killed after this 3 month trial. I need four solid days of nothing but FileMaker. DevCon or bust!

                                • 13. Re: Solution taking over 6 min to perform a find
                                  Vaughan

                                  Paul Webb wrote:

                                   

                                  Boss man said I could go to DevCon this year so long as my project does not get killed after this 3 month trial. I need four solid days of nothing but FileMaker. DevCon or bust!

                                   

                                  Also think about getting a good developer in to sit with you an analyse what youve done, and get ideas for imporvement. It might be a couple of hours well spent, and it'll be very targeted information.

                                  • 14. Re: Solution taking over 6 min to perform a find
                                    PaulWebb

                                    Definitely something I'm hoping for but as of now I'm begging for dollars. Once they see the value it will be easier.

                                    1 2 Previous Next