Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!uupsi!sunic!chalmers.se!appli!niklas
From: niklas@appli.se (Niklas Hallqvist)
Newsgroups: comp.databases
Subject: Informix SQL select time optimization
Keywords: Informix SQL
Message-ID: <1356@appli.se>
Date: 18 Apr 91 13:55:52 GMT
Organization: Applitron Datasystem AB, GOTHENBURG, SWEDEN
Lines: 70


	Hello!

I'm using Informix ESQL and have problems understanding how this
product does it's queries.  Very often when I've typed down a complex
select statement I find that it gets real slooooow, in spite of the
possibility of getting a fast keyed search.  It seems that ISQL's
heuristics fails to find the best search path throw the joins.  Is
there some way to find out these heuristics?  If I knew them, I could
always rearrange my select statements to reflect the search strategy
ISQL imposes on them.  The only thing I've discovered myself so far
is that the order of the tables specified in the from clause is very
important if there are several ways to go about the search.

As an example to this problem, I have the following scenario right now:

5 tables A, B, C, D, E

The relations between the tables are as follows:

for every A-column there are many B:s, both A and B have an index on the column
that the join is on.

for every C-column there are many B:s, but only C has an index on the joined
column.

for every D-column there are many C:s, both C and D have an index on the column
the join is on, D:s index is unique. (the C index is really a prefix of the
composite index mentioned below)

for every D-column there are many E:s, both D and E have an index on the column
the join is on, D:s index is unique. (the E index is really a prefix of the
composite index mentioned below)

for every E-column there are many B:s, but only B has a unique index on the
joined column.

On top of this, there is a unique composite index in C on the columns which
joins an C to a combination of B and D.  There is a also unique composite
index in E on the columns which joins an E to a combination of B and D.

Of these tables only B is large (~ 400000 records) and the other ones
contain just a couple of thousand records or less.  The join between
A and B are the one which is important as that's the one which cut down
B's size to just some thousand records.

What I want to do is this:

select lots_of_fields
from A, B, C, D, E
where A.unique_index_of_A = $user_entered_variable
and   A.unique_index_of_A = B.unique_index_of_A_and_of_B_too
and   E.second_member_of_Es_index = B.second_member_of_Es_index
and   E.first_member_of_Es_index = D.first_unique_index_of_D
and   D.secod_unique_index_of_D = C.second_unique_index_of_D
and   C.unique_index_of_C = B.unique_index_of_C;

Did you get that???

This query takes hours to complete, it should take minutes.
Well actually I solved this using yet another temporary table just
containing the joined rows of A, B and C, but thats space-expensive.

					Niklas

-- 
Niklas Hallqvist	Phone: +46-(0)31-40 75 00
Applitron Datasystem	Fax:   +46-(0)31-83 39 50
Molndalsvagen 95	Email: niklas@appli.se
S-412 63  GOTEBORG, Sweden     mcsun!sunic!chalmers!appli!niklas