Re: bad performance with a simple select statement
Did you actually run "update statistics", see Informix
recommendations for update statistics.
If the statistics are not updated, the optimizer will not
have much information to actually, adress the best
path. We have experimented this type of situation, in larger
tables (after we loaded them) of course, but remember that
maybe 8 seconds out of the 10, are being used by the
optimizer to actually choose the best path. You can use
the set optimization low (if you want), but just after run
update statistics.
Regards,
Mario Estrada
-------------------------------Reply Separator-----------
-----Original Message-----
From: lol007@my-dejanews.com <lol007@my-dejanews.com>
To: informix-list@iiug.org <informix-list@iiug.org>
Date: Domingo 20 de Septiembre de 1998 03:38 PM
Subject: bad performance with a simple select statement
>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;
>commit work;
>Still 10" long !
>
>The same query on MS SQL 6.5 is almost instantaneous ... :-(
>
>Any idea ?
>LL
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>