Paging in Sql Server
(this is one of the article i posted on our local intranet repository; hope it will be helpful to others)
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)
!