Sorting and Paging in the Database

[MattC]

Hi,

I wanted to take advantage of the Row_Number feature of SQL 2005 to to DB
side paging. However, currently I perform my sorting on the front end.

If I perform a sort and then ask for page two, I will retrieve the wrong
items for tha page as the sort item would be wrong.

Is it possible to do something like this?

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by @OrderField) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between @Start and @End

TIA

MattC

 

[Alvin Bruney [MVP]]

Paging in the database is frequently a bad idea simply because you introduce
load that isn't necessary and may cause bottlenecks with load. What about
the paging isn't working? You need to set the correct page index, have you
done so?

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
OWC Black book on Amazon and
www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[MattC]

Ok for 100 rows I would do it all in my middle tier. For 10,000 then I
don't want that coming down the line and having 9,950 rows unseen,

So for my mediocre sized table I employ PagedDataSource stuff. What I was
looking for was a generic way of using some paging code for my 'larger'
tables.

Although if I'm only doing this a few times the code maintenance wont be too
bad on writing each one individually.

Thanks

MattC

 

[Guest]

Paging in the database is frequently a bad idea simply because you introduce
load that isn't necessary and may cause bottlenecks with load. What about
the paging isn't working? You need to set the correct page index, have you
done so?

Alvin, are you serious about sql paging?

 

[Guest]

Hi,

I wanted to take advantage of the Row_Number feature of SQL 2005 to to DB
side paging. However, currently I perform my sorting on the front end.

If I perform a sort and then ask for page two, I will retrieve the wrong
items for tha page as the sort item would be wrong.

Is it possible to do something like this?

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by @OrderField) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between @Start and @End

TIA

MattC

Matt, I think the statement is correct and working well on my server
(also for a second page).

How do you calculate the @Start value, maybe this is the reason of
your problem?

 

[MattC]

Well I wanted to write a TVF that would allow me to pass in a table and the
start and end points.

Select x,y,z FROM into #mytable
bigtable where ID = @SomeID
SortBy @MyColumn

then do

SELECT * FROM MyPagingFunction(#mytable , @PageNumber, @PageSize)

Now my first issue was I can't do dynamic variable based sorting ok so maybe
I'll use dynamic sql or case statements. But I really wanted to wrap the
paging code into a function.

Can CLR written TVF's accept tables?

This topic probably should now be on a SQL Server 2005 group I think though
now.

MattC

 

[Guest]

Well I wanted to write a TVF that would allow me to pass in a table and the
start and end points.

Select x,y,z FROM into #mytable
bigtable where ID = @SomeID
SortBy @MyColumn

then do

SELECT * FROM MyPagingFunction(#mytable , @PageNumber, @PageSize)

Now my first issue was I can't do dynamic variable based sorting ok so maybe
I'll use dynamic sql or case statements. But I really wanted to wrap the
paging code into a function.

But here the dynamic variable based sorting is working (@OrderField),
isn't it?

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by @OrderField) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between @Start and @End

Or, I don't get it...

Can CLR written TVF's accept tables?

Well, I've never try it, what about a stored procedure?

Alexey

 

[Alvin Bruney [MVP]]

I see where my answer could cause confusion. I'm referring to page index at
the front end level NOT sql paging.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[Guest]

I see where my answer could cause confusion. I'm referring to page index at
the front end level NOT sql paging.

Please forgive me as I've misunderstood you.

 

[Alvin Bruney [MVP]]

What's your opinion on his architecture? Mine? sure it can work and it is
probably the easiest solution. However, with large data sets as s/he
implies, I can see a DBA getting really angry about paging logic inside SQL
server.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[Guest]

What's your opinion on his architecture? Mine? sure it can work and it is
probably the easiest solution. However, with large data sets as s/he
implies, I can see a DBA getting really angry about paging logic inside SQL
server.

It's a question of what is more important in this particular
application (performance vs. design)

Actually, this is what Microsoft recommended as "an improvement
of .NET application performance and scalability"

http://msdn2.microsoft.com/en-us/library/ms979197.aspx

(Note, this whitepaper is for SQL 2000 and .NET 1.1)

Although there are significant improvements in .NET 2 (Gridview and
ObjectDataSource), a quick and efficient database paging is still a
problem.