Blog Archives

Custom paging query comparison Sql server 2000 , 2005 and 2012 (denali)

Custom paging is a very common requirement for any web application. It was always performance enhancer for our old applications. Linq and generic collections enabled us to process and query the result sets in memory. However, if the query can fetch only desired number of records with a better query performance, we need not perform any manipulation later on.

I am going to describe the custom paging query which had evolved with every Sql server upgrade. I will compare Sql Server 2000, 2005 and 2012 (denali) version to showcase the performance improvement. Sample DB isAdventureWorks.

Sql Server 2000: In 2000 we had to create a temp table before we skip certain number of records and fetch required number of records only. Temp table creation and inner join degrades the query performance here.


CREATE PROCEDURE GetPersonPaged_Sql2000
(
@startRowIndex int,
@pageSize int
)
AS
BEGIN
 SET NOCOUNT ON;

DECLARE @lastRowIndex int

SET @lastRowIndex = (@startRowIndex + @pageSize) - 1;

--Create a table variable
 DECLARE @TempItems TABLE
 (
 ID int IDENTITY,
 BusinessEntityID int,
 firstname varchar(200),
 MiddleName varchar(200)
 )
 -- Insert the rows from tblItems into the temp. table
 INSERT INTO @TempItems (BusinessEntityID,firstname,MiddleName)
 SELECT BusinessEntityID,firstname,MiddleName FROM Person.Person
 order by BusinessEntityID

-- Now, return the set of paged records
 SELECT p.* FROM @TempItems t
 INNER JOIN Person.Person p ON
 p.BusinessEntityID = t.BusinessEntityID
 WHERE ID BETWEEN @startRowIndex AND @lastRowIndex
 order by BusinessEntityID

END

Sql Server 2005: With Ranking functions introduced in 2005 upgrade it was easier to get a row number for each record (Please note that Sql Server never had any keyword RowNum like Oracle). ROW_NUMBER() is the function which helps sorting and numbering any result set. Please refer to http://msdn.microsoft.com/en-us/library/ms186734.aspx to know more about this function.
New implementation was even better using Common table expression CTE. Refer http://msdn.microsoft.com/en-us/library/ms190766.aspxto read more about CTE.

CREATE PROCEDURE GetPersonPaged_Sql2005
(
@startRowIndex int,
@pageSize int
)
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @lastRowIndex int

SET @lastRowIndex = (@startRowIndex + @pageSize) - 1;

;with CTE
 AS
 (
 select BusinessEntityID,firstname,MiddleName , ROW_NUMBER() OVER (order by BusinessEntityID ) as rownum
 from Person.Person
 )
 select * from CTE
 where rownum between @startRowIndex AND @lastRowIndex

END

It had improved the query performance by more than 80% than the Sql 2000 query. You can test the same by comparing execution plans.
Finally the winner is :

Sql Server 2012:It introduced new keywords like OFFSET and ROWS. It not only reduces the number of lines of code but improve the query performance by more than 50% when compared to Sql 2005 query using CTE.


CREATE PROCEDURE GetPersonPaged_Sql2012
(
@startRowIndex int,
@pageSize int
)
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @lastRowIndex int

SET @lastRowIndex = (@startRowIndex + @pageSize) - 1;

SELECT * FROM Person.Person
 ORDER BY BusinessEntityID
 OFFSET @startRowIndex ROWS
 FETCH NEXT @lastRowIndex ROWS ONLY;

END

Conclusion:    2012 query is the best of all three in terms of ease, lines of code and performance. If you are still using the old style query(even after upgrading to next version of sql server) for custom paging, this is high time you should switch to query with new syntax which is best suited for that particular version.

Note: I did not cover Sql server 2008 as it did not add any features which could add any value to custom paging querying.

I hope it is useful!

Advertisements

Parameter sniffing in Sql server

Hi All,

I work for a project which involves very huge data reporting using SSRS. Business users reported a bug which was very difficult to analyze.
Whenever there was a new report added to report server and a certain user requests report data for smaller territory like New Jersey to see his sales figures for his territory. The report renders very quick. But whenever a user requests for a bigger territory like Texas it takes more time almost 10 times (phew!).

when i started looking into the issue and found something interesting which i have only read in my academic days.

SP was like this.

CREATE PROCEDURE TerritorySales
@TerritoryId int
AS
BEGIN
SELECT sale
FROM dbo.SaleDetails
WHERE TerritoryId = @TerritoryId
END

Even though all looks fine, there is a catch here. @TerritoryId is the input parameter which is used directly into the where condition. So here is what happens with Sql server.

1. developer executes the stored proc. Sql server compiles the SP and save it.
2. User calls the SP with certain parameters (say TerritoryId which is smaller). it sure will have less data.
3. Sql server creates an execution plan which is optimized for this input parameter (smaller territory) and saves it.
4. Next time a user call the same SP for a bigger territory (more data). Sql server still uses the same execution plan which was saved earlier (optimized for small data). It takes unusually more time.

Solution to this problem (called Parameter sniffing) is very simple.

1. Assign the input parameter to a local variable.
2. use that local variable in the sql query.

CREATE PROCEDURE TerritorySales
@TerritoryId int
AS
BEGIN
DECLARE @i_TerritoryId int

SET @i_TerritoryId = @TerritoryId

SELECT sale
FROM dbo.SaleDetails
WHERE TerritoryId = @i_TerritoryId
END

Even though it all looks one and the same thing, it surely make a difference in certain data intensive queries.

I hope it helps.

Praveen