Question Speed Up Csv File Import

jamie_moore

New Member
Hi,

I'm wondering is someone can help me speed up an upload of data I'm trying to perform. I'm new to this so haven't much experience but I've managed to piece together the below which works fine although I would expect it to complete within a few seconds instead it's taking 45 seconds a go. Any help would be appreciated :)

Code:
DEFINE VARIABLE uploadFile AS CHAR LABEL "Upload File Name" FORMAT "x(40)" NO-UNDO.
DEFINE VARIABLE inputCompany AS INT LABEL "Company" FORMAT "99" NO-UNDO.
DEFINE VARIABLE inputDepot AS INT LABEL "Depot" FORMAT "99" NO-UNDO.
DEFINE VARIABLE runTime AS INT FORMAT ">>9999.99" NO-UNDO.

DEFINE TEMP-TABLE ttProdImp
  FIELD pProdCode AS INT
  FIELD pProdName AS CHAR
  FIELD pPack AS CHAR
  FIELD pPip AS INT
  FIELD pCatNo AS CHAR
  FIELD pImpQty AS INT
  INDEX prodImp-key pProdCode DESCENDING.
 
UPDATE uploadFile HELP "Enter Name of File including Full Path"
       inputCompany HELP "Enter Company"
       inputDepot HELP "Enter Depot"
       WITH NO-VALIDATE 1 COLUMNS FRAME IMP-UPLOAD TITLE "IMPOUND UPLOAD QTY CHANGE" 
       CENTERED ROW FRAME-ROW(IMP-UPLOAD) + 4.

ETIME(TRUE).

INPUT FROM VALUE(uploadFile).

DISABLE TRIGGERS FOR LOAD OF stock.

REPEAT:
CREATE ttProdImp.
IMPORT DELIMITER ",":U ttProdImp.
FOR EACH main.stock WHERE company = inputCompany AND loc-cd = inputDepot:
    IF stock.prod-code = pProdCode THEN DO:
      ASSIGN
        stock.imp-qty = ttProdImp.pImpQty.
        /*MESSAGE "Product Code = " + STRING(ttProdImp.pProdCode) 
        "Impound Qty = " + STRING(ttProdImp.pImpQty)*/    
END.
END.
END.
ASSIGN runTime = INT(ETIME) / 1000.
EMPTY TEMP-TABLE ttProdImp.
ETIME(TRUE).
MESSAGE "Upload took = " + STRING(runTime) "secs" VIEW-AS ALERT-BOX.
INPUT CLOSE.
 

Cringer

ProgressTalk.com Moderator
Staff member
I would separate out the load of the temp-table from file and the load of the data to separate loops for starters. Then you need to specify a lock on the for each of main.stock. And you should do the updates against a buffer with a strongly scoped transaction.
Code:
DEFINE VARIABLE uploadFile AS CHAR LABEL "Upload File Name" FORMAT "x(40)" NO-UNDO.
DEFINE VARIABLE inputCompany AS INT LABEL "Company" FORMAT "99" NO-UNDO.
DEFINE VARIABLE inputDepot AS INT LABEL "Depot" FORMAT "99" NO-UNDO.
DEFINE VARIABLE runTime AS INT FORMAT ">>9999.99" NO-UNDO.

DEFINE TEMP-TABLE ttProdImp
  FIELD pProdCode AS INT
  FIELD pProdName AS CHAR
  FIELD pPack AS CHAR
  FIELD pPip AS INT
  FIELD pCatNo AS CHAR
  FIELD pImpQty AS INT
  INDEX prodImp-key pProdCode DESCENDING.
 define buffer bstock for stock. 
UPDATE uploadFile HELP "Enter Name of File including Full Path"
       inputCompany HELP "Enter Company"
       inputDepot HELP "Enter Depot"
       WITH NO-VALIDATE 1 COLUMNS FRAME IMP-UPLOAD TITLE "IMPOUND UPLOAD QTY CHANGE"
       CENTERED ROW FRAME-ROW(IMP-UPLOAD) + 4.

ETIME(TRUE).

INPUT FROM VALUE(uploadFile).

DISABLE TRIGGERS FOR LOAD OF stock.

REPEAT:
CREATE ttProdImp.
IMPORT DELIMITER ",":U ttProdImp.
END.
END.

for each ttProdImp:
FOR EACH main.stock no-lock WHERE company = inputCompany AND loc-cd = inputDepot:
    IF stock.prod-code = pProdCode THEN DO for bstock transaction:
      find bstock exclusive-lock 
where rowid(bstock) eq rowid(stock) no-wait no-error. 
if available bstock then 
     ASSIGN
        bstock.imp-qty = ttProdImp.pImpQty.
        /*MESSAGE "Product Code = " + STRING(ttProdImp.pProdCode)
        "Impound Qty = " + STRING(ttProdImp.pImpQty)*/   
END.
end. 

ASSIGN runTime = INT(ETIME) / 1000.
EMPTY TEMP-TABLE ttProdImp.
ETIME(TRUE).
MESSAGE "Upload took = " + STRING(runTime) "secs" VIEW-AS ALERT-BOX.
INPUT CLOSE.
Coded in the Chrome IDE so compilation and functionality not guaranteed, but it will give you the idea.
 
Hi,

I'm wondering is someone can help me speed up an upload of data I'm trying to perform. I'm new to this so haven't much experience but I've managed to piece together the below which works fine although I would expect it to complete within a few seconds instead it's taking 45 seconds a go. Any help would be appreciated :)

