RE: SQL scroll cursor slow performance

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Joe Pluta
> Sent: Thursday, June 23, 2005 12:38 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL scroll cursor slow performance
> 
> 
> This is a syndrome.  I'm going to name it.  I'm going to call it
> "creeping SQL-ism".  I think this could be done with a simple 
> read loop
> and about seven CHAINs.  The wide use of IFNULL and MIN in situations
> where there should only be one record or none makes this statement
> awfully complex.  And I always start to wonder when I see the CASE
> keyword.  I have to admit that I'm a bit confused as to how the SQL
> engine figures out the CASE clause in this statement where different
> fields are being MIN'd based on the value of a different field's MIN
> value.

I'm going to have to agree with Joe here.  The use of min() when you're looking 
for a minimum value is fine.  The use of min() to work around the GROUP BY 
clause is messy.

Some Common Table Expressions or UDF functions (scalar and/or table) may be of 
use here.


> 
> Poll questions (anybody who feels like it, please answer):
> 
> 1. Do you think this would require more or less complexity to write
> using native I/O statements?

In general, about the same.  This particular SQL seems to be messier than it 
needs to be.

> 
> 2. What reasons would you give for choosing SQL or native 
> (remember, for
> performance purposes all the indexes have to exist, so that's not an
> reason to choose SQL)?

Performance could still be an issue.  But since he's fetching one row at a 
time, the OP must not be too concerned about it.  Then again, is his asking 
about poor performance here.

In general, if I'm dealing with obvious set at time requirements, I'll chose 
SQL.

In particular, I like to encode "business queries" in a UDF table function for 
easier reuse and/or outputting in alternative formats.

> 
> 3. Do you think it would perform better in native or SQL?

When done correctly?  SQL of course.

> 
> 4. Which do you think would take longer to debug?

I find SQL easier; particularly when you use CTE and UDFs to break stuff down.  
It's easy to check the results are what you expect.  You don't need to step 
thru one line at a time.

However, with an SQL statement this big SQL would be harder.

> 
> 5. Which would be easier to maintain?

see above. the same applies

> 
> (A side issue is to ask why you are running through the exact same
> cursor four times; where are the report lines going?  One of the first
> things I'd be looking at is how to run through the data once, 
> but that's
> just my old dinosaur thinking.)
> 
> Joe
> 

Agreed.  If the only differences are the headers, open 4 spool files and write 
them all at the same time for crying out loud.

Even with other differences, there's no reason to read the same data 4 time, 
just create separate procedures to process that data into 4 spool files at the 
same time.


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121