RE: SQL question

James,

Interesting point. However, the setll would only work if the system
built an index to do the setll on (or already had one, which it didn't).
According to the joblog under debug (and the status messages) the query
simply pounded the entire physical. While I agree that a hash table for
3 values would be quicker than one for 465,000 the query would have ran
MUCH faster if the system had built an index and counted unique values
that way.

-Walden

PS. Only techo-dweebs like you and I would even think to increment the
bit values.

-----Original Message-----
From: James W. Kilgore [mailto:qappdsn@ibm.net]
Sent: Thursday, June 18, 1998 3:03 PM
To: MIDRANGE-L@midrange.com
Subject: Re: SQL question


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