fhg
Volume 9, Number 17 -- May 20, 2009

Faster Fetching

Published: May 20, 2009

Hey, Ted:

When using embedded SQL to read data, I have the option of retrieving one row or multiple rows with one fetch. Does fetching more than one row at a time improve program performance?

--Neil


I'd like to think it does, Neil. It seems to me it should. Here are the comments of John, a reader who claims that a multiple-row fetch is appreciably faster.

I have used fetch with multiple-occurrence data structures in order to read multiple records at once and minimize the use of FETCH in the program. The execution of a FETCH is quite resource intensive and slow. I have found 50 to 100 rows quite effective. Trust me. It makes a massive difference in execution time.

In programming terms I wrote a read subroutine that managed the data structure occurrence and executed FETCH only when I had run out of occurrences. It hid the mechanics of end of file and data handling from the main routines.

I ran a little test, just to see what I might discover. First, here's a simple single-fetch program.

H option(*srcstmt:*nodebugio) 

Fqsysprt   o    f  132        printer 

D Transaction     ds                  qualified inz 
D   Batch                        3p 0 
D   Code                         2a 
D   Order                        7a 
D   Item                        15a 
D   Warehouse                    3a 
D   Quantity                    11p 3 

D SqlEof          c                   const('02000') 
D DetailLine      s            132a 

D Print           pr 
D   Data                       132a   value 

D/copy prototypes,assert 

 /free 
     *inlr = *on; 
     exec sql 
        declare Input cursor for 
           select batch, tcode, ordno, itnbr, house, trqty 
             from imhist 
            order by prqoh desc; 

     exec sql 
        open Input; 
        assert (sqlstt=*zeros: 'Open error'); 

     dow '1'; 
        exec sql 
           fetch next from Input into :Transaction; 
        assert (sqlstt <= SqlEof: 'Fetch error'); 
        if sqlstt >= SqlEof; 
           leave; 
        endif; 
        Print (%editc(Transaction.Batch:'3') + ' ' + 
               Transaction.Code + ' ' + 
               Transaction.Order + ' ' + 
               Transaction.Item + ' ' + 
               Transaction.Warehouse + ' ' + 
               %editc(Transaction.Quantity:'J')); 
     enddo; 
     return; 
 /end-free 

Oqsysprt   e            PrintLine   1 
O                       DetailLine 
 * ========================================================== 
P Print           b 
D                 pi 
D   Data                       132a   value 
 /free 
     DetailLine = Data; 
     except PrintLine; 
 /end-free 
P                 e 

Here's the same program, with a multiple-row fetch into an array data structure.

H option(*srcstmt:*nodebugio) 

Fqsysprt   o    f  132        printer 

D ArraySize       s             10i 0 inz(100)
D Transaction     ds                  qualified inz dim(100)
D   Batch                        3p 0 
D   Code                         2a 
D   Order                        7a 
D   Item                        15a 
D   Warehouse                    3a 
D   Quantity                    11p 3 

D Ndx             s             10i 0 
D SqlEof          c                   const('02000')
D DetailLine      s            132a 

D Print           pr 
D   Data                       132a   value 

D/copy prototypes,assert 

 /free 
     *inlr = *on; 
     assert (%elem(Transaction) = ArraySize: 
                   'Invalid data structure definition');

     exec sql 
        declare Input cursor for 
           select batch, tcode, ordno, itnbr, house, trqty
             from imhist
            order by prqoh desc;

     exec sql 
        open Input;
        assert (sqlstt=*zeros: 'Open error'); 

     dou sqlstt >= SqlEof; 
        exec sql 
           fetch next from Input 
              for :ArraySize rows 
              into :Transaction; 
        assert (sqlstt <= SqlEof: 'Fetch error');
        for Ndx = 1 to SqlEr3; 
           Print (%editc(Transaction(Ndx).Batch:'3') + ' ' +
                  Transaction(Ndx).Code + ' ' + 
                  Transaction(Ndx).Order + ' ' + 
                  Transaction(Ndx).Item + ' ' + 
                  Transaction(Ndx).Warehouse + ' ' +
                  %editc(Transaction(Ndx).Quantity:'J'));
        endfor; 
     enddo; 
     return; 
 /end-free 

Oqsysprt   e            PrintLine   1 
O                       DetailLine 
 * ==========================================================
P Print           b 
D                 pi 
D   Data                       132a   value
 /free 
     DetailLine = Data; 
     except PrintLine; 
 /end-free 
P                 e 

Last, I tested a multiple-row fetch into a multiple-occurrence data structure.

H option(*srcstmt:*nodebugio) 

Fqsysprt   o    f  132        printer 

D ArraySize       s             10i 0 inz(100) 
D Transaction     ds                  qualified inz occurs(100)
D   Batch                        3p 0 
D   Code                         2a 
D   Order                        7a 
D   Item                        15a 
D   Warehouse                    3a 
D   Quantity                    11p 3 

D Ndx             s             10i 0 
D SqlEof          c                   const('02000') 
D DetailLine      s            132a 

D Print           pr 
D   Data                       132a   value 

D/copy prototypes,assert 

 /free 
     *inlr = *on; 
     assert (%elem(Transaction) = ArraySize: 
                   'Invalid data structure definition');

     exec sql 
        declare Input cursor for 
           select batch, tcode, ordno, itnbr, house, trqty
             from imhist 
            order by prqoh desc; 

     exec sql 
        open Input; 
        assert (sqlstt=*zeros: 'Open error'); 

     dou sqlstt >= SqlEof; 
        exec sql 
           fetch next from Input 
              for :ArraySize rows 
              into :Transaction; 
        assert (sqlstt <= SqlEof: 'Fetch error'); 
        for Ndx = 1 to SqlEr3; 
           %occur(Transaction) = Ndx; 
           Print (%editc(Transaction.Batch:'3') + ' ' +
                  Transaction.Code + ' ' + 
                  Transaction.Order + ' ' + 
                  Transaction.Item + ' ' + 
                  Transaction.Warehouse + ' ' + 
                  %editc(Transaction.Quantity:'J')); 
        endfor; 
     enddo; 
     return; 
 /end-free 

Oqsysprt   e            PrintLine   1 
O                       DetailLine 
 * ==========================================================
P Print           b 
D                 pi 
D   Data                       132a   value 
 /free 
     DetailLine = Data; 
     except PrintLine; 
 /end-free 
P                 e 

The file with which I tested had 1.3 million records. The first two programs used 11 seconds of CPU time. However, the last one used only seven seconds of CPU time. Using an array data structure was no faster than the single-row fetch. The multiple-occurrence data structure was faster than the array data structure.

--Ted


RELATED STORY

SQL's Other Fetch Options



Copyright © 1996-2009 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement