Donate

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

Comments

Donate

Popular Posts From This Blog

WPF CRUD Application Using DataGrid, MVVM Pattern, Entity Framework, And C#.NET

How To Insert Or Add Emojis In Microsoft Teams Status Message

TypeScript Error Or Bug: The term 'tsc' is not recognized as the name of a cmdlet, function, script file, or operable program.