Making the Query Optimizer more intelligent?

[DavidJA]

Hey all,

I have a DB with several tables which are between 100,000 rows and 500,000 rows.

When I join some of these tables using INNER JOIN, SQL Server will choose to use HashMatch/Inner Join, and take around 30 seconds to execute my query.

When I change my joins to INNER LOOP JOIN, the same query runs sub-second.

It is fairly obvious to my limited SQL server that the Query Optimiser is under estimating the cost of a HASH join and/or over estimating the cost of a INNER LOOP join.

Is there any system-wide configuration I can do to make the Query Optimiser more likely to use a INNER LOOP over a has join?

I know I can use Join Hints but then what happens if my data set changes and it becomes more efficent to use a Hash join.

 

[bperry]

>>Is there any system-wide configuration I can...

I don't think there's any system-wide option to toggle. (At least I've never heard of it. Anyone else?)

You perhaps may have considered this already: If the distribution statistics for particular indexes are out of date, not useful, or not available, then the QO will make incorrect execution plan decisions based on incomplete information.

(For instance, it is well known that index statistics should be updated after a table has been truncated and repopulated.)

In any case, I think the SP sp_updatestats would update the statistics for all tables in the database. Certainly if the stats were out of date for any reason, then the QO could get it wrong. Does this make any difference in the performance you are seeing?

bp