IBM i SQL is much faster than CPYF

  • Posted on: 18 March 2012
  • By: NickLitten

 

For the longest time I've heard about the performance difference between a true SQL INDEX and a DDS ACCESS PATH generated index on the IBM i operating system (or 'OS400' which was a ten year old, but very commonly used version of the the same OS). But is it true? I know that SQL creates its own special access path... and have heard vague rumblings about how it just does things different so its quicker... and I've read many times that us IBM i programmers should be creating files using SQL rather than the old DDS.

 

So, as part of the project I'm working on we have to copy data from a bunch of rather large files... so I did some real time comparisons and got some fascinating results.

Timings for the big Fight:

Using a physical file with 141,988,325 records in it I ran a (1) batch copy into a DDS version of the file using both CPYF and SQL INSERT and then (2) created a SQL/DDL version of the file and repeated the exercise. Both times running in a single thread job queue when the machine was quiet.

Times with a *PF created from DDS

Copy file with 142 million records on an IBMi DDS compiled physical file:

  • DDS –> DDS using CPYF took 19 minutes  (it used 99 seconds processing unit time)
  • DDS –> DDS using SQL INSERT took 18 minutes  (it used 93 seconds processing unit time)

Close but no huge benefits!

Times with a TABLE created using SQL

So, now its time to try SQL, so I deleted and recreated the file using DDL (SQL) source code:

  • DDS –> DDL using CPYF 15 minutes (it used 121 seconds processing unit time)
  • DDS –> DDL using SQL INSERT  10 minutes (after it used 212 seconds processing unit time)

So SQL wins.

 No Doubt about that. Or is there? Sure it ran 30% faster in physical time... but it used nearly 100% more CPU! I guess you can make your own mind up.

Here is the CPYF I ran:

  • CPYF FROMFILE(GRLP_SV) TOFILE(GRLP_NEW) MBROPT(*REPLACE) FMTOPT(*MAP *DROP)  

Should be timed in batch against:

  • RUNSQLSTM SRCFILE(QGPL/QSQLSRC) SRCMBR(GRLP) COMMIT(*NONE)    

 

Which runs this:

 INSERT INTO grlp_new                                                   

