Re: Set explain Comments

> Hi, > > For a couple of days we had a discussion on "update statistics" before > doing a "set explain on". I followed the instruction and seemed to get > better, more meaningful results. I say *seemed* because of my > experiencetoday. > > I was attempting to speed up a report (Fourgen standard), and used "set > explain on" on our test database, getting very encouraging results (*3 > inspeed). Made the changes in the report, copied to the live env and > timedthe old version against the new. Guess what? Little or no time > difference, in fact under certain conditions the new program was slower > bya few minutes. > > Whats happening? Am I misunderstanding, or does "set explain" not > alwayswork? This is strange as I have used this successfully in the > past. I think you may have misunderstood the relevance of SET EXPLAIN and UPDATE STATISTICS. SET EXPLAIN merely tells you how the engine decidedto do the query. If the statistics are inaccurate the optimizer could choose the wrong path. To nake sure that the optimizer chooses the right path you need to run UPDATE STATISTICS before running the query. Your experience also confirms one of the basic tenets I teach people on optimization courses. You can optimize the query with a small amount of data and get significant improvements in performance. That does not mean that you will get a performance increase when working with live data sizes. Many of the techniques used by optimizers will result in using techniques which get exponentially slower with larger volumes of data. When optimizing a large application you can get some idea through analysis of SET EXPLAIN on a sample database but there is no substitute for using SET EXPLAIN and analysing the results on the live data. > > Another question: I created a temporary table, created an index on it > andjoined to another indexed table on the respective indexed fields. > "setexplain" said the join query did not use the temp table index. Why > not? > Which was the smaller table. If the temp table was smaller than the table being joined to I bet it read the temp table sequentially. An index is only used on the larger table in a join unless there is a filter on the smaller table. > Yours > Wayne > > |Wayne Harlech-Jones > |Windhoek, Namibia, Southern Africa > |wayne@jones.mac.alt.na > > Malcolm Weallans Online Database Consultancy Phone 0628-72154 Fax 0628-37463 CIX - onlinedbc