RE: SQL scroll cursor slow performance

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

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.

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?

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

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

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

5. Which would be easier to maintain?

(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

> From: darren@xxxxxxxxx
> 
> I'm using an SQL scroll cursor with the code shown below.  I call the
> $ResetCurs1 subroutine and read through the entire cursor 4 times
because
> I generate the report 4 times with 4 different headings.