Re: Performance Problem - selecting date range and values
What does set explain on tell you?
What's the index? I'd guess you need an index on (day, text)
Oh, and since OTC isn't here: update statistics ?
>>> Erhard Schwenk <eschwenk@fto.de> 09/06/00 10:03am >>>
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.
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.
Is there any possibility to speed this up? I think of special create
Options for the table, a better way to write the SQL Statement or
something like this?
We use Informix Dynamic Server 7.23 on Tru64 Unix (AlphaServer 1200, 512
MB RAM)
Best regards,
Erhard Schwenk