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.
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.