Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Sorting and Paging in the Database

Reply
Thread Tools

Sorting and Paging in the Database

 
 
MattC
Guest
Posts: n/a
 
      05-15-2007
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


 
Reply With Quote
 
 
 
 
Alvin Bruney [MVP]
Guest
Posts: n/a
 
      05-16-2007
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 Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
 
 
 
MattC
Guest
Posts: n/a
 
      05-16-2007
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 address> wrote in message
news:O%(E-Mail Removed)...
> 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 Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>

>
>



 
Reply With Quote
 
Alexey Smirnov
Guest
Posts: n/a
 
      05-16-2007
On May 16, 3:20 am, "Alvin Bruney [MVP]" <some guy without an email
address> wrote:
> 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?

 
Reply With Quote
 
Alexey Smirnov
Guest
Posts: n/a
 
      05-16-2007
On May 15, 6:58 pm, "MattC" <(E-Mail Removed)> wrote:
> 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?

 
Reply With Quote
 
MattC
Guest
Posts: n/a
 
      05-16-2007
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> On May 15, 6:58 pm, "MattC" <(E-Mail Removed)> wrote:
>> 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?
>



 
Reply With Quote
 
Alexey Smirnov
Guest
Posts: n/a
 
      05-16-2007
On May 16, 3:41 pm, "MattC" <(E-Mail Removed)> wrote:
> 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

 
Reply With Quote
 
Alvin Bruney [MVP]
Guest
Posts: n/a
 
      05-17-2007
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> On May 16, 3:20 am, "Alvin Bruney [MVP]" <some guy without an email
> address> wrote:
>> 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?
>



 
Reply With Quote
 
Alexey Smirnov
Guest
Posts: n/a
 
      05-17-2007
On May 17, 1:58 pm, "Alvin Bruney [MVP]" <some guy without an email
address> wrote:
> 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.

 
Reply With Quote
 
Alvin Bruney [MVP]
Guest
Posts: n/a
 
      05-18-2007
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...
> On May 17, 1:58 pm, "Alvin Bruney [MVP]" <some guy without an email
> address> wrote:
>> 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.
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Is selecting column, filtering , sorting, paging is done in database or memory when use linq to sql? Ryan Liu ASP .Net Web Controls 0 12-12-2008 06:40 PM
GridView paging/sorting vs. Retrieving data from database gnewsgroup ASP .Net 0 04-10-2008 01:00 PM
Combining numeric mode paging and nextPreview paging in datagrid Red ASP .Net 1 03-12-2005 11:41 PM



Advertisments