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" <[e-mail] in message
news:eI**************@TK2MSFTNGP02.phx.gbl...

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

[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
"Alvin Bruney [MVP]" <some guy without an email addresswrote in message
news:O%****************@TK2MSFTNGP03.phx.gbl...

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" <[e-mail] in message
news:eI**************@TK2MSFTNGP02.phx.gbl...

>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

[Alexey Smirnov]

On May 16, 3:20 am, "Alvin Bruney [MVP]" <some guy without an email
addresswrote:

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?

[Alexey Smirnov]

On May 15, 6:58 pm, "MattC" <[e-mail]:

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
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@u30g2000hsc.googlegr oups.com...

On May 15, 6:58 pm, "MattC" <[e-mail]:

>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?

[Alexey Smirnov]

On May 16, 3:41 pm, "MattC" <[e-mail]:

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
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11**********************@q75g2000hsh.googlegr oups.com...

On May 16, 3:20 am, "Alvin Bruney [MVP]" <some guy without an email
addresswrote:

>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?

[Alexey Smirnov]

On May 17, 1:58 pm, "Alvin Bruney [MVP]" <some guy without an email
addresswrote:

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
"Alexey Smirnov" <al************@gmail.comwrote in message
news:11*********************@q75g2000hsh.googlegro ups.com...

On May 17, 1:58 pm, "Alvin Bruney [MVP]" <some guy without an email
addresswrote:

>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.

[Alexey Smirnov]

On May 18, 9:29 pm, "Alvin Bruney [MVP]" <some guy without an email
addresswrote:

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.