9 Replies Latest reply on Dec 21, 2016 7:57 AM by noto

    Perform find, related field vs calculation field

    noto

      Hi, I´m using the Perform Find [Restore] step in a script.

      I have to find FieldA =1 and Field8(from related Table)=0

      The Find in progress... is very slow.

      Is there any way of speed this?

      I tried creating a calculation field (Field8(from related Table)) but I see no diference.

      PS: Lot of records both tables!

      Thank you!

       

        • 1. Re: Perform find, related field vs calculation field
          ninja

          How many records is "lots" ?

           

          Are either of these fields unstored calculations?  If so, referencing fields from where?

          • 2. Re: Perform find, related field vs calculation field
            philmodjunk

            You may not have seen a difference, but you might get different results if there is more than one matching record in the related table. Your calculation field will copy over only the "first" related record where a find on the field from the related table will find records in your first table if any related record linked to it meets your specified criteria.

             

            There are no guarantees that it would be quicker, but you could try finding all records on table 1 where FieldA = 1, then return to find mode and constrain the found set to just those where Field8 = 0.

             

            This can be much quicker if the number of records found where FieldA = 1 is fairly small as the constrain only operates over the records in your current found set.

            1 of 1 people found this helpful
            • 3. Re: Perform find, related field vs calculation field
              keywords

              You may find it faster to split the Find into two stages:

              1.     Find the records which match FieldA = 1

              2.     Perform a second Find to either Constrain or Extend (I'm not clear which you would want) for Field8 = 0

               

              A couple of other points though:

              3.     For parsing your script—and certainly for getting assistance on this forum—most developers recommend not using Perform Find [Restore], because the details of the Find criteria are embedded. In stead use—

              Enter Find Mode [ ]

              Set Field [ ]

              Perform Find [ ]

              4.     For clarity, not to mention your own sanity, use descriptive field names, not names like FieldA and Field8 (and presumably B, C, … and 1, 2, …)

               

              (EDIT: I see Phil and I overlapped somewhat. His was posted while I was working.)

              1 of 1 people found this helpful
              • 4. Re: Perform find, related field vs calculation field
                philmodjunk

                Scraping your chocolate off of my peanut butter....

                • 5. Re: Perform find, related field vs calculation field
                  David Moyer

                  For those who don't appreciate the best "candy bar" on the planet, the Reese's Peanut Butter Cup, or those who don't get the reference ... here's a commercial from the deep, deep past - just after disco died ...

                  Hey You Got Peanut Butter in My Chocolate High Quality VHS rip 1981 - YouTube

                  1 of 1 people found this helpful
                  • 6. Re: Perform find, related field vs calculation field
                    noto

                    Thank you!

                    There are 222755 in one table and 140308 in the other.

                    Both fields are unstored calculations referencing related tables.

                    • 7. Re: Perform find, related field vs calculation field
                      ninja

                      Yup, thats going to be slow.  Others have posted ideas below for ways that might help.

                       

                      The biggest help will be if you can make these fixed values, or at least indexed.

                       

                      In some cases, triggering a script to change a value rather than having an unstored clac is a better way to go.

                      More work to set up...but MUCH faster should you need to do a find.  Life is balance...

                       

                      Example:

                      I can get a current bank balance by adding up all the deposits (related table) and subtracting all the withdrawals (related table) into an unstored calc field.  When I do a find, it has to do all the math over again for every record...the value is unstored.

                       

                      I can get the same result by setting the current balance into a fixed field each time a deposit or withdrawal is made...stored value of current balance...much faster find with no math to do during the find or sort.

                       

                      The second way is more work to set up in the first place, but the usability of it later is much faster.

                       

                      HTH

                      1 of 1 people found this helpful
                      • 8. Re: Perform find, related field vs calculation field
                        philmodjunk

                        Yes, but the key to getting much better performance with a two stage find (find on indexed field in layout's table followed by constrain on the found set), is how many records typically are found by the first stage of the find. If this number is fairly small, the resulting performance boost can be substantial.

                         

                        I discovered this when I needed to search some "invoice type" records with a parent record, line items and an unstored line item total in the parent record. I had a stored number set by script for the total, but we had a problem that produced a discrepancy between the two values that I needed to resolve. I needed to find all such "invoices" for a given date where the total value returned by the unstored calculation field was less than a specified amount. Since this table had millions of records, a straight up find could take quite a few minutes and adversely affected server performance for other clients. But doing a find for all records with a specified date, -- which found no more than a few hundred records, and then constraining by the unstored invoice total got me what I needed in seconds.

                        1 of 1 people found this helpful
                        • 9. Re: Perform find, related field vs calculation field
                          noto

                          Thank you all for sharing knowledge!

                           

                          I´m using the find mode set field perform find method, I remember better results doing this, and as you said before, is well documented in the forum.

                           

                          The names were alias to illustrate the case! But thanks for the advice.

                           

                          I don´t see the possibility in the near future to change the calculation fields, but the tip is very appreciated and will use it for sure.

                           

                          Again,

                          Thank you all for sharing knowledge!