Re: SQL question

["James W. Kilgore" <qappdsn@xxxxxxx>]

Walden,

This may not be a bug.  Think about it, to count the number of unique values in 
a
field that only has three values as opposed to 465,000 would definatly return a
faster answer.

To do the same thing in RPG you would start with a blank field and do a
SETLL/READ the returned value is count one.  You increment the bit value by one
and do another SETLL/READ, that's two, do it again that's three, and one more
time is end of file.

Now do that loop 465,000 times and it would make sense that it would take 
longer.

Walden Leverich wrote:

> Strangely enough, I had the same problem this morning on V4R1 over
> 465,000 records. I actually killed the SQL after 15 minutes trying to
> count(distinct field) from file. After reading your e-mail I tried the
> select without the count and it ran almost instantly.
>
> Now, the original count would have resulted in close to 465,000 records
> (a nearly unique field), I ran another count distinct that resulted in
> only 3 records and it ran almost instantly.

<<snip>>

> Try your SQL again counting distinct values for a very non-unique field
> and post the results. If we see the same result I think we can report
> this as a database bug.
>



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