Re: SQL question

[Dave Shaw <dshaw1@xxxxxxxxxxx>]

-----Original Message-----
From: Ravi <ravi@spacestar.com>
To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
Date: Wednesday, June 17, 1998 6:33 PM
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...


The first query didn't return all 200,000 records to you, just the first 20
or so that showed on your screen.  The second query actually does have to
read (or at least build an index for) all 200,000 records in order to count
them.  ISQL always tells the query optimizer that it's only looking for a
screenful of records at a time, and the optimizer adjusts the query to
efficiently provide just that.  When you do a Page Down, it gets another
screenful.  If you page through all of the selected records, you'll actually
use more machine resources than you would if it did return all 200,000 up
front, but ISQL is betting that you won't actually do that, or if you do
that it will take you long enough that the demand for resource will be
spread over a much longer time so that the query will have less impact on
system response to other jobs.

Dave Shaw, General Nutrition, Greenville, SC (just down the road from BMW -
Bubba Makes Wheels :)
The opinions expressed may not be my employer's unless I'm sufficiently
persuasive...


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