6 Replies Latest reply on May 3, 2012 1:55 PM by DavidJondreau

    Go to Related Records slow on large found sets

    rnovakov

      I have a solution that has two tables, where Main has approx. 150,000 records and Secondary has approx. 450,000 records.

       

      The Relationship Graph is pretty straight forward and looks like this. Both key fields are fully indexed.

       

      Screen shot 2012-01-12 at 5.00.40 PM.png

       

      I am trying to find records in the Secondary table and then show all related records from the Main table using the Go to Related Records script step, navigating to a layouts that has fields from the Main table.

       

      If the found set is quite large, then the Go to Related Script step takes a long time, with a Find progress bar appearing.

       

      Is there any way of speeding things? Appreciate your comments and ideas.

       

      Roumen

       

      PS: I am attaching a sample file as well.

        • 1. Re: Go to Related Records slow on large found sets

          Go To Related [ match found set ] can be slow.  I find it is quicker to set a global field with multiline result of IDs and GTRR [ match current record only].

           

          You can use Copy All Records[] from a layout with only the Key or gather the IDs (there are other methods around) ...

          • 2. Re: Go to Related Records slow on large found sets
            comment

            Roumen Novakov wrote:

             

            I am trying to find records in the Secondary table and then show all related records from the Main table

             

            Couldn't you perform the find in the main table to begin with?

            • 3. Re: Go to Related Records slow on large found sets
              Stephen Huston

              Hi Roumen,

               

              Part of the slowness of Go To Related Recors (GTRR) may be related to what is set to display on the target layout. (I am suspicious that your relationship graph image has been  simplified so we aren't seeing all the fields.)

               

              If you have unstored calcs on the GTRR target table, these may produce progress bars.

               

              GTRR also can require significant data caches from served files to resolve the found/related set, and you have a large set of data to resolve in those tables. If you are doing GTRR for the current Found Set instead of the Current Record, that may also increase the caching required.

               

              Stephen Huston

              • 4. Re: Go to Related Records slow on large found sets
                rnovakov

                Thank you, for you reply.

                I included a sample file for you to download.  No unstored calcs, all just text fields.

                Roumen

                • 5. Re: Go to Related Records slow on large found sets
                  Stephen Huston

                  I have now tested your file, and it does seem slow.

                   

                  However, I found that using the GTRR button was faster after the first time I ran it. With both key fields fully indexed, that surprised me a little. The 2nd and later times I did it, it took about 3 seconds, which allowed the progress bar to appear, but at least that let me know it was processing.

                   

                  It seems from the "performing find" progress bar, that FM is using the key fields to perform a multi-search find on the corresponding key in the related table. This means a large found will generate  a longer find request (more search conditions) to process.

                   

                  That being the case, it's logical that performing a GTRR from a local found set will become slower as the local found set increases in size.

                   

                  The other methods suggested might be faster, but only testing will tell.

                  • 6. Re: Go to Related Records slow on large found sets
                    DavidJondreau

                    I agree with LaRetta, grabbing the IDs and setting a global to GTRR from is faster.

                     

                    However, you probably need a custom function to get that performance benefit (you could script a loop too) and with the size of the record sets being in the 100K range, you might be out of luck.