Re: Performance hit with large number of rows and compound index

->From: cp@dtt.co.nz (Chris Palmer) ->Subject: Performance hit with large number of rows and compound index ->Date: Thu, 11 Aug 1994 10:56:23 UNDEFINED ->Reply-To: cp@dtt.co.nz (Chris Palmer) ->Organization: Deloitte Touche Tohmatsu, Auckland, New Zealand -> ->A client of ours has standard Informix 5.02 installed as part of a turn-key ->system from a third party, and is experiencing a bad performance hit ->on a table with a compound index, which has grown to over 30,000 rows. -> ->They have heard a rumour that this is a known problem. Can anyone shed any ->light? ->--------------------------------------------------------- ->Chris Palmer ->Deloitte Touche Tohmatsu, Auckland, New Zealand ->c.palmer@dtt.co.nz 30,000 is not really very many rows; your client should not be having problems with such a table size unless the rows themselves are huge, or the machine itself is small and weak. However, the following should help: 1. Perform "UPDATE STATISTICS ON tablename". This is the easiest, and it may help performance. 2. DROP INDEX idxname; CREATE INDEX idxname ON tablename ( cola, colb, ... ); Dropping and recreating the index will improve index contiguity, subject to operating system / file system limitations. Medium cost to do; must be DBA or have index privilege on "tablename". 3. ALTER INDEX idxname TO CLUSTER; This will recreate the table and order the rows by index value. It forces logical contiguity, and improves physical contiguity, again subject to OS / FS limitations. This has the highest cost to do, but should provide the most benefit. However, you can only have one cluster index per table, and reordering the table rows may impact some OTHER query using some other index. You must have DBA privilege or alter table privilege on "tablename" to do this. "Known problem"? Not to me, but that's certainly not definitive. :-) Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\