> Does anyone know of an efficient way to page result sets with
> ADO.NET and SQL Server?
>
> Why the heck doesn't SQL Server support a feature similar to
> Oracle's ROWNUM?! Does Oracle have this patented?
Yukon will have it afaik, but indeed it would be very handy :)
> The best thing I have come up with so far is to 1) establish
> a rule that every table (at least those tables that need to
> support paged results) must have to have a single field
> unique key. 2) then use the following stored proc to get
> paged results:
[snip]
(note, code below is copied/pasted from the LLBLGenPro forums, and the
code is not written by me, except option 2. )
generally you can do something like: (option 1)
SELECT TOP x ProductID, Name
FROM Product
WHERE CategoryID = @categoryID AND ProductID NOT IN
(
SELECT TOP y ProductID
FROM Product
WHERE CategoryID = @categoryID
ORDER BY Name
)
ORDER BY Name
where x is the page size and y is the page size * amount of pages seen.
This too requires sql creation on the fly, due to the TOP clause which
doesn't accept a parameter
There are other tricks: (option 2)
SELECT TOP x ProductID, Name
FROM Product
WHERE @lastProductID IS NULL
OR
(
ProductID > @lastProductID
AND
@lastProductID IS NOT NULL
)
ORDER BY ProductID, Name
where x is the page size and @lastProductID is OR NULL (start of the
pages) OR the last productID on the previous page
This last one is more efficient, (can bug a little, I write it down from
my bare head in this early morning) as it doesn't require sql
construction on the fly. It works because you specify the last ProductID
seen. Your situation can thus work well in this case. The last option
doesn't work well in situations where you do not have a sequenced PK
field.
Another one, using a table variable: (option 3)
CREATE PROCEDURE [dbo].[spGetCustomersWithPaging]
@recordsByPage int = 30,
@currentPage int = 1
AS
BEGIN
SET NOCOUNT ON
DECLARE @temp TABLE (
[Index] [int] IDENTITY (1, 1) NOT NULL,
[CustomerID] char (6) COLLATE SQL_Latin1_General_CP1_CI_AS
)
INSERT INTO @temp SELECT CustomerID FROM Customer
SELECT * FROM [Customer]
WHERE
CustomerID IN (
SELECT CustomerID
FROM @temp
WHERE
[INDEX] > (@currentPage-1)[EMAIL PROTECTED]
AND [INDEX] <= @[EMAIL PROTECTED]
)
END
GO
This is based on the idea that you have a rather small set of data you
have to walk through, and you add a sequenced index to the data in the
temp table, then requery that temp table. Will not work with tables with
a lot of rows of course because the table variable will then contain the
complete table, hwich is not that wise :)
Another solution would be to page on the client. All solutions with
SqlServer will re-execute the search query, which too can be quite
intensive. It's sometimes more wise to simply fetch a resultset, for
example 500 rows, and page through that resultset via a dataset or
object collection. This way, you don't have to re-execute the query
again, which can be more efficient in some situations. If you then reach
the end of your in-memory pages, you can decide to requery the search
and repeat the trick with the next 500 rows.
So it's a matter of how big your rowset is and how many rows your
resultset has to have. These are things which are neglected often but
are very important: is it wise to offer a resultset of 10,000 rows to a
user? I don't think so. :)
Frans
--------------------------------------------------------------------
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
My .NET Blog : http://weblogs.asp.net/FBouma
--------------------------------------------------------------------
===================================
This list is hosted by DevelopMentorŪ http://www.develop.com
Some .NET courses you may be interested in:
NEW! Guerrilla ASP.NET, 26 Jan 2004, in Los Angeles
http://www.develop.com/courses/gaspdotnetls
View archives and manage your subscription(s) at http://discuss.develop.com
