>Hi,
>
>I have this program to split the address field. The program scans
>a table record by record and updates certain fields. What it
>really does is, it splits the value in address field into four values
>namely StreetNo, StreetName, Predirectional, SecondaryAddr. It then
>inserts these values into the corresponding fields for each record.
>I am not altering the structure of the table. I already have the
>extra fields (streetname, streetno etc.) but only that these fields
>are empty and they are updated by this program.
>
>The table has over 2 million records and I want to make sure that this
>job is done in the shortest time possible. Could anyone suggest the
>things that I should take care of.
>
>Here is a snippet of the code:
>
> SELECT MYTABLE
> IF RECCOUNT() > 0
> SCAN
> ADDRESSVALUE=MYTABLE.ADDRESS
> STANDARDIZE(ADDRESSVALUE)
> REPLACE STREETNAME WITH STNAME, STREETNO WITH STNO, PREDIRECTIONAL WITH PREDIR, SECONDARYADDR WITH SECADR
> ENDSCAN
> ENDIF
>
>STANDARDIZE IS A PROGRAM THAT SPLITS THE ADDRESS FIELD INTO
>THE VARIABLES STNAME,STNO,PREDIR,SECADR.
>
>ANY SUGGESTIONS WOULD BE MUCH APPRECIATED.
>
>RIA

You're pretty much optimized with what you have but with 2 million records a fraction here or there could add up. Here's some suggestions:

1) use STANDARDIZE(MYTABLE.ADDRESS) to skip the assignment to ADDRESSVALUE

2) move the STANDARDIZE() code into the SCAN..ENDSCAN loop to save the 2 million function calls.

Other than that, maybe STANDARDIZE() could be optimized. There are many different ways to parse strings.

Mark