Re: SQL vs. traditional I/O?

[Joel Cochran <jrc@xxxxxxxxxx>]

On Fri, 2004-07-23 at 14:16, midrange-l-request@xxxxxxxxxxxx wrote:
> message: 5
> date: Fri, 23 Jul 2004 08:19:08 -0500
> from: Mike Wills <koldark@xxxxxxxxx>
> subject: Re: SQL vs. traditional I/O?
> 
> May I ask why you created a cursor? That alone takes quite a bit of
> time. If you just did:
> > c/END-EXEC
> > c/EXEC SQL select * from j4pp1 where key1 = :mykey into :j4pp1
> > c/END-EXEC
> I believe you might get better performance.

Also, if you did want/need to use a cursor, you certainly don't need to
declare it on every iteration of the loop.  Using the SQL statement
above will work, but requires the statement to be created, implemented,
and destroyed every time.  Instead, try creating the cursor based on a
prepared statement:

 /free
     sql = 'select * from j4ppl where key1 = ?' ;
 /end-free
c/exec SQL
c+ declare myCursor Cursor
c+   for Selection
c/end-exec
c/exec sql
c+ prepare Selection
c+    from :sql
c/end-exec
 /free
     for x=1 to count ;
        myKey = %char(x);
 /end-free
c/exec sql
c+    open myCursor
c+     using :myKey
c/end-exec
c/exec sql
c+    fetch myCursor
c+      into :j4ppl
c/end-exec
c/exec sql
c+    close myCursor
c/end-exec
 /free
     endfor ;
 /end-free

By using the prepared statement this way, you only have the overhead of
creating the statement and cursor one time instead of every time through
the loop.  This should speed up performance.  Also, you should look at
the SQL Optimizer (either in debug or in OpsNav) and see if it is making
any path recommendations.  If the optimizer recommends a specific Index,
then having that index in place will almost surely increase performance.

On another note - and this should be right up Joe's alley - is that
while we are trying to compare single record access, perhaps a
tangential question should be one of architecture.  Maybe we could
expand this little test to a real business case: perhaps we tend to use
single record access because historically that IS our hammer!  I think
the harder, and ultimately more rewarding test, is to see if in fact we
need a nail gun!  I think we need to revisit the way we design programs,
if nothing else it would prove an interesting excercise.

Joel
http://www.rpgnext.com