I've got a MODIFY script that loads a hold file into a table. The job is fast if the hold file is small, and slow if it is large, un-surprisingly.
The table itself is very basic; five fields, no keys. I think the MODIFY job is doing inserts row by row, and I feel that there are probably some performance tuning opportunities here.
This is my code:
-SET &ECHO=ALL;
-SET &UNIQUE_ID=&UNIQUE_ID+0;
-SET &HOLDNAME='BORs_'||'&UNIQUE_ID.EVAL'||'_'||'&YYMD.EVAL'||'_'||'&TOD.EVAL';
SET ASNAMES=ON
TABLE FILE FOCCACHE/STEPONE
PRINT
ID AS BOR_ID
COMPUTE UNIQUEID/P20=&UNIQUE_ID;
COMPUTE USER_ACCT/A100='&IBIMR_user';
COMPUTE LOG_DATE/A10='&YYMD';
COMPUTE LOG_TIME/A8='&TOD';
ON TABLE HOLD AS &HOLDNAME
END
-SET &NUMBEROFLINES=&LINES;
-** Load the data into the SQL Server table
MODIFY FILE SUMARYFORINVOICING_BOR_IDS_TO_UNIQUE_ID_V2
FIXFORM FROM &HOLDNAME
MATCH BOR_ID UNIQUEID USER_ACCT LOG_DATE LOG_TIME
ON NOMATCH INCLUDE
ON MATCH REJECT
CHECK 2000
DATA ON &HOLDNAME
END
-RUN
Any advice on making this fly?
Cheers,
JoeyThis message has been edited. Last edited by: <Kathryn Henning>,
-WebFOCUS 8.2.01 on Windows
June 27, 2013, 04:30 PM
David Briars
You might want to take a look at the Modify Fastload facility. The syntax is: SQL SET LOADONLY, after the MODIFY command.
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
June 27, 2013, 06:11 PM
Waz
Just what I was thinking as well.
FYI
By default, MODIFY will select the record from the DB to see if it needs to update or insert.
I believe that the LOADONLY setting skips the select.