RE: Strange Performancetuning
Markus,
Yes, this might happen, and it might be not related
to any optimizer bug.
Consider the following scenario:
Join results for tables b and c might return very few
rows because filters, applied to each table, are not
restrictive against each table alone but become restrictive
when they work together.
In that case, it is much more efficient to join B & C first
and then join the resulting table to A.
Database optimizer knows nothing about this possible
high selectivity of b<->c join, because there are no
cross-distributions...
As a result, optimizer might choose a different join order:
instead of joining b & c first, it takes A as a primary
table for join and then picks rows from B and C using indexes
and applying proper filters AFTER fetching rows from B & C.
This is a case, when clever developer might make a big
favor to the database server by creating temporary tables
or by specifying the join order explicitly with optimizer directives.
I believe that there are scenario's when the database
server is unable to construct the optimal plan because it doesn't
have the information about the column cross-distributions
that the developer might have in his head.
------------------------------------------
Alexey Sonkin
> -----Original Message-----
> From: Markus Bschorer [mailto:mb@worxbox.com]
> Sent: Thursday, April 01, 2004 10:36 AM
> To: informix-list@iiug.org
> Subject: Strange Performancetuning
>
> IDS 9.4 (WE) on RH9 Linux 2.4.20
>
> Hi all,
>
> we found a very strange Performance-Tuning of a simple SELECT-Statement,
> and
> now, we fear to have to rewrite a lot of application-code in order to tune
> our SQL-Statements:
>
> We have 3 tables (a,b,c), which are joined each by one join-column:
> a --> b --> c (each 1:n)
> All join-columns have a single index, some unique, some duplicate.
> We compared the following two SELECT's which have the same results.
>
> Query 1: SELECT a.* FROM a,b,c WHERE ...
>
> Query 2: SELECT b.join_col_to_a from b, c WHERE ... INTO TEMP temp01;
> CREATE INDEX idx01 ON temp01(join_col_to_a);
> UPDATE STATISTICS FOR TABLE temp01;> SELECT a.* FROM a, temp01 WHERE .....
>
>
> Query 1 takes 15 seconds
> Query 2 takes 1 second. (all 4 Statements!!)
>
> This is really confusing. We executed some UPDATE STAISTICS on these
> tables,
> and the optimizer seems to go the right way in both queries. However,
> Query1
> is much more elegant and I expected, that Query1 should not be a problem
> for
> the Database-Server.
>
> I made the same test under IDS 9.21 and had (nearly) the same effect.
>
> Does anyone know the reason for this behaviour? Did I miss any important
> ONCONFIG-Setting?
>
> Thanks in advance!
>
> bye
> Markus
>
>
>
>
>
sending to informix-list