Sorting and Paging in the Database

Discussion in 'ASP .Net' started by MattC, May 15, 2007.

  1. MattC

    MattC 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
    MattC, May 15, 2007
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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], May 16, 2007
    #2
    1. Advertising

  3. MattC

    MattC Guest

    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%...
    > 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" <> wrote in message
    > news:...
    >> 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, May 16, 2007
    #3
  4. 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?
    Alexey Smirnov, May 16, 2007
    #4
  5. On May 15, 6:58 pm, "MattC" <> 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?
    Alexey Smirnov, May 16, 2007
    #5
  6. MattC

    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.

    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" <> wrote in message
    news:...
    > On May 15, 6:58 pm, "MattC" <> 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?
    >
    MattC, May 16, 2007
    #6
  7. On May 16, 3:41 pm, "MattC" <> 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
    Alexey Smirnov, May 16, 2007
    #7
  8. 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" <> wrote in message
    news:...
    > 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?
    >
    Alvin Bruney [MVP], May 17, 2007
    #8
  9. 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.
    Alexey Smirnov, May 17, 2007
    #9
  10. 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" <> wrote in message
    news:...
    > 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.
    >
    Alvin Bruney [MVP], May 18, 2007
    #10
  11. On May 18, 9:29 pm, "Alvin Bruney [MVP]" <some guy without an email
    address> wrote:
    > 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.
    Alexey Smirnov, May 18, 2007
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UGF0cmljay5PLklnZQ==?=

    DataSet paging vs Datareader paging

    =?Utf-8?B?UGF0cmljay5PLklnZQ==?=, Oct 8, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    10,661
    Scott Allen
    Oct 8, 2004
  2. Red
    Replies:
    1
    Views:
    708
  3. gnewsgroup
    Replies:
    0
    Views:
    361
    gnewsgroup
    Apr 10, 2008
  4. anil reddy
    Replies:
    0
    Views:
    919
    anil reddy
    Feb 11, 2009
  5. Ryan Liu
    Replies:
    0
    Views:
    123
    Ryan Liu
    Dec 12, 2008
Loading...

Share This Page