RE: SQL scroll cursor slow performance

That's just the nature of SQL.  When you group based on one or more fields,
there will be other fields that aren't part of the grouping and you have to
instruct the query what to do about it.  In other words, anytime you use
GROUP BY, any fields not listed in this list have to have some kind of
modifier in front of them, like min(), max(), or sum().  I'm having trouble
seeing how common table expressions or a UDF could get around that rule.




                                                                           
             "Wilt, Charles"                                               
             <CWilt@xxxxxxxxxx                                             
             om>                                                        To 
             Sent by:                  "Midrange Systems Technical         
             midrange-l-bounce         Discussion"                         
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>           
                                                                        cc 
                                                                           
             06/23/2005 12:17                                      Subject 
             PM                        RE: SQL scroll cursor slow          
                                       performance                         
                                                                           
             Please respond to                                             
             Midrange Systems                                              
                 Technical                                                 
                Discussion                                                 
             <midrange-l@midra                                             
                 nge.com>                                                  
                                                                           
                                                                           




> -----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


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.