Re: SELECT DISTINCT(columname) and TEMP dbs

}>From ilist@rmy.emory.edu Thu May 15 18:35:14 1997 }>Received: from rmy.rmy.emory.edu by hobbes.dbintellect.com via ESMTP (950413.SGI.8.6.12/930416.SGI.AUTO) }> for <settler@dbintellect.com> id SAA27188; Thu, 15 May 1997 18:34:15 -0600 }>Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id RAA04999 for informix-list-out; Thu, 15 May 1997 17:20:01 -0400 (EDT) }>From: David Henseler <henslerd@bscc.bls.com> }>Message-Id: <337B6EF6.6591@bscc.bls.com> }>Subject: Re: SELECT DISTINCT(columname) and TEMP dbs }>Date: Thu, 15 May 1997 16:15:50 -0400 }>Reply-To: David Henseler <henslerd@bscc.bls.com> }>Organization: BellSouth BSCC lab }>Sender: informix-list-owner@rmy.emory.edu }>To: informix-list@rmy.emory.edu }>X-Informix-List-Id: <news.37958> }> }>Jeff Craig wrote: }> }> I am trying to execute a simple query such as: }> SELECT DISTINCT(columnname) }> FROM ANYTABLE }> ; }> }> There are approximately 40 million rows in ANYTABLE. The query runs }> forever, and the only activity I can see is major temporary dbspace }> reads and writes. I have tried forcing a light scan, but still hangs }> in the temporary dbspace thrash step. }> }> There are twenty temporary dbspaces on separate devices and }> controllers, raw. The system has sixteen CPUs, several gigabytes of }> memory. We are running INFORMIX-OnLine 7.20.UC2 on IRIX 6.2. ANYTABLE }> is on a single device. It is a two-column (rowsize of eight bytes) }> table. Statistics are fresh. UNIX sar command shows moderate activity }> on the temporary chunks, but no "pegged" disks. }> }> Anyone have any ideas on where and why this query is hanging??? }> }> }>Jeff: }> I think you are just maxing out the system but you could try these }>solutions. }> }> I don't know if you have the luxury of rebuilding the table but it }>seems to me that you have plenty of disks to break the i/o up. Try to }>fragment the table by round robin or expression and then set PDQPRIORITY }>= 1 to allow parallel disk i/o. }> }> This is another possible solution although not always practical, }>unload the table to a UFS and run a unix sort with a unique parameter. }> }>Good Luck. }> }>David Henseler }> Thank you for the suggestions. In most instances, we did fragment tables by round-robin. This table is only two columns, however, so total size is less than 400 megabytes. I guess I thought that since I can scan it relatively quickly with a UNIX process that Informix could do that as well. At this point, it is orders of magnitude quicker to unload it to UNIX file and use uniq or sort to get distinct values than to use Informix - WHY? ------------------------------------------------------------------- Jeff Craig | dbINTELLECT Technologies, Inc. Senior Consultant | Golden, CO | (303) 275-6954 ext. 8008 | email: settler@dbintellect.com | fax: (303) 275-2134 -------------------------------------------------------------------