Re: Strange Performancetuning

I broadly agree with the other two, but would suggest there must be some filter criteria you didn't tell us or some missing join index that means it makes such a difference what order the tables are processed in. Or maybe your filter or join column data has skewed values and should have UPDATE STATISTICS MEDIUM run on it. One handy technique is to force the order you believe to be best using optimiser directives ("SELECT --+ ORDERED"), run it through EXPLAIN and see if it does an autoindex or hash join or something else expensive along the way. That'll be where to add an index. Andy "Markus Bschorer" <mb@worxbox.com> wrote in message news:<c4hcpo$qed$01$1@news.t-online.com>... > 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