RE: SQL performance (was Evaluate complex conditions)

[Buck Calabro <buck.calabro@xxxxxxxxxxxxxxxxx>]

Booth Martin wrote:

>You suggest that an SQL process is not 
>a performance pig compared to 
>properly written RPGIV program.   Is there 
>any experience out there to  support or confute this?

Paul Conte did some benchmarking of SQL vs "native" RPG I/O in News/400 a
while ago.  I don't remember the precise results but I remember SQL having a
slight edge for "set at a time" work, whilst RPG had an edge for "single
record" work.

Personally, I haven't used SQL for multi-million record batch jobs, simply
because the existing native I/O works well enough performance wise.  Where I
have been using SQL is in the interactive environment, where you can do
things that will open your eyes.  In this case, performance comes second to
function.  What matters is that the program can now solve another business
problem with little programmer expense.

>I ask because we notice that whenever one 
>of the SQL gurus starts an SQL process 
>off the command line all the rest of us might 
>as well go for coffee until the SQL is done. 

The SQL guru should learn the difference between "batch" and "interactive!"
<grin>  I don't compile programs interactively, run interactive Query, SQL
or CPYF.  In fact, I make every effort to follow The Golden Rule: If it
doesn't interact with the user, submit it.

That aside, SQL is a general-use tool.  Internally, the query optimiser
makes some decisions about how to go about finding the records you ask for:
should it build a logical or read/compare every record?  Sort the records
first, then SETLL/READE or find a logical file in that order already?  You
can see it's decision making process at work by doing a STRDBG, running your
SQL/Query and looking at the messages in the joblog.

You can think of SQL as an average programmer writing a one-time program to
manipulate the data.  Sometimes, it makes decisions that an experienced
programmer wouldn't; especially a programmer familiar with your data.  If
SQL can locate an existing logical, it will try to use it.  This means that
creating logical files is the number one means at your disposal to influence
SQL to be a good performer.

Buck Calabro
Aptis; Albany, NY
"Nothing is so firmly believed as
 that which we least know" -- Michel Montaigne
Visit the Midrange archives at http://www.midrange.com


Billing Concepts Corp., a NASDAQ Listed Company, Symbol: BILL
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---