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.
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
Erhard Schwenk wrote:
> 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
1. Have you run UPDATE STATISTICS HIGH on the table?
2. What are you using to judge response time? It might be the first
records come back faster in the select without day in the where clause, but
the entire select takes just as long. I would use select count(*) from t
where blah ... Or use a program which fetches all the rows(Which you might
already be doing).
Maybe more ideas once I know the answers to these questions.
Hope this helps,
Will
Run the recommended UPDATE STATISTICS as documented in the Performance Guide.
Also do you mean that you have three singleton indexes one each on set, day,
and text? (BTW text is a minor keyword as there is an Informix type TEXT,
which may cause you grief down the road so you may want to rename that
column.) Informix, except for 8.xx, can only use one index at a time on a
table. Looks like the text filter is a better one that the day filter and
including the day range is causing the optimizer to select the other
index. Run the query both ways under SET EXPLAIN ON and see. If so try
adding two more indexes and see what happens:
create index t_i4 on t(day, text);
create index t_i5 on t(text, day);
Because of the range search on day and the LIKE search on text neither index
can be used for direct lookup but one of the columns can be looked up
directly and the index pages used for a "LOWER INDEX FILTER" on the other.
Giving the optimizer both indexes and then updating stats allows the
optimizer to select the better one for specific values.
Art S. Kagel
Erhard Schwenk wrote:
>
> 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