RE: SQL question

Could it be that the count is done sequentially on the result or the
pointers to the result?



Barry Grau (grau@uic.edu)
Information Technology Services
UIC Medical Center
Chicago, Il 60612


-bwg


> ----------
> From:         Walden Leverich[SMTP:walden@techsoftinc.com]
> Reply To:     MIDRANGE-L@midrange.com
> Sent:         Thursday, June 18, 1998 12:42 PM
> To:   MIDRANGE-L@midrange.com
> Subject:      RE: SQL question
> 
> 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. The only thing I can think
> of is that the count is being performed in such a way that it is
> optimized for a very low count value and takes FOREVER for a large
> number of distinct values.
> 
> 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.
> 
> -Walden
> 
> -----Original Message-----
> From: Ravi [mailto:ravi@spacestar.com]
> Sent: Wednesday, June 17, 1998 5:31 PM
> To: MIDRANGE-L@midrange.com
> Subject: SQL question
> 
> 
> Howdy folks,
> 
> I have a file with about 1/2 million records on our development
> machine (V4R2).
> 
> I ran a SQL query from ISQL:
> -  Select distinct FIELD from FILE.
> The query ran in about 2 minutes and gave me the result set with 
> about 200,000 records. FIELD is not a key field.
> 
> I ran another query from ISQL over the same file/field:
> - Select count(distinct FIELD) from FILE.
> This query ran for over 10 minutes and returned the count.
> Why would this query take so much longer than the first query? I ran the
> test a few times when there was hardly any activity on the system and 
> got the same results. The 2nd query takes atleast 5 times as long 
> to complete. 
> We have a similar table(few hundred thousand rcds)on an RS6000/Oracle
> database. I ran similar queries over Oracle and there was no noticeable
> difference in run times. I chose a non-indexed field.
> What is so different about DB2/400 that would cause this? Just
> curious...
> 
> Thanks
> 
> Ravi
> +---
> | 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
+---