IBM i SQL vs DDS – Is SQL faster than CPYF Native IO?

AS400

Mar 18
as400 ddl vs dds

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 the big question remains “IBM i SQL vs DDS – who wins?”

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:

dds ddl which is fastest - lets fightUsing 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 – decreasing runtime from 19 to 10 minutes.

No Doubt about that. Or is there?

Result – SQL is Faster but it’s Hungrier on the CPU.

Sure it ran twice as fast… but it used twice as much CPU! If it eats twice the processing power then I would expect it to run at least twice as fast. Which it did. So, its hard to throw my hat in the air and yell YIPPEEEEE!

Having said that,  I feel that SQL gives us an immediate performance improvement, in real physical watch-the-clock runtime, which is a big deal if you are waiting for an install or upgrade to finish. We also know that IBM are focusing on SQL #bigtime and we can expect more improvements as time goes by.

I vote SQL and DDL but …

I guess you can make your own mind up.

 

DDS & SQL Source Code

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:

 ** ------------------------------------------------------------- *
 ** file name................ GRLP_SV 
 ** file name................ GRLP_SV 
 ** retrieved from library... VENCUSTUG 
 ** retrieved by user........ VDLITTENN on 14/03/12 
 ** type of file (pf or lf)..**PHY 
 ** ------------------------------------------------------------- *
 REF(LMSRC15/FLDREFP)
 R GRLR
 TEXT('rate lock record ')
 ** ------------------------------------------------------------- *
 KYDTRL R REFFLD(KYDT FLDREFP)
 TEXT('key date for this record')
 COLHDG('KEY' +
 'DTE')
 SEQRL R REFFLD(SEQ FLDREFP)
 TEXT('seq num')
 COLHDG('SEQ' +
 'NUM')
 TRIDRL R REFFLD( FLDREFP)
 TEXT('trip uniquifier')
 COLHDG('KEY' +
 'SEQ')
 ACSQRL R REFFLD(SEQ FLDREFP)
 TEXT('accomodation sequence')
 COLHDG('ACOM' +
 'SEQ')
 WINGRL R REFFLD(WING FLDREFP)
 TEXT('wing code')
 COLHDG('WING')
 RMNOSIDRL R REFFLD(RMNO FLDREFP)
 TEXT('room number/suite id')
 COLHDG('ROOM')
 RTUSRL R REFFLD(RTUS FLDREFP)
 TEXT('rate type used')
 COLHDG('RTE' +
 'TYP' +
 'USE')
 RTEPRL R REFFLD(RTEP FLDREFP)
 TEXT('rate plan')
 COLHDG('RATE' +
 'PLAN')
 CONVRL R REFFLD(CONV FLDREFP)
 TEXT('conv')
 COLHDG('CONV')
 PKGRL R REFFLD( FLDREFP)
 TEXT('package')
 COLHDG('BILL' +
 'CODE')
 STRGRL R REFFLD(STRGRT FLDREFP)
 TEXT('strategy code')
 COLHDG('STRG CODE')
 EFDTRL R REFFLD(EFDT FLDREFP)
 TEXT('effective date')
 COLHDG('EFF' +
 'DATE')
 EDTCDE(3)
 DTLKRL R REFFLD( FLDREFP)
 TEXT('date locked')
 COLHDG('LCK' +
 'DTE')
 MPLNRL R REFFLD(MPLN FLDREFP)
 TEXT('meal plan')
 COLHDG('MEAL' +
 'PLAN')
 POPTRL R REFFLD(POPT FLDREFP)
 TEXT('posting option')
 COLHDG('PST' +
 'OPT')
 BPRCRL R REFFLD( FLDREFP)
 TEXT('room rate')
 COLHDG('AMOUNT')
 EDTCDE(J)
 AMADRL R REFFLD( FLDREFP)
 TEXT('add to room amount')
 COLHDG('ADD' +
 'ROM' +
 'AMT')
 EDTCDE(J)
 CRADRL R REFFLD( FLDREFP)
 TEXT('meal credit amount')
 COLHDG('MEL' +
 'CRD' +
 'AMT')
 EDTCDE(J)
 SCADRL R REFFLD( FLDREFP)
 TEXT('svc charge amount')
 COLHDG('SVC' +
 'CHG' +
 'AMT')
 EDTCDE(J)
 DEPPRL R REFFLD(CODEDP FLDREFP)
 TEXT('dep code')
 COLHDG('CODEDP')
 CANPRL R REFFLD(CODECP FLDREFP)
 TEXT('cnl code')
 COLHDG('CODECP')
 USCRRL R REFFLD( FLDREFP)
 TEXT('basic definition of user nam')
 COLHDG('OPERATOR')
 USLMRL R REFFLD( FLDREFP)
 TEXT('basic definition of user nam')
 COLHDG('OPERATOR')
 RACORL R REFFLD(RACO FLDREFP)
 TEXT('rated after cutoff')
 COLHDG('RAT' +
 'AFT' +
 'C/O')
 GRPURL R REFFLD(GRPU FLDREFP)
 TEXT('group pickup when rated')
 COLHDG('GRP' +
 'P/U' +
 'RAT')
 EDTCDE(Z)
 CLNMRL R REFFLD(CALENDAR FLDREFP)
 TEXT('calendar name definition')
 COLHDG('CALENDAR')
 CAL#RL R REFFLD(CAL# FLDREFP)
 TEXT('calendar id code')
 COLHDG('CAL' +
 'ID' +
 'COD')
 IDRL R REFFLD(RATID FLDREFP)
 TEXT('display rate id')
 COLHDG('RAT' +
 'DSP' +
 'ID')
 CMTRL R REFFLD(CMNT FLDREFP)
 TEXT('comments')
 COLHDG('COMMENTS')

If we convert this DDS into SQL format (DDL) then it 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 command RTVDDLSRC

Follow

About the Author

IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.