Re: Strange Performancetuning
On Thu, 01 Apr 2004 10:35:38 -0500, Markus Bschorer wrote:
Alexey hit it pretty well. Just want to add the elegant version of your 2nd
solution:
select a.*
from a
where ...
and a.join_to_b_col in (
select b.join_to_a_col
from b,c
where ...);
Or you might try an EXISTS clause instead of the IN to bind the sub-query.
Might even be sub-second response.
Art S. Kagel
> 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