Wiki Home

SCATTERNAMEvs SCATTERMEMVAR


Namespace: VFP
Using SCATTER NAME instead of SCATTER MEMVAR

Performance improvements using SCATTER NAME were so dramatic, thought it would be useful to mention on the Wiki.

Background:
Have program that is run once each year to import a large amount of data into our company's system. Originally used SCATTER MEMVAR because the program was created in older versions of foxpro (before SCATTER NAME was available).

Using SCATTER MEMVAR took more than 8 hours and crashed
At 8+ hours the program had processed around 9,000 records in the parent table and then crashed. The problem seemed memory related because you could see partially drawn screens behind the Windows error message indicating the program crashed.

Using SCATTER NAME took 1 1/2 hours and finished
Suspecting MEMVAR, we changed the program to use SCATTER NAME instead. Then the program took only 1 1/2 hours to run and it successfully processed all 13,000 parent records. I could hardly believe my eyes when I watched it blazing along so much faster with just this minor tweak to the program.

Also, in the past we had about
6,000 records in a parent table and about
20,000 records in a child table

But this year we had about twice as much data
13,000 records in the parent table and about
46,000 in the child table

Below I tried to simplify the code so it makes sense without the specific references to fields we use in our system. The old code just had SCATTER MEMVAR ... GATHER MEMVAR instead of SCATTER NAME ... GATHER NAME.

Example of modified code:

SELECT parent
    APPEND FROM parent.txt TYPE SDF
SELECT child
    APPEND FROM child.txt TYPE SDF
SELECT parent
x = 0
cxNumber = ALLTRIM(STR(RECCOUNT()))
SCAN
     x = x + 1
     WAIT WINDOW "Working on parent "+ALLTRIM(STR(x))+ ;
                 " of "+cxNumber NOWAIT
     SCATTER FIELDS field1, field2, field3, field4, field5 NAME oParent
     *** Call another procedure to do a lot of things to
     *** process record, check against another table
     *** gather additional data, perform calculations, etc.
     REPLACE field6 WITH newdata6, field7 WITH newdata7

     SELECT child
     COUNT TO cyNumber FOR (invoice header data same except invoice#)
     cyNumber = ALLTRIM(STR(cyNumber))
     y = 0
     SCAN FOR (invoice header data same except invoice#)
           y = y + 1
          WAIT WINDOW "Working on Parent "+ALLTRIM(STR(x))+ ;
                      " of "+cxNumber + CHR(13) + ;
                      " Child "+ALLTRIM(STR(y))+ ;
                      " of "+cyNumber NOWAIT
          SCATTER FIELDS field1, field2 NAME oChild
          *** Call another procedure to do a lot of things to
          *** process record, check against another table
          *** gather additional data, perform calculations, etc.
          REPLACE field3 WITH newdata3, field4 WITH newdata4

          *** Apply the same changes to field3 and field4 to
          *** all invoice lineitems in the child table
          *** that are exactly alike except for invoice#
          *** This saves a lot of time since the process
          *** which calculates the value for those fields 
          *** is very intensive and time consuming
          SCATTER NAME oChild2
          DO WHILE (all invoice header and lineitem data same)
               GATEHR NAME oChild2
               SKIP
               y = y + 1
          ENDDO
          ** Have to skip back one record to be on 
          ** the correct record for the SCAN LOOP
          ** since the scan loop will increment the record pointer again
          SKIP -1
          y = y - 1
     ENDSCAN

     *** Apply the same changes to field6 and field7 to
     *** all invoices that are exactly alike except for invoice#
     *** This saves a lot of time since the process
     *** which calculates the value for those fields 
     *** is very intensive and time consuming
     SELECT PARENT
     SCATTER NAME oParent2
     DO WHILE (all invoice data the same except invoice#)
         GATHER NAME oParent2
         SKIP
         x = x + 1
     ENDDO
     ** Have to skip back one record to be on 
     ** the correct record for the SCAN LOOP
     ** since the scan loop will increment the record pointer again
     SKIP -1
     x = x - 1

     SELECT parent
ENDSCAN

Submitted by Shelly Moore
Category Code Samples Category VFP Command Comparison Category VFP Commands
( Topic last updated: 2003.02.05 10:13:56 PM )