READ vs SQL SELECT, A Quick Performance Test - Part 2

We have seen the comparison between READ and SQL SELECT (with CURSOR & Fetch one record at a time) and SQL SELECT has run about 30% less time than READ (70% of READ). Click Here to see more details on this.

However, Fetching more number of Records into a Multi Occurrence Data Structure and looping the data through DS should run even faster. We would be using same Table created in Part - 1, Please see Part - 1 using the above link for these details.

We will see a sample program with FETCH multiple rows into DS and compare the results. 

Program with SELECT (PPSQL1):

Now, This program took just 2 seconds to read one million records, which is much faster than using READ (> 70% Faster) and SELECT with single record for every FETCH (> 60% Faster).

Please note that,  File Size and Data are same as used in the Part - 1.

 

READ vs SQL SELECT, A Quick Performance Test

READ vs SQL SELECT

With no doubt, SQL is the advanced and efficient way for operations (Read, Write, Update or Delete) on Tables (or Files) on IBM i. 

With the use of Embedded SQL programming (SQLRPGLE), it is efficient to use SELECT to read the data from a table compared to using READ. 

Here is the result from a quick performance test between READ and SELECT to read One million records from a table.

Below are few points to consider about the test before we see the results. 

• Created Physical File (using DDS) for the use in RPGLE with READ Operation. 
• Created Table from SQL Session for the use in SQLRPGLE with SQL SELECT Operation.
• Both the files has same number of fields, length and same number of records.
• Both Programs are called from a new Interactive Session immediately after login.

Let's see the code used to create File/Table and Program. 

Physical File (PPTESTFILE):

Table (PTESTTABL):

Inserted one million records (same data) to both the files. And, Table is occupying less size compared to Physical File. 

Size of Physical File:

Record Length - 40, Total Records - 1,000,000, Size of Physical File - 64,008,192 Bytes

Size of Table:

Record Length - 40, Total Records - 1,000,000, Size of Physical File - 41,951,232 Bytes

Below is the code used for both READ & SELECT. 

for READ (PPREAD):

for SELECT (PPSQL):

Now, coming to the Results, Program with SELECT operation has run in in 30% (approx) less time than with READ (70% of READ).

Program with READ took about 9 seconds to read one million records. 

Program with SELECT took about 6 seconds to read one million records. 

So, It is efficient both in terms of Processing time and Storage to use SQL in place of READ wherever possible. 

Again, Is there a better way of doing this with SQL? Yes, Instead of Fetching one record every time, We can Fetch multiple records into Data Structure and loop through Data Structure for accessing the data. 

This has run > 60% faster than SQL SELECT example listed above. Click Here to see more details on this test.

CHAIN vs SETLL/READE, What to use & When?

CHAIN vs SETLL/READE:

CHAIN & READE, These are the two Operations that are used to read the data from a database file using a specified Key data. 

SETLL Operation is used to point to the record provided in the Key data. 

How do we use? 

CHAIN:

CHAIN (Search Arguments/Key Fields) FILENAME ;

IF %FOUND(FILENAME) ; 

// Required Program Logic

ENDIF ;

Click Here to find more details about CHAIN.

SETLL/READE:

SETLL (Search Arguments/Key Fields) FILENAME ; 

READE (Search Arguments/Key Fields) FILENAME ;

DOW NOT %EOF(FILENAME) ;

// Required Program Logic

READE (Search Arguments/Key Fields) FILENAME ;

ENDDO ;

SETLL:

SETLL (Search Arguments/Key Fields) FILENAME ; 

IF %FOUND(FILENAME) ;

// Required Program Logic

ENDIF ; 

Click Here to find more details about SETLL/RPGLE.

Now, What to use When? 

