Re: bad performance with a simple select statement
lol007@my-dejanews.com wrote:
>
> Hello:
> I run INFORMIX-Universal Server Version 9.14.UC1X3 on a SGI/IRIX 6.2 INDY
> RS5000SC box.
> I create a buffered-logging database in a mirrored dbspace.
> I create the following table :
> create table test (
> f1 int,
> f2 smallfloat,
> f3 smallfloat,
> f4 smallfloat,
> f5 smallfloat,
> f6 smallfloat,
> f7 smallfloat,
> f8 smallfloat,
> f9 smallfloat,
> f10 smallfloat,
> PRIMARY KEY (f1)
> );> I load the table with a 400K records file.
> The following simple statement :
> select * from test where f1=200000;> takes 10 seconds !?
> I even tried the following :
> begin work;
> set isolation to dirty read;> select {+ USE_INDEX(test,100_1) } * from test where f1=1;
Actually the name of the index created by a primary key for tableid 100
is " 100_1" so you cannot successfully specify it in a directive. If
you want to be able to do that drop the primary key constraint, create
a unique index on f1, then alter the table to add the primary key back.
the primary key will use the existing index you have manually created
and you can name that index in a directive.
Anyway the real problem? What is the query plan reported by SET
EXPLAIN ON? Have you updated statistics? The last is IMPERATIVE for
the optimizer to be able to select the best way to perform the query.
Without that the optimizer thinks that you have a table with zero rows
so why not perform the query via table scan (I assume that is the plan
since you did not report the SET EXPLAIN output). Run the following
commands:
UPDATE STATISTICS MEDIUM FOR TABLE test DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH FOR TABLE test(f1);
Then try the query. It should be even more instantaneous than SQL
Server. :-)
> commit work;
> Still 10" long !
> The same query on MS SQL 6.5 is almost instantaneous ... :-(
Art S. Kagel