Script Running Way Too Long

[Lawrencex]

Hello FM Pros

Would appreciate some tips for the following script problem:

 I have a situation here where my scripts in FM 13 Pro Adv takes more than 5 hrs to complete a calculation.

OS: This is Win 7 Pro. with 16 GB RAM (i5 Processor.)


I have Field "A" which is a 7 digit code numbers FIELD, which most often are repeating numbers, from 200 different types.

(And I have upto 80 million records, in this field.) Code examples: 1234567, 2345678, 5678012,...

Fields "B" to "Y" (are global Fields) provides a reference number for each type of the 7 digit codes from Field "A". For

example the code 1234567, as above, the reference is, eg. HH/2015/Jul/132, Code 2345678 the reference is, HH/2015/Sep/314,...etc

Field "C" list the reference numbers from Field "B" in the order of the records from Field "A"

I use the following scripts in Field "C"

If [Field A="1234567"]
Perform Script [Replace Field Contents Field C;Field B]
Else If [Field A="2345678"]
Perform Script [Replace Field Contents Field C:Field B]

Else If (...until 200 lines of the references from Field)

End If

The script runs well. However, it takes more than 5 hrs to complete the entire 80 million records.)

Would appreciate your suggestion to speed run the scripts.

Many thanks in advance.

Lawrencex

 

 

[David Jondreau]

It's hard to tell, but it sounds like there are a couple fundamental changes you can make.

You should move to having another table. Instead of a script with 200 If/Else If options, create a table with 200 records. On field would be the code, another field would be the reference.

Then you build a relationship between the two tables and use an auto-enter calculation to set Field B.

It's not entirely clear if Field A contains a single 7 digit number or a series of them (up to 25?). If a series, you should split those up into separate records as well. Also, are you getting 80 Million new records frequently or is this a one time process? You might be able to save a lot of time by using relationships rather than scripts, but 80 million records is a lot to update.

In short, this isn't a scripting problem as much as it is a data structure problem.

It would be helpful as well to have the actual data rather than samples. It is easier to understand. A sample file would be great.

[comment]

I agree with David, with only a minor change. All you need to do is:

1. Add a table of Codes, with two fields (Code and Reference), and populate it with 200 records;

2. Define relationship between the two tables, matching on Code.

There is no need to populate another field in your existing table with the corresponding reference; just place the Reference field from the Codes table on the layout of your existing table (make it non-enterable to prevent accidental modification).