• If the Data base file used has Unique key constraint and/or if only one/first matching record is required by the Program, CHAIN would be the best choice. 
• If the Data base file used has Unique key constraint and/or if the program only intend to check if matching record is present or not (and doesn't require the data), SETLL would be the best choice (with %FOUND).
• If the Data base file may have multiple records with specified Search Arguments/Key fields and all records are required to be read, READE would be the best choice in combination with SETLL.

All about READ in RPGLE & Why we use it with SETLL/SETGT?

READ:

READ is one of the most used Opcodes in RPGLE. As the name suggests main purpose of this Opcode is to read a record from Database file.

What are the different READ Opcodes?

To list, Below are the five Opcodes. 

1. READ - Read a Record
2. READC - Read Next Changed Record
3. READE - Read Equal Key Record
4. READP - Read Prior Record
5. READPE - Read Prior Equal Record

We will see more about each of these later in this article. Before that, We will see a bit about SETLL/SETGT. 

SETLL (Set Lower Limit):

SETLL accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Corresponding Record (or Next Record if exact match isn't found). 

SETGT (Set Greater Than): 

SETGT accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Next Record (Greater Than the Key value).

Syntax:

SETLL SEARCH-ARGUMENTS/KEYFIELDS FILENAME

SETGT SEARCH-ARGUMENTS/KEYFIELDS FILENAME

One of the below can be passed as Search Arguments.

• Key Fields
• Relative Record Number
• Key List (KLIST - defined with Key Fields) - Only in Fixed Format RPGLE
• %KDS (Key Fields defined in a Data Structure)

SETLL/SETGT are also used to identify if a record is present in the File or not. %FOUND() can be used to immediately after SETLL/SETGT. 

This would eliminate the need to execute READ operation if there is no record found in the File.

We will now see more about each of the READ* Operations before we go on to the dependency with SETLL/SETGT with READ.

READ (Read a Record):

READ Operation reads a record currently pointed to from a Database file. And, loads it into Data structure if Data Structure name is provided in the READ operation. 

We will see later in this article on how to point to a specific record using SETLL/SETGT.

Syntax:

READ(E N) FILE_NAME DATA_STRUCTURE_NAME

Data Structure Name and Extenders 'E', 'N' are Optional.

Extender 'E' is used to capture any errors. %ERROR can be used to identify if there are any errors during the READ.

Extender 'N' is used if the Record needs to be read with No lock (Read Only mode). This isn't allowed if the file is opened for Inout only.

READC (Read Next Changed Record):

READC Operation reads the next changed Record on a Subfile. This can only be used with WORKSTN files. 

Syntax:

READC(E) RECORD_FORMAT DATA_STRUCTURE_NAME

Unlike the READ operation, READC expects Record Format Name. Record Format specified with SFILE Keyword on the File Definition. 

Data Structure Name and Extender 'E' are Optional and are similar to READ. Please see the READ Section for more details. 

Extender 'N' isn't allowed with READC. 

READ doesn't require pointer to be setup by SETLL/SETGT. Click Here to see more about READC and how it is related to SFLNXTCHG.

READE (Read Equal Key Record):

READE operation Reads the Next record with Matching key record currently pointed to from a Database file. 

Syntax:

READE(N E) SEARCH_ARGUMENTS/KEYS FILE_NAME DATA_STRUCTURE_NAME

One of the below can be passed as Search Arguments.

• Key Fields
• Relative Record Number
• Key List (KLIST - defined with Key Fields) - Only in Fixed Format RPGLE
• %KDS (Key Fields defined in a Data Structure)

Data Structure Name and Extenders 'E', 'N' are Optional. See READ Section for more details on these.

READP (Read Prior Record): 

READP operation Reads the Prior record to the currently pointed record from a Database file. 

Syntax: 

READP(E N) FILE_NAME DATA_STRUCTURE_NAME

Data Structure Name and Extenders 'E', 'N' are Optional. See READ Section for more details on these.

READPE (Read Prior Equal Key Record):

READPE operation Reads the Prior record with Matching Key record currently pointed to from a Database file. 

Syntax:

READPE(N E) SEARCH_ARGUMENTS/KEYS FILE_NAME DATA_STRUCTURE_NAME

One of the below can be passed as Search Arguments.

• Key Fields
• Relative Record Number
• Key List (KLIST - defined with Key Fields) - Only in Fixed Format RPGLE
• %KDS (Key Fields defined in a Data Structure)

Data Structure Name and Extenders 'E', 'N' are Optional. See READ Section for more details on these.

Why is it necessary to use SETLL/SETGT with READ?

As we have seen in the previous sections, READ would read the record from where it is pointed to. Now, How is it pointed? We should be using SETLL/SETGT to position at a specific record. 

READ & READP would read the Next & Prior records respectively from where it is pointed to. These doesn't accept any Search Arguments or Key fields. These can be used with files which aren't defined for Keyed Access as well. 

We could set the pointer at the start of the file, end of the file, before a specific key record or after a specific key record. We will see how to read (and what combination to use) for each of these scenarios.

• Read the first record in the file. 

Position to the file using SETLL and use READ to read the first Record. Search Arguments needs to be passed as '*START' in case of not opened with Keyed Access and '*LOVAL' could be used with file opened with Keyed Access. If there are no records available to read, End Of File can be captured using %EOF(), this would return '1' for End Of File.

E.g.: 

SETLL *START FILE_NAME ; // would position to the Starting of the file

(OR)

SETLL *LOVAL FILE_NAME ; // would position to the Lowest key value.    Would be starting of the file.

READ FILE_NAME ;

DOW NOT %EOF(FILE_NAME) ; 

// Required Program Logic

READ FILE_NAME ;

ENDDO ; // This would read the file Until End Of File

• Read the last record in the file.

Position to the file using SETGT and use READP to read the first Record. Search Arguments needs to be passed as '*END' in case of not opened with Keyed Access and '*HIVAL' could be used with file opened with Keyed Access. If there are no records available to read, End Of File can be captured using %EOF(), this would return '1' for End Of File.

E.g.: 

SETGT *END FILE_NAME ; // would position to the Starting of the file

(OR)

SETGT *HIVAL FILE_NAME ; // would position to the Lowest key value.    Would be starting of the file.

READP FILE_NAME ;

DOW NOT %EOF(FILE_NAME) ; 

// Required Program Logic

READP FILE_NAME ;

ENDDO ; // This would read the file Until End Of File. File would read from End to Start. So End of File in this case would be after the first Record.

READE & READPE would Read the Next Equal Key & Prior Equal Key records respectively from where it is pointed to. Key Fields are mandatory for these Operations and can only be used with the filed defined with Keyed Access. 

We could set the pointer at the start of the file, end of the file, before a specific key record or after a specific key record. We will see how to read (and what combination to use) for each of these scenarios.

• Read the record(s) with Specific Key values in the file from Start to End. 

Position to the file using SETLL with Key Fields and use READE to read the first Record with Matching Key. If there are no records available to read matching with the Key fields defined, End Of File can be captured using %EOF(), this would return '1' for End Of File.

E.g.: 

SETLL (KFLD1 : KFLD2) FILE_NAME ; // would position before the KFLD1, KFLD2

READE (KFLD1 : KFLD2) FILE_NAME ;

DOW NOT %EOF(FILE_NAME) ; 

// Required Program Logic

READE (KFLD1 : KFLD2) FILE_NAME ;

ENDDO ; // This would read the file Until End Of File

• Read the record(s) with Specific Key values from End to Start.

Position to the file using SETGT and use READP to read the first Record. Search Arguments needs to be passed as '*END' in case of not opened with Keyed Access and '*HIVAL' could be used with file opened with Keyed Access. If there are no records available to read, End Of File can be captured using %EOF(), this would return '1' for End Of File.

E.g.: 

SETGT (KFLD1 : KFLD2) FILE_NAME ; // would position to the Starting of 

READPE (KFLD1 : KFLD2) FILE_NAME ;

DOW NOT %EOF(FILE_NAME) ; 

// Required Program Logic

READPE (KFLD1 : KFLD2) FILE_NAME ;

ENDDO ; // This would read the file Until End Of File. File would read from End to Start. So End of File in this case would be after the first Record.

E.g.: If there are below records in FILE1 under FLD1

ABC

DEF

GHI

JKL

MNO

PQR

SETLL *START FILE1 ; // This would position the cursor at the First Record of the File (i.e. ABC). READ would read 'ABC' and READP would return End Of File.

SETLL ('DEF') FILE1 ; // This would position the cursor at the Second Record (i.e. before DEF). READ would read Record 'DEF' and READP would read record 'ABC'

SETGT ('MNO') FILE1 ; // This would position the cursor at the end of Record 'MNO'. READP would read 'MNO' and READ would read PQR.

SETGT ('PQR') FILE1 ; // This would position the cursor at the end of Last Record (i.e., PQR). READP would read 'PQR' and READ would return End Of File.

SETGT *END FILE1 ; // This would position the cursor at the end of Last Record (i.e. PQR). READP would read 'PQR' and READ would return End Of File.

What we need to know about CHAIN (RPGLE) & How is it different from READ?

CHAIN:

READ & CHAIN, These are one of the most used (& useful) Opcodes by any RPG developer. These Opcodes are used to read a record from file.

So, What's the difference between CHAIN & READ? 

CHAIN operation retrieves a record based on the Key specified. It's more like Retrieving Random record from a Database file based on the Key fields. 

READ operation reads the record currently pointed to from a Database file. There are multiple Opcodes that start with READ and all are used to read a record but with slight difference. We will see more about different Opcodes and How they are different from each other (and CHAIN) in another article.

Few differences to note. 

• CHAIN requires Key fields to read a record where as READ would read the record currently pointed to (SETLL or SETGT are used to point a Record). 
• If there are multiple records with the same Key data, CHAIN would return the same record every time. READE can be used to read all the records with the specified Key data.
• No need of setting up pointer before reading a Record. CHAIN can directly fetch a record based on the Key fields. Unlike READE (or READPE) which would require setting up the pointer to a specific Record using SETLL/SETGT.
• CHAIN is better choice when the Key data is unique and if only one record is expected from the file for the specified Key data.

We will see more about CHAIN in this article. 

More about CHAIN

Syntax:

CHAIN (Search Arguments/Key Data) FILENAME ;

Result can be directly moved to Data Structure, For this Data Structure name needs to be specified after the FILENAME in the Syntax. 

CHAIN (Search Arguments/Key Data) FILENAME DATA_STRUCTURE_NAME ;

CHAIN can be used to read a record by using RRN (Relative Record Number), In this case, RRN needs to be passed in Search Arguments. This is Applicable for reading the data from Subfile Data format as well.

Key List (KLIST) can be defined and used as Search Arguments rather than specifying the individual fields on CHAIN in Fixed-format RPGLE. 

Alternatively, %KDS can be used to fetch the Key list from a Data Structure in Free Format RPGLE.

Extenders like 'N' and/or 'E' can be used along with CHAIN.

'N' - Extender N can be used to specify the Record is retrieved for Read Only and Not to lock the record for Update. This Extender cannot be used if the File is not Opened for UPDATE operation. 

'E' - Extender E can be used to capture any errors. Using %ERROR would catch the error avoiding any Program crash/failure. 

CHAIN(N E) (Search Arguments/Key Data) FILENAME ;

IF %ERROR() ;

// Error Handling

ENDIF ;

%FOUND() is used to verify if the Record (with Search Arguments) is found in the file or not. 

IF %FOUND() ;

// Program Logic 

ENDIF ; 

E.g.:

Let's consider, we have a file 'FILENAME1' with 3 Key fields.

- Retrieve the Record with specifying the Key fields directly on the CHAIN.

CHAIN (KEYFLD1 : KEYFLD2 : KEYFLD3) FILENAME1 ;

- It isn't necessary to use all the Key fields on CHAIN. 

CHAIN (KEYFLD1 : KEYFLD2) FILENAME1 ;

- Instead, Define a Key List and use the Key List directly on CHAIN using %KDS

Dcl-DS FileKeys LikeRec(FILEREC : *KEY) ; // Fields can be explicitly defined as well in the DS instead of using LikeRec

CHAIN %KDS(FileKeys) FILENAME1 ;

If we need to use partial key, then need to mention the number of Key fields to be considered. 

CHAIN %KDS(FileKeys : 2) FILENAME1 ;