Focal Point
[CLOSED] MODIFY performance tuning

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4057087136

June 27, 2013, 03:59 PM
Moogle
[CLOSED] MODIFY performance tuning
Hi Team,

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,

Joey

This 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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!