RE: DB performance : 45 seconds for a query ?
Hello, Michel.
Don't forget to look into fragmentation by expression (by month
would be good...) You can also frag your indexes by expression
( good for OLTP, not so good for DSS). Sounds like you may want
to drop & rebuild indexes anyway, with FILLFACTOR 80 or so. Run
oncheck -pT <database:table> and check the lowest (leaf) levelAVG FREE BYTES column to see how full your leaf pages are (do=20
you have 2K pages?).
Could multiple existing indexes be combined into a single=20
composite index? You could still get to a single column via=20
WHERE A =3D A AND B =3D <value>.
-Kevyn ;-{>
-----Original Message-----
From: owner-informix-list@iiug.org
[mailto:owner-informix-list@iiug.org]On Behalf Of Tommi M=E4kitalo
Sent: Sunday, January 03, 1999 1:22 PM
To: informix-list@iiug.org
Subject: Re: DB performance : 45 seconds for a query ?
Hi Michel
This sounds like ugly database-design. You sould create only one table =
whith
a
month field.
Optimizing a query for one table is far easier than with union. You =
should
add
this month field
in every index your mentioned and access through than field. Even with =
about
40
million
records access-time should be better.
But I'm sure you forgot update statistics. This will improve =
performance far
more.
And you should read the documentation for 'set explain on'. So you can =
get
the
access path.
It should have tell you, than the engine made a full table scan instead =
of
using
your index.
Michel Dalle wrote:
> Hi,
>
> I have a frustrating question concerning Informix performance (on a =
UNIX
> system).
>
> The basic database structure is pretty simple : there is 1 type of =
table
with
> 6 (significant) fields :
> - reference nr.
> - date
> - from
> - to
> - amount
> - currency
>
> There are 12 tables of this type : one for each of the last 12 =
months.
Each
> table should hold about 3 to 4 million records. There is an index for
> each field.
> ...
_________________________________________________________________
Tommi Maekitalo
Dr. Eckhardt + Partner GmbH