FW: Strange Performancetuning

Marcus, I think I have an idea what could happen. If C<->B join returns considerable(>10000) number of rows, then Your intermediate index build prevents the server from sequential scan on the temporary (C <-> B) join result, and join A <-> (C <-> B) becomes more efficient. In Informix documentation, they mention situations when the server might create an automatic index on temporary table. I've never seen that. Only hash joins. This is why 'join order' directives and explicit 'temporary table' creation are not equivalent in Your case. Try 'temp table' without index. I hope it will be 15 sec ------------------------------------------ Alexey Sonkin > -----Original Message----- > From: Markus Bschorer [mailto:mb@worxbox.com] > Sent: Thursday, April 01, 2004 1:56 PM > To: Alexey Sonkin > Subject: AW: Strange Performancetuning > > Hi Alexey, > > thanks for your answer. > > I already did some experiments on that what you told me. > > Using the optimizer hint --+ordered, I tried all combinations of > join-orders. The best result uses the join-order which the optimizer tooks > when I didn't give him any hint. So, the best result was 15 seconds again. > > I agree with you, that there are situations in which he knowledge about > the > relations between several table-data might help you, but not in my case. > > This is the reason why I wrote that problem to the informix-newsgroup. > > Bye > Markus > > > > > -----Urspr'ngliche Nachricht----- > Von: Alexey Sonkin [mailto:alexeis@grandvirtual.com] > Gesendet: Donnerstag, 1. April 2004 19:19 > An: 'Markus Bschorer'; informix-list@iiug.org > Betreff: 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