IDS Forum

Re: Sequential Scan vs Index Read

Posted By:
Date:

ISpy will help capture the various queries that access the table of concern,
then you can try each under SET EXPLAIN ON and see. BTW Have you/do you run the
recommended suite of update statistics commands as described in the Performance
Guide? If not, that would be the first thing to do the improve things, at
least for those queries that CAN use an existing index. Get my dostats utility
which implements the recommended suite automatically with many options to
improve performance and/or reduce the work it has to do. Dostats is part of the
package utils2_ak available for download from the IIUG Software Repository.

Art S. Kagel

----- Original Message -----
From: Roy Verstegen <VERROY@wsinc.com>
At: 2/12 16:38

> The facts.
> OS = AIX 4.3.3
> Machine = RS 6000 F80 (64 bit)
> IDS = 7.31.FD1
>
> We have a table that is being sequentially scanned over 100 times an hour
> during our peak hours. This table has flucuated between 20,000 and 40,000
> rows over the course of this month. This table already has multiple indices
> (5). The sequential scans must be slowing down the users, but the users are
> not complaining, because I think it has always been this way for them . The
> issue is that we have over 250 seperate 4GL programs that query this table
> somewhere in the code. The code is from a third party supplier that is not
> moving very fast to help us solve this issue.
>
> Are there any Informix tools available to us to see which queries are
> reading the table through an index, and which ones are sequentially scanning
> the table? To run set explain for all 250+ 4GL programs would be too time
> consuming and cumbersome.
>
> Any help would be appreciated, thank you.
>
> Roy Verstegen
> WSI - Informix DBA

IDS Forum is maintained by Administrator with WebBBS 5.12.