• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Faster Fetching

    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



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Racksquared

    It’s time for IBM Power in the Cloud!

    Stop buying hardware and make the move to the cloud. It’s easier and more cost effective than you might think.

    • IBM Power in the Cloud
    • IBM Power Backup Solutions
    • IBM Power High Availability and DR solutions
    • IBM Power Colocation with Management and Monitoring

    Let’s talk about your business needs.

    Call: 855-380-7225

    Email: Sales@racksquared.com

    www.racksquared.com/ibmsolutions

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    ProData Computer Services:  Simplify your iT with DBU, DBU RDB, and RDB Connect
    Halcyon Software:  Webinar: How to Survive in IT with a reduced headcount, June 4
    Aberdeen Group:  Take the 2009 ERP in Manufacturing survey, get a free copy of complete report

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    IBM Adds iPhone Support to Lotus Notes Traveler Halcyon Revs Up U.S. Channel Effort

    2 thoughts on “Faster Fetching”

    • Warren Kinley says:
      November 15, 2017 at 6:36 am

      Hi thanks for this, but I’m not sure SqlEr3 is the correct variable to use for number of rows returned from the block fetch. On a successful fetch this value is always zero. Should we not be using: “exec sql GET DIAGNOSTICS :Rows = ROW_COUNT” ?

      Reply
    • duane m scott says:
      November 16, 2017 at 12:11 pm

      Actually, in my testing, I couldn’t figure out how it was duping some of the data until I changed it to SqlEr3.

      Reply

    Leave a Reply Cancel reply

Volume 9, Number 17 -- May 20, 2009
THIS ISSUE SPONSORED BY:

Halcyon Software
Help/Systems
Twin Data

Table of Contents

  • Faster Fetching
  • A Bevy of BIFs: %Dec to the Rescue
  • Admin Alert: Four Ways to Encrypt i5/OS Backups, Part 2

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050
  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.