Question about speed of a script

[Bimsickle]

I have a script that builds a HTML List and saves it as a variable used to display a cross tab report.

 

If I run a full script it can be quite slow.

 

The process is

 

Go to Sales_Lines table

find customer type, Date range

loop

Selected Rep/reps

loop

selected products

end loop

end loop

 

then on each line I grab the customer ID, and check the date range, if it falls into previous year, check product ID against first in list, then loop through following products.

Next line, check again.

 

For speed purposes, would it be quicker to perform multiple finds & sorts rather than one find, with multiple checks?

Each line currently get checked

If the Customer ID is the same as the last

If the date is in the range of last year or this year

If product ID = group of ID's

if product ID = x on list (looping through the list).

 

Next line

 

As a change, perform a find for set things

 

Initial find sales within range for selected products/ reps

Build a list of all valid customer ID's

 

then a find for product 1 within date range x

check if each line matches GetValue($$CustID;$Counter) - looping through and increasing the count until a match is found, build lists

 

then find for product 2 with date range x

check if each line matches GetValue($$CustID;$Counter) - looping through and increasing the count until a match is found, build lists

 

then a find for product 1 within date range xx

check if each line matches GetValue($$CustID;$Counter) - looping through and increasing the count until a match is found, build lists

 

And a find for product 2 within date range xx

check if each line matches GetValue($$CustID;$Counter) - looping through and increasing the count until a match is found, build lists

 

 

I just don't want to write the monster script again if it won't be any faster.

 

There currently is

5,500 Expected customer ID's

300,500 matched items lines out of 590,650 possible

21 reps

23 products

2 date ranges

[Steve Martino]

Could you post your exact script?

[Bimsickle]

I made a sample to address another question, so I will attached the same file.

 

Although this one is a little buggy from memory.

 

I have made a few additions like adding a growth calculation before adding the record to the final line.

 

It's just doing so many checks on each line, I was thinking it may be quicker to do a search for the list of ID's, then multiple searches for each "check"...

 

It's pretty quick(ish) for one rep, about a minute or two, but takes almost an hour for 20.

TableSorter_CrossTab.fmp12.zip

[Steve Martino]

For whatever reason the forum wont let me download the file. Can you post the script or a screenshot of the script

[AHunter3]

You're doing a multiple-request Find in which each Find request includes a value in Customers::Sales Rep, which is a table two relationships away from the Lines table where you're doing the find.

 

Yeah, that's going to be dog-slow all right.

 

Create a field in Lines, Sales Rep, and set it up to be a lookup field that looks up values from Customers.

 

Then in your script, do your find on Lines::Sales Rep not on the distant related field Customers::Sales Rep.