Optimizing SQL TOP Queries In MSSQL Database
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
2. Using Variables.
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!
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'
DECLARE @topCount INT SET @topCount = 5 SELECT TOP (@topCount) (...)
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!
Comments
Post a Comment