Code:
DEFINE VARIABLE uploadFile AS CHAR LABEL "Upload File Name" FORMAT "x(40)" NO-UNDO.
DEFINE VARIABLE inputCompany AS INT LABEL "Company" FORMAT "99" NO-UNDO.
..........
.........
END.
ASSIGN runTime = INT(ETIME) / 1000.
EMPTY TEMP-TABLE ttProdImp.
ETIME(TRUE).
MESSAGE "Upload took = " + STRING(runTime) "secs" VIEW-AS ALERT-BOX.
INPUT CLOSE.

I don't see why you need the Temp-Table. So, I would remove the temp-table and use only Variables,
and replace
  1. FOR EACH main.stock WHERE company = inputCompany AND loc-cd = inputDepot:
  2. IF stock.prod-code = pProdCode THEN DO:

By

  1. FOR EACH main.stock EXCLUSIVE-LOCK WHERE company = inputCompany AND loc-cd = inputDepot AND stock.prod-code = pProdCode:
 

TomBascom

Curmudgeon
The temp-table should be No-UNDO.

The copy from the TT to the db table should be done in chunks. Untested pseudo-code follows:
Code:
define buffer updStock  for stock.
define variable i as integer no-undo.

chunk: do for updStock while true transaction:
  for each ttProdImp:
    for each updStock exclusive-lock where ...
      /* the rest of that logic */
    end.
    i = i + 1.
    if i modulo 100 = 0 then next chunk.  /* this commits all of the accumulated sub-stransactions */
  end.
  leave.
end.

By updating in chucks of 100 records you are greatly reducing the number of synchronous write operations needed to complete the task. (You can fiddle with the chunk size but 100 seems to be a pretty good starting place most of the time.)
 

Cringer

ProgressTalk.com Moderator
Staff member
If these tips don't help, then letting us know details of the indexes on stock will help us to identify if there's an issue with the query to find the relevant records.
 

jamie_moore

New Member
Thanks for all the feedback guys being a massive help :)

I've tested out Cringer's method and seen a ~50% speed increase which is great although on a 50 record test it's still taking 26 seconds as opposed to 49 seconds before, when I'm expecting ~1-2 seconds run time.

Tom I tried to incorporate the Chunck method you suggested with Cringers code and was unsuccessful so I'm not sure what I'm doing wrong there but I suspect it wouldn't impact a 50 record test anyway and will only come into effect when I start updating 1000's records?

Details of the stock indexes are as follows:

Table: stock

Flags Index Name St Area Cnt Field Name
pick-key 200 3 + company
+ loc-cd
+ fwd-loc

pu stk-key 200 3 + company
+ prod-code
+ loc-cd

u sup-key 200 4 + company
+ loc-cd
+ supplier
+ prod-code
 

Cringer

ProgressTalk.com Moderator
Staff member
Code:
DEFINE VARIABLE uploadFile AS CHAR LABEL "Upload File Name" FORMAT "x(40)" NO-UNDO.
DEFINE VARIABLE inputCompany AS INT LABEL "Company" FORMAT "99" NO-UNDO.
DEFINE VARIABLE inputDepot AS INT LABEL "Depot" FORMAT "99" NO-UNDO.
DEFINE VARIABLE runTime AS INT FORMAT ">>9999.99" NO-UNDO.

DEFINE TEMP-TABLE ttProdImp
  FIELD pProdCode AS INT
  FIELD pProdName AS CHAR
  FIELD pPack AS CHAR
  FIELD pPip AS INT
  FIELD pCatNo AS CHAR
  FIELD pImpQty AS INT
  INDEX prodImp-key pProdCode DESCENDING.
 define buffer bstock for stock.
UPDATE uploadFile HELP "Enter Name of File including Full Path"
       inputCompany HELP "Enter Company"
       inputDepot HELP "Enter Depot"
       WITH NO-VALIDATE 1 COLUMNS FRAME IMP-UPLOAD TITLE "IMPOUND UPLOAD QTY CHANGE"
       CENTERED ROW FRAME-ROW(IMP-UPLOAD) + 4.

ETIME(TRUE).

INPUT FROM VALUE(uploadFile).

DISABLE TRIGGERS FOR LOAD OF stock.

REPEAT:
CREATE ttProdImp.
IMPORT DELIMITER ",":U ttProdImp.
END.
END.

for each ttProdImp:
FOR EACH main.stock no-lock WHERE company = inputCompany and stock.prod-code = pProdCode AND loc-cd = inputDepot:
    DO for bstock transaction:
      find bstock exclusive-lock
where rowid(bstock) eq rowid(stock) no-wait no-error.
if available bstock then
     ASSIGN
        bstock.imp-qty = ttProdImp.pImpQty.
        /*MESSAGE "Product Code = " + STRING(ttProdImp.pProdCode)
        "Impound Qty = " + STRING(ttProdImp.pImpQty)*/ 
END.
end.

ASSIGN runTime = INT(ETIME) / 1000.
EMPTY TEMP-TABLE ttProdImp.
ETIME(TRUE).
MESSAGE "Upload took = " + STRING(runTime) "secs" VIEW-AS ALERT-BOX.
INPUT CLOSE.

Based on those indexes, you have a suitable index to add the test on Prod Code to the query which means you'll be hitting a lot less stock records you don't need. See my tweak above. There's ways of finding out how many records you're reading vs how many are useful, but I'm presuming the actual effort here isn't with the processing of the csv, or even the updating of the records, rather it's the finding of the records to update.
The only other question I'd ask, does main.stock have any write triggers defined?[/QUOTE]
 
Top