• 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
    Raz-Lee Security

    Raz-Lee Security is the leader in security and compliance solutions that guard business-critical information on IBM i servers. We are committed to providing the best and most comprehensive solutions for compliance, auditing, and protection from threats and ransomware. We have developed cutting-edge solutions that have revolutionized analysis and fortification of IBM i servers.

    Raz-Lee’s flagship iSecurity suite of products is comprised of solutions that help your company safeguard and monitor valuable information assets against intrusions. Our state-of-the-art products protect your files and databases from both theft and extortion attacks. Our technology provides visibility into how users access data and applications, and uses sophisticated user tracking and classification to detect and block cyberattacks, unauthorized users and malicious insiders.

    With over 35 years of exclusive IBM i security focus, Raz-Lee has achieved outstanding development capabilities and expertise. We work hard to help your company achieve the highest security and regulatory compliance.

    Key Products:

    • AUDIT
    • FIREWALL
    • ANTIVIRUS
    • ANTI-RANSOMWARE
    • MULTI-FACTOR AUTHENTICATION
    • AP-JOURNAL
    • DB-GATE
    • FILESCOPE
    • COMPLIANCE MANAGER
    • FIELD ENCRYPTION

    Learn about iSecurity Products at https://www.razlee.com/isecurity-products/

    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

  • The IBM i Power10 Upgrade Cycle Forecast Looks Favorable
  • White Hats Completely Dismantle Menu-Based Security
  • Cloud Software To Drive Enterprise Application Growth
  • How Do You Stay In Touch With The IBM i Community?
  • IBM i PTF Guide, Volume 25, Number 6
  • Security Still Top Concern, IBM i Marketplace Study Says
  • Bob Langieri Shares IBM i Career Trends Outlook for 2023
  • Kisco Brings Native SMS Messaging to IBM i
  • Four Hundred Monitor, February 1
  • 2023 IBM i Predictions, Part 4

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.