RE: Performance Problem - selecting date range and values
Have you run the recommended UPDATE STATISTICS? The optimizer may need a
bit more information in order to make an intelligent decision on the query
path.
See below for further comments . . .
> -----Original Message-----
> From: Erhard Schwenk [mailto:eschwenk@fto.de]
> Sent: Wednesday, September 06, 2000 5:04 AM
> To: informix-list@iiug.org
> Subject: Performance Problem - selecting date range and values
>
>
> Hello,
>
> Can anybody tell me how I can tune performance on the following:
>
> My database is a single table (in fact, it is two tables with a join,
> but I do not think that is the Problem here) like this:
>
> CREATE TABLE t (
> set INTEGER,
> day DATE,
> text CHAR(32)
> );>
> I have created an index over each of the three columns. The Table has
> about 2 Millions of Rows.
>
So each column has a corresponding index?
> Now I need all sets within a given date range and text
> beginning like a
> constant, e.g.:
>
> SELECT set FROM t
> WHERE text LIKE 'ABC%'
> AND day <= '01-01-1999'
> AND day >= '01-01-1998'>
> This runs fine, but it takes forever time. If I remove the date
> selection it goes as fast as light speed, but I need the date
> column and
> like this the select lasts up to 2 Minutes, depending on the number of
> results.
>
What does sqexplain.out say?
This might sense if this table has indexes made up of single columns. The
optimizer has to make a best call as to which index to use (UPDATE
STATISTICS??). Depending on the selectivity of the column 'day', maybe you
could use a composite index, such like . . . .
CREATE INDEX ix_temp on t (day, text);
John Carlson
Informix DBA
EDS - WHSmith USA
3200 Windy Hill Road, Suite 1500 West
Atlanta, GA 30330