Tuesday, March 26, 2013

Optimizing SQL TOP queries (REPOST from geekswithblogs.net)

Here's an interesting article on optimizing queries using Top statement
to filter result sets: Why SQL Top may slow down your query?
The solutions are the following:
1. using Hash joins
 SELECT TOP 5  
 [Articles].Id  
 ,CountryCategories.Name  
 ,CityCategories.Name  
 FROM [Articles]  
  INNER HASH JOIN CategoryCountry2Articles  
 ON [Articles].Id = CategoryCountry2Articles.IdArticle  
  INNER HASH JOIN CountryCategories  
 ON CountryCategories.Id = CategoryCountry2Articles.IdCountry  
  INNER HASH JOIN CategoryCity2Articles  
 ON [Articles].Id = CategoryCity2Articles.IdArticle  
  INNER HASH JOIN CityCategories  
 ON CityCategories.Id = CategoryCity2Articles.IdCity  
 WHERE CountryCategories.Name = 'country1'  
 AND CityCategories.Name = 'city4'  
2. Using Variables.
 DECLARE @topCount INT  
 SET @topCount = 5  
 SELECT TOP (@topCount)  
 (...)  
I am pretty much interested why using variables is much faster. Compared with other findings that variables slow your queries:
a. Parameter Sniffing Stored Procedures
b. Sql server query fast but slow from procedure
c. Why SQL Server go slow when using variables
d. SQL 2008 row number slow with variables

Cheers!

0 comments:

Post a Comment