Re[2]: SQL question

James,
Why even bother with incrementing bit values when the opcode you need is already
available to you. SETGT positions to the next record after the current limits 
record set. Plus, bitwise operations could confuse novice programmers. 

Question.... is there any way to know the relative record number of access path.
That is: a file keyed on alpha field. Setll *loval points us to the beginning of
the access path (which could be considered RRN 1 for the access path). I know 
that once accessed, the file inf data structure returns RRN from the PF not the 
LF. 
______________________________ Reply Separator _________________________________
Subject: RE: SQL question 
Author:  <MIDRANGE-L@midrange.com > at INTERNET
Date:    6/19/98 8:00 AM


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


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