Re: Need some performance help ..

alvin lam wrote: > > Hi > > I am using Online 7.14 engine; In 1 sql, I have to join 5 tables together; > > > > table 1 is a "orders" files which has over 12,000,000 records; > > table 2 to 5 are for description purpose. they have a little bit over 10,000 > records. > > Looking at the sqexplain.sql; the table 1 never being filtered as the first > table; which makes the execution of query so long ... > > I have try 'Update statistics' high on all the keys; but does not help > at all; Several points on this: 1) The optimizer will frequently join the largest table last unless the specific filters and indexes on that table would whittle its contents down to a VERY SMALL subset. This is because it determines that the filters on the other tables along with the join conditions will eliminate more rows that the direct filters can. 2) If this is taking very long look to see that you have indexes on the join conditions for all of the joins that are possible. 3) Optimizing a join of 5 tables can take quite some time as there are 120 possible ways to perform the join and the optimizer will calculate the cost of every combination. You could try SET OPTMIZATION LOW; which will take the best subpath only at each level of analysis and not backtrack to test the other paths this will cut the possible paths analyzed from 120 (5!) to 14 (5+4+3+2). 4) Lastly, do you have an ORDER BY clause? If so it is unlikely that the ORDER BY would use the same index as the optimizer is selecting for join optimization even if the ORDER BY only references one table. Therefore the query has to wait until all results are collected to begin sorting; after which data can be returned. If the query is returning large numbers of rows this will take a while. Art S. Kagel