(KYDTRL,SEQRL,TRIDRL,ACSQRL,WINGRL,RMNOSIDRL,RTUSRL,RTEPRL,CONVRL,PKGRL, STRGRL,EFDTRL,DTLKRL,MPLNRL,POPTRL,BPRCRL,AMADRL,CRADRL,SCADRL,DEPPRL, CANPRL,USCRRL,USLMRL,RACORL,GRPURL,CLNMRL,CAL#RL,IDRL,CMTRL)           

SELECT                                                                

KYDTRL,SEQRL,TRIDRL,ACSQRL,WINGRL,RMNOSIDRL,RTUSRL,RTEPRL,CONVRL,PKGRL,
STRGRL,EFDTRL,DTLKRL,MPLNRL,POPTRL,BPRCRL,AMADRL,CRADRL,SCADRL,DEPPRL, CANPRL,USCRRL,USLMRL,RACORL,GRPURL,CLNMRL,CAL#RL,IDRL,CMTRL FROM grlp_sv                                                           

So now we create the DDL version of the file:

RUNSQLSTM SRCFILE(VDLITTENN/QSQLSRC) SRCMBR(GRLP_NEW) COMMIT(*NONE)

DDS Source code looks like this:

     ?* ------------------------------------------------------------- *

     ?*?   _Ver.000 Guest History Rate Lock File                 ?    *

     ?*?    source retrieved using?projex/4i - rtvddssrc?command     *

     ?* ------------------------------------------------------------- *

     ?* file name................?GRLP_SV   ?                         *

     ?* retrieved from library...?VENCUSTUG ?                         *

     ?* retrieved by user........?VDLITTENN ?on?14/03/12?             *

     ?* type of file (pf or lf)..?*PHY?     ?                         *

     ?* format level identifier..?33C589C42340C?                      *

     ?* no. of fields in file....?00029?    ?                         *

     ?* ------------------------------------------------------------- *

     A                                      REF(LMSRC15/FLDREFP)

     A          R GRLR

     A                                      TEXT('rate lock record            ')

     ?* ------------------------------------------------------------- *

     A            KYDTRL    R               REFFLD(KYDT FLDREFP)

     A                                      TEXT('key date for this record')

     A                                      COLHDG('KEY' +

     A                                      'DTE')

     A            SEQRL     R               REFFLD(SEQ FLDREFP)

     A                                      TEXT('seq num')

     A                                      COLHDG('SEQ' +

     A                                      'NUM')

     A            TRIDRL    R               REFFLD($KEYSEQ FLDREFP)

     A                                      TEXT('trip uniquifier')

     A                                      COLHDG('KEY' +

     A                                      'SEQ')

     A            ACSQRL    R               REFFLD(SEQ FLDREFP)

     A                                      TEXT('accomodation sequence')

     A                                      COLHDG('ACOM' +

     A                                      'SEQ')

     A            WINGRL    R               REFFLD(WING FLDREFP)

     A                                      TEXT('wing code')

     A                                      COLHDG('WING')

     A            RMNOSIDRL R               REFFLD(RMNO FLDREFP)

     A                                      TEXT('room number/suite id')

     A                                      COLHDG('ROOM')

     A            RTUSRL    R               REFFLD(RTUS FLDREFP)

     A                                      TEXT('rate type used')

     A                                      COLHDG('RTE' +

     A                                      'TYP' +

     A                                      'USE')

     A            RTEPRL    R               REFFLD(RTEP FLDREFP)

     A                                      TEXT('rate plan')

     A                                      COLHDG('RATE' +

     A                                      'PLAN')

     A            CONVRL    R               REFFLD(CONV FLDREFP)

     A                                      TEXT('conv')

     A                                      COLHDG('CONV')

     A            PKGRL     R               REFFLD($BILLCODE FLDREFP)

     A                                      TEXT('package')

     A                                      COLHDG('BILL' +

     A                                      'CODE')

     A            STRGRL    R               REFFLD(STRGRT FLDREFP)

     A                                      TEXT('strategy code')

     A                                      COLHDG('STRG CODE')

     A            EFDTRL    R               REFFLD(EFDT FLDREFP)

     A                                      TEXT('effective date')

     A                                      COLHDG('EFF' +

     A                                      'DATE')

     A                                      EDTCDE(3)

     A            DTLKRL    R               REFFLD($HUNYRDTE FLDREFP)

     A                                      TEXT('date locked')

     A                                      COLHDG('LCK' +

     A                                      'DTE')

     A            MPLNRL    R               REFFLD(MPLN FLDREFP)

     A                                      TEXT('meal plan')

     A                                      COLHDG('MEAL' +

     A                                      'PLAN')

     A            POPTRL    R               REFFLD(POPT FLDREFP)

     A                                      TEXT('posting option')

     A                                      COLHDG('PST' +

     A                                      'OPT')

     A            BPRCRL    R               REFFLD($LOCCURR FLDREFP)

     A                                      TEXT('room rate')

     A                                      COLHDG('AMOUNT')

     A                                      EDTCDE(J)

     A            AMADRL    R               REFFLD($LOCCURR FLDREFP)

     A                                      TEXT('add to room amount')

     A                                      COLHDG('ADD' +

     A                                      'ROM' +

     A                                      'AMT')

     A                                      EDTCDE(J)

     A            CRADRL    R               REFFLD($LOCCURR FLDREFP)

     A                                      TEXT('meal credit amount')

     A                                      COLHDG('MEL' +

     A                                      'CRD' +

     A                                      'AMT')

     A                                      EDTCDE(J)

     A            SCADRL    R               REFFLD($LOCCURR FLDREFP)

     A                                      TEXT('svc charge amount')

     A                                      COLHDG('SVC' +

     A                                      'CHG' +

     A                                      'AMT')

     A                                      EDTCDE(J)

     A            DEPPRL    R               REFFLD(CODEDP FLDREFP)

     A                                      TEXT('dep code')

     A                                      COLHDG('CODEDP')

     A            CANPRL    R               REFFLD(CODECP FLDREFP)

     A                                      TEXT('cnl code')

     A                                      COLHDG('CODECP')

     A            USCRRL    R               REFFLD($USER FLDREFP)

     A                                      TEXT('basic definition of user nam')

     A                                      COLHDG('OPERATOR')

     A            USLMRL    R               REFFLD($USER FLDREFP)

     A                                      TEXT('basic definition of user nam')

     A                                      COLHDG('OPERATOR')

     A            RACORL    R               REFFLD(RACO FLDREFP)

     A                                      TEXT('rated after cutoff')

     A                                      COLHDG('RAT' +

     A                                      'AFT' +

     A                                      'C/O')

     A            GRPURL    R               REFFLD(GRPU FLDREFP)

     A                                      TEXT('group pickup when rated')

     A                                      COLHDG('GRP' +

     A                                      'P/U' +

     A                                      'RAT')

     A                                      EDTCDE(Z)

     A            CLNMRL    R               REFFLD(CALENDAR FLDREFP)

     A                                      TEXT('calendar name definition')

     A                                      COLHDG('CALENDAR')

     A            CAL#RL    R               REFFLD(CAL# FLDREFP)

     A                                      TEXT('calendar id code')

     A                                      COLHDG('CAL' +

     A                                      'ID' +

     A                                      'COD')

     A            IDRL      R               REFFLD(RATID FLDREFP)

     A                                      TEXT('display rate id')

     A                                      COLHDG('RAT' +

     A                                      'DSP' +

     A                                      'ID')

     A            CMTRL     R               REFFLD(CMNT FLDREFP)

     A                                      TEXT('comments')

     A                                      COLHDG('COMMENTS')

     ?* ------------------------------------------------------------- *

Generated using PROJEX command RTVDDSSRC

 

DDL (SQL) Source looks like this:

--  Generate SQL

--  Version:                   V5R4M0 060210

--  Generated on:              03/14/12 09:35:18

--  Relational Database:

--  Standards Option:          DB2 UDB iSeries


CREATE TABLE VENCUSTUG/GRLP_NEW (

  KYDTRL DECIMAL(5, 0) NOT NULL DEFAULT 0 ,

  SEQRL DECIMAL(7, 0) NOT NULL DEFAULT 0 ,

  TRIDRL DECIMAL(7, 0) NOT NULL DEFAULT 0 ,

  ACSQRL DECIMAL(7, 0) NOT NULL DEFAULT 0 ,

  WINGRL CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,

  RMNOSIDRL CHAR(5) CCSID 37 NOT NULL DEFAULT '' ,

  RTUSRL CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,

  RTEPRL CHAR(7) CCSID 37 NOT NULL DEFAULT '' ,

  CONVRL CHAR(7) CCSID 37 NOT NULL DEFAULT '' ,

  PKGRL CHAR(5) CCSID 37 NOT NULL DEFAULT '' ,

  STRGRL CHAR(5) CCSID 37 NOT NULL DEFAULT '' ,

  EFDTRL DECIMAL(5, 0) NOT NULL DEFAULT 0 ,

--  SQL150D   10   EDTCDE in column EFDTRL ignored.

  DTLKRL DECIMAL(5, 0) NOT NULL DEFAULT 0 ,

  MPLNRL CHAR(4) CCSID 37 NOT NULL DEFAULT '' ,

  POPTRL CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,

  BPRCRL DECIMAL(9, 2) NOT NULL DEFAULT 0 ,

--  SQL150D   10   EDTCDE in column BPRCRL ignored.

  AMADRL DECIMAL(9, 2) NOT NULL DEFAULT 0 ,

--  SQL150D   10   EDTCDE in column AMADRL ignored.

  CRADRL DECIMAL(9, 2) NOT NULL DEFAULT 0 ,

--  SQL150D   10   EDTCDE in column CRADRL ignored.

  SCADRL DECIMAL(9, 2) NOT NULL DEFAULT 0 ,

--  SQL150D   10   EDTCDE in column SCADRL ignored.

  DEPPRL CHAR(7) CCSID 37 NOT NULL DEFAULT '' ,

  CANPRL CHAR(7) CCSID 37 NOT NULL DEFAULT '' ,

  USCRRL CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,

  USLMRL CHAR(10) CCSID 37 NOT NULL DEFAULT '' ,

  RACORL CHAR(1) CCSID 37 NOT NULL DEFAULT '' ,

--  SQL150D   10   VALUES in column RACORL ignored.

  GRPURL DECIMAL(5, 0) NOT NULL DEFAULT 0 ,

--  SQL150D   10   EDTCDE in column GRPURL ignored.

  CLNMRL CHAR(7) CCSID 37 NOT NULL DEFAULT '' ,

  CAL#RL DECIMAL(3, 0) NOT NULL DEFAULT 0 ,

  IDRL CHAR(6) CCSID 37 NOT NULL DEFAULT '' ,

  CMTRL CHAR(25) CCSID 37 NOT NULL DEFAULT '' )


  RCDFMT GRLR       ;


LABEL ON TABLE VENCUSTUG/GRLP_NEW

  IS 'Ver.000 Guest History Rate Lock File' ;


LABEL ON COLUMN VENCUSTUG/GRLP_NEW

( KYDTRL IS 'KEY                 DTE' ,

  SEQRL IS 'SEQ                 NUM' ,

  TRIDRL IS 'KEY                 SEQ' ,

  ACSQRL IS 'ACOM                SEQ' ,

  WINGRL IS 'WING' ,

  RMNOSIDRL IS 'ROOM' ,

  RTUSRL IS 'RTE                 TYP                 USE' ,

  RTEPRL IS 'RATE                PLAN' ,

  CONVRL IS 'CONV' ,

  PKGRL IS 'BILL                CODE' ,

  STRGRL IS 'STRG CODE' ,

  EFDTRL IS 'EFF                 DATE' ,

  DTLKRL IS 'LCK                 DTE' ,

  MPLNRL IS 'MEAL                PLAN' ,

  POPTRL IS 'PST                 OPT' ,

  BPRCRL IS 'AMOUNT' ,

  AMADRL IS 'ADD                 ROM                 AMT' ,

  CRADRL IS 'MEL                 CRD                 AMT' ,

  SCADRL IS 'SVC                 CHG                 AMT' ,

  DEPPRL IS 'CODEDP' ,

  CANPRL IS 'CODECP' ,

  USCRRL IS 'OPERATOR' ,

  USLMRL IS 'OPERATOR' ,

  RACORL IS 'RAT                 AFT                 C/O' ,

  GRPURL IS 'GRP                 P/U                 RAT' ,

  CLNMRL IS 'CALENDAR' ,

  CAL#RL IS 'CAL                 ID                  COD' ,

  IDRL IS 'RAT                 DSP                 ID' ,

  CMTRL IS 'COMMENTS' ) ;


LABEL ON COLUMN VENCUSTUG/GRLP_NEW

( KYDTRL TEXT IS 'KEY DATE FOR THIS RECORD' ,

  SEQRL TEXT IS 'SEQ NUM' ,

  TRIDRL TEXT IS 'TRIP UNIQUIFIER' ,

  ACSQRL TEXT IS 'ACCOMODATION SEQUENCE' ,

  WINGRL TEXT IS 'WING CODE' ,

  RMNOSIDRL TEXT IS 'ROOM NUMBER/SUITE ID' ,

  RTUSRL TEXT IS 'RATE TYPE USED' ,

  RTEPRL TEXT IS 'RATE PLAN' ,

  CONVRL TEXT IS 'CONV' ,

  PKGRL TEXT IS 'PACKAGE' ,

  STRGRL TEXT IS 'STRATEGY CODE' ,

  EFDTRL TEXT IS 'EFFECTIVE DATE' ,

  DTLKRL TEXT IS 'DATE LOCKED' ,

  MPLNRL TEXT IS 'MEAL PLAN' ,

  POPTRL TEXT IS 'POSTING OPTION' ,

  BPRCRL TEXT IS 'ROOM RATE' ,

  AMADRL TEXT IS 'ADD TO ROOM AMOUNT' ,

  CRADRL TEXT IS 'MEAL CREDIT AMOUNT' ,

  SCADRL TEXT IS 'SVC CHARGE AMOUNT' ,

  DEPPRL TEXT IS 'DEP CODE' ,

  CANPRL TEXT IS 'CNL CODE' ,

  USCRRL TEXT IS 'BASIC DEFINITION OF USER NAME FIELD' ,

  USLMRL TEXT IS 'BASIC DEFINITION OF USER NAME FIELD' ,

  RACORL TEXT IS 'RATED AFTER CUTOFF' ,

  GRPURL TEXT IS 'GROUP PICKUP WHEN RATED' ,

  CLNMRL TEXT IS 'CALENDAR NAME DEFINITION' ,

  CAL#RL TEXT IS 'CALENDAR ID CODE' ,

  IDRL TEXT IS 'DISPLAY RATE ID' ,

  CMTRL TEXT IS 'COMMENTS' ) ;

Generated using http://www.projex.com command RTVDDLSRC