Re: QUERY Optimizer

> Hi netters > > I have observed that the optimizer (SQL 4.0 and an On-Line 4.0 DB) is > very often guessing wrong, even if we run update statistics. > > The estimated number of rows returned does very much depend on the > sequence in which you enter the SELECT statement. In one case we had > 3 different SELECT statements, which should all return the same number > of rows, but the optimizer estimited the number of rows returned to > 250, 170894 and 262477 !!!! > > Besides running update statistics frequently, are there any rules/hints > which will help the optimizer to optimize instead of pure guessing. > > Best regards Christian Hansen The estimate that the query optimiser makes is not really supposed to be an accurate figure. What it shows is a quess at the number of rows the backend will have to view to produce the results. So if your where clause is written so that non indexed filters are used to join tables the number of rows queried can be exceptionally large - see above. Also the optimiser will sometimes produce different query strategies for differently worded select statements. Even though they produced the same end result albeit at wildly different estimates and actual costs. This is dependant on how the optimiser parsed the where clause. Some rules to improve matters are listed below other people may have more. 1. If you know which table has the least rows or is the one that you wish to be treated as the most important table list this first in both the from clause and the where clause. The optimiser may still disagree but at least you tried and this will work for older engines. 2. Make sure that all filters use an index and check that it is the right index. If you have two composite indexes with the same first entity the current optimiser will use the first of the two indexes from the sysindexes table even if the second one is a better match to the filter. 3. Try and make sure no autoindexing is going on. Either create the index as part of the database or in the program. 4. If you know the value of a field in the where clause set it. Don't use another table.column which has already been set to that value. (eg Dont use where a.aa = "1" and b.aa = a.aa use where a.aa "1" and b.aa = "1") 5. Make sure that all filter statements in the where clause are kept simple and are the right way round. In other words put the item from the table that will be retreived first in the list first and items that make up a key in the order in which they are in the key. (Eg Dont use where a.aa = "1" and a.bb = "2" and b.bb = a.bb and b.aa = a.aa use where a.aa = "1" and a.bb = "2" and a.aa = b.aa and a.bb = b.bb 6. Make sure that filter statements that are not part of the indexed filter are listed after the index filter. 7. If you create a temporary table remember to index it within the program before you start including it in new select statements. Not all of these will necessarily have an effect some are just designed to make the select statement more readable. Occasionally you may find that they make things worse but I find that they work pretty well in most cases. Hope this helps. Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------