Paging at the end of a large data set

I want to do paging with my VB.NET app using SQL Server. I have a
large table with over 5 million records. I've read numerous articles
on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
examples that they have provided. Performance is fine if you are
paging the "top" part of the data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best time that I can get in returning the page is 10+
seconds for each page.
How can I do this efficiently?Did you look at the methods offered at http://www.aspfaq.com/2120
?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||BTW, are you narrowing down your result set before you allow the user to
page through them, or is every paging operation performed on all 5 million
rows every time?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||> Did you look at the methods offered athttp://www.aspfaq.com/2120
Thanks for the response Aaron. I had not seen that article. However,
I do not believe these SQL Server examples help me much. For example,
in the author's SPs, he has the following code:
SELECT
@.rows = COUNT(*),
@.pages = COUNT(*) / @.perpage
FROM
SampleCDs WITH (NOLOCK)
That alone takes 9 seconds to run over my 5+ million records.
However, I had done some testing with ADODB and recordsets yesterday
and the performance was really good. So this article may help me with
that. I need to look at it some more.
I didn't want to use an ADODB solution. So I'm still looking for an
adequate SQL Server solution. Do you, or anyone else, know of any
others?|||> That alone takes 9 seconds to run over my 5+ million records.
What is the DDL for the table? Is there a clustered index?|||On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> BTW, are you narrowing down your result set before you allow the user to
> page through them, or is every paging operation performed on all 5 million
> rows every time?
Mike, thanks for your response. My goal is to do what you are
saying. I do not want to return all 5 million records. That takes
minutes. I want to create a SQL statement that returns a "page" of
records (page = 10 or 500). I can successfully do that. But like I
said, when I return records 5,000,001 through 5,000,010 it takes over
10 seconds. That is bad performance.
FYI, I am running in SQL Server 2005 and am ordering the records over
the Primary Key.|||> What is the DDL for the table? Is there a clustered index?
I apologize. I'm not a SQL Server expert and do not know what a DDL
is. Also, my table does have a clustered index. It is on the Primary
Key which is an Identity Field. There are other non-clustered indeces
also.|||Why can't you just do something like this to get to the end or bottom of the
dataset?
Select top 10 percent * from tblMyTable order by tblMyTable.MyColumn DESC?
Maybe you don't have a column that puts them in any order, but if you
didn't how would you know the bottom was always the bottom?
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171650295.295163.62480@.s48g2000cws.googlegroups.com...
>I want to do paging with my VB.NET app using SQL Server. I have a
> large table with over 5 million records. I've read numerous articles
> on how to page using TOP, ROW_NUMBER and OVER , etc. I've tried the
> examples that they have provided. Performance is fine if you are
> paging the "top" part of the data set.
> However, if I want to go to the last "page" of my data set, and
> traverse "backwards" though it, performance is terrible. In my tests,
> I have been returning 10 records a page. When I try to traverse
> backwards, the best time that I can get in returning the page is 10+
> seconds for each page.
> How can I do this efficiently?
>|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
Hi Paul,
What I was getting at is on the server side are your users paging through
all 5,000,000 rows or do you have some way to narrow it down beforehand.
For instance, if I wanted to page through a list of books, I might just want
the ones with titles that begin with "B". That would go a long way to
narrowing down my results from 5,000,000 from the start.
Also you said you are ordering these rows by PK. Is the PK the clustered
index as well?
BTW what type of data is it that you're paging through? Names, products,
...?
Thanks|||P.S. - SQL 2000 or 2005? With 2005 you could use ROW_NUMBER and to get a
better response time. Your best response though (I know I keep saying this)
would be if you could narrow the result set down before you start paging.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1171657612.904639.10350@.k78g2000cwa.googlegroups.com...
> On Feb 16, 1:30 pm, "Mike C#" <x...@.xyz.com> wrote:
>
> Mike, thanks for your response. My goal is to do what you are
> saying. I do not want to return all 5 million records. That takes
> minutes. I want to create a SQL statement that returns a "page" of
> records (page = 10 or 500). I can successfully do that. But like I
> said, when I return records 5,000,001 through 5,000,010 it takes over
> 10 seconds. That is bad performance.
> FYI, I am running in SQL Server 2005 and am ordering the records over
> the Primary Key.
>