Sorting and Paging in the Database

  • Thread starter Thread starter MattC
  • Start date Start date
M

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
 
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
 
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
 
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?
Click to expand...

Alvin, are you serious about sql paging?
 
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
Click to expand...

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?
 
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
 
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.
Click to expand...

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?
Click to expand...

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

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

Please forgive me as I've misunderstood you.
 
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
 
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.
Click to expand...

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