Visit: https://zonixsoft.com (our official Website)
Web Application Performance (Paging in Sql Server)
As a Web developer, you know by now that using the default paging capabilities of ASP.NET Webcontrols like DataGrid and GridView cause the Performance issue when we have thousands and thousands of records in our database; because with every roundtrip to the data-server, you get ALL the records ALL of the time. This is fine perhaps for very small databases. But in web application, you always have to count on its Performance.
One alternate approach to this scenario is to serve “On Demand” records. That is; for example; retrieving 1-100 records, then 101-200 records result set based on some event rather than retrieving all the records all of the time. I am talking about “Paging in Sql Server”.
My-SQL
The simplest solution is provided by My-SQL using LIMIT keyword. Check out the below Query:
Select * From Product Limits 15,5
It will retrieve results from 16 to 20 records. In above Query, 15 if the offset from where you want the result set records and 5 is the number of records you want to retrieve.
Sql Server 2005
Unfortunately, SQL Server does not have an equivalent to “Limit” keyword. Its nearest is TOP N, which returns the first N rows. Still there are two alternative than we can use for “Paging”.
Using ROW_NUMBER()
Sql 2005 includes the ROW_NUMBER() function, which adds an integer field to each record. In other words, it adds the record’s position within the result set as an additional field so that the first record has a 1, the second a 2, etc.
To ensure the numbering is consistent, however, SQL Server needs to know how to sort the data. Because of this, ROW_NUMBER() must immediately be followed by the OVER() function. OVER() has one required parameter, which is an ORDER BY clause. The basic syntax is:
SELECT ROW_NUMBER() OVER(ORDER BY [Name]) as RowNum,
[Name], [SKU], [ManufacturerPartNumber]
FROM Product
The Product data in result set will be appeared sorted by Name, and it has an extra column indicating each record’s position within the results.
If we want to limit the results displayed to a certain range, we need to nest this SELECT inside another one. To limit our results to records 5 to 10, we can use the following query:
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY [Name]) AS RowNum,
[Name], [SKU], [ManufacturerPartNumber] FROM Product) AS Product1
WHERE RowNum >= 5 AND RowNum <= 10
With Nested Queries
There is one another solution that consists only of 1 sql-statement, and so is efficient and quick, even with large databases. The good news is : it will work ! Always ! it’s looking rather a bit complicated; but its not!
Let us see the Example directly to limit our results to records 6 through 10,
SELECT P.[Name], P.[SKU], P.[ManufacturerPartNumber] FROM (
SELECT TOP 5 [ProductID],[Name] FROM (
SELECT TOP 10 [ProductID],[Name]
FROM Product
ORDER BY Product.[Name] ASC) as Product1
ORDER by Product1.[Name] DESC) as Product2
INNER JOIN Product P ON Product2.ProductID = P.ProductID
ORDER by Product2.[Name] ASC
Where 5 is the number of rows you want to retrieve and 10 is the offset.
The innermost Sql Statement will fetch 10 records (1 to 10) Order by Product Name in Ascending Order.
This means that if you have a table with 10,000 records, and you want to have the last 10 records, the innermost SELECT statement will indeed retrieve 10,000 records. This of course has it’s ramifications on performance, but since it’s only the primary key fields (which are indexed) and optionally some sort fields, the impact will be minimal.
The middle Sql Statement will fetch 5 records (10 to 6) Order by Product Name in Descending Order. And Finally the Outer Sql Stateement is simply select statement that will fetch the result in Ascending Order (6 to 10). In Innermost and Midddle Sql Statement only primary key fields or the fields which are indexed are used.
After looking at this in Query Analyzer, it appears that the extra nesting does not add very much to the load. The SQL statement plays with the sorting orders to limit the records, thus resulting in paged recordsets. This means that there should be at least ONE field to be sorted. If there isn’t any, sort on the primary key field(s) !
