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:
>> 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.
>|||Thanks to all who responded! I appreciate it.
First, I could use the ORDER BY DESC *IF* I only wanted to get the
last 10. But I will need to page backwards from the last page. To
get the second to last page I need to do something else. So this
solution is not acceptable.
Second, we will provide for the ability to filter. However, I want to
be able to figure this out. Microsoft Access can do it. If I open
the same table with the 5 million records, I can move to the bottom of
the data set and page backward. There is a lag going to the bottom,
but once there it pages with virtually no delay.
Third, I've used the ROW_NUMBER and results are not much different. I
tried every example that I could find. Nothing beats using the ADODB
recordset object. This doesn't make sense to me. I have to believe
that SQL Server can do this.
Ultimately what I am trying to do is create my own DataGridView and
use it in Virtual mode to be able to display tables with this many
records. So far I have not found anything that comes even close to
the performance Microsoft Access provides.|||One more thing...
Another option that I'm considering is the use of threading. Like MS
Access there would be a delay in getting the bottom 3-5 pages.
However as the person is paging through, a new thread is generated to
capture additional pages. I was trying to see if SQL Server could
work with better performance without having to use threading.
FYI...I have pasted below the SQL code that I used with the
ROW_NUMBER:
WITH A AS ( SELECT TrustID, ClientID, [DATE], Amount, ROW_NUMBER()
OVER (order by TrustID) AS RowNumber FROM tbl_TrustData )
SELECT *
FROM A
WHERE RowNumber between 5000001 and 5000010
TrustID is the PK and is a clustered index.|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1171986970.993291.134530@.k78g2000cwa.googlegroups.com...
> Thanks to all who responded! I appreciate it.
> First, I could use the ORDER BY DESC *IF* I only wanted to get the
> last 10. But I will need to page backwards from the last page. To
> get the second to last page I need to do something else. So this
> solution is not acceptable.
I think the idea there was to retrieve pages from the bottom up. For
instance, any pages in the back half (last 50% of the rows in your table)
might get some benefit from retrieving them from the back - although you'd
have to play with it to see if it helps your situation.
> Second, we will provide for the ability to filter. However, I want to
> be able to figure this out. Microsoft Access can do it. If I open
> the same table with the 5 million records, I can move to the bottom of
> the data set and page backward. There is a lag going to the bottom,
> but once there it pages with virtually no delay.
Microsoft Access is opening a cursor, iterating every single row (hence the
lag time you feel), and caching every single row in memory. If that's what
you want to replicate, then just retrieve every single row from SQL Server
into your client application and page client-side...
> Third, I've used the ROW_NUMBER and results are not much different. I
> tried every example that I could find. Nothing beats using the ADODB
> recordset object. This doesn't make sense to me. I have to believe
> that SQL Server can do this.
> Ultimately what I am trying to do is create my own DataGridView and
> use it in Virtual mode to be able to display tables with this many
> records. So far I have not found anything that comes even close to
> the performance Microsoft Access provides.
That "performance" comes from caching the entire dataset in memory
client-side, which you can easily replicate yourself if you really want to
read the entire dataset into memory client-side just to page it...|||Access cannot be loading every record into memory. When I view the
Process from the Task Manager, Access never goes over 50K (under the
"Mem Usage" column). Also, I've tried loading every record in memory
just to see what would happen and get an "Out of Memory" exception.
Is Access loading every record to some temporary file? I would not
think that would improve performance. I would guess that would make
it worse.
It sounds like I cannot get the performance that I want from SQL
Server retrieving any page, whether top or bottom, from a large
DataSet.|||It's using a fast-forward cursor to rip through the dataset (one of the
methods described on Aaron Bertrand's article on the subject at ASPFAQ). I
was exaggerating Access' caching mechanism (for effect), but Jet does cache
a lot of data client-side which, while it is one way to try to improve
performance, introduces a lot of complexity since you need to keep track of
who's updating what and all that good crap. If you want to try to emulate
Access, use a cursor.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1172098426.310438.17840@.j27g2000cwj.googlegroups.com...
> Access cannot be loading every record into memory. When I view the
> Process from the Task Manager, Access never goes over 50K (under the
> "Mem Usage" column). Also, I've tried loading every record in memory
> just to see what would happen and get an "Out of Memory" exception.
> Is Access loading every record to some temporary file? I would not
> think that would improve performance. I would guess that would make
> it worse.
> It sounds like I cannot get the performance that I want from SQL
> Server retrieving any page, whether top or bottom, from a large
> DataSet.
>|||Thanks Mike. That's the information that I was looking for. The
ASPFAQ article does not mention cursors. Is the Recordset object
using the cursor?|||"Paul" <pwh777@.hotmail.com> wrote in message
news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
> Thanks Mike. That's the information that I was looking for. The
> ASPFAQ article does not mention cursors. Is the Recordset object
> using the cursor?
I must have been drinking that night. I thought ASPFAQ had an article
comparing performance of different paging techniques; apparently not. I'll
keep looking for that article (maybe someone else remembers seeing it and
can provide a link?)
Depending on which technique you use, you may get a client-side cursor
automatically, and it may even be backed up by a server-side cursor. (See
SqlDataReader). Old ADO was famous for its use of cursors to get at the
data, although its been a while so I'd have to look up the Recordset object
specs to find out for sure in that code. I would suspect that it is using a
client-side cursor, at least.
Here's a couple more links for you:
http://www.4guysfromrolla.com/webtech/042606-1.shtml
http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
Google up some "SQL Server paging speed" or some such... This problem has
been attacked by a lot of people all over the place through the years, so
there's a lot of good info. out there on it.|||Thanks Mike! You've been very helpful. I will look through these.|||> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques;
It does;
http://www.aspfaq.com/2120
A|||Not sure if this is relevant but I did a blog post a while ago about paging
that had some references:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/20/580353.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike C#" <xyz@.xyz.com> wrote in message
news:O04M$i3VHHA.488@.TK2MSFTNGP06.phx.gbl...
> "Paul" <pwh777@.hotmail.com> wrote in message
> news:1172253270.739022.146740@.8g2000cwh.googlegroups.com...
>> Thanks Mike. That's the information that I was looking for. The
>> ASPFAQ article does not mention cursors. Is the Recordset object
>> using the cursor?
> I must have been drinking that night. I thought ASPFAQ had an article
> comparing performance of different paging techniques; apparently not.
> I'll keep looking for that article (maybe someone else remembers seeing it
> and can provide a link?)
> Depending on which technique you use, you may get a client-side cursor
> automatically, and it may even be backed up by a server-side cursor. (See
> SqlDataReader). Old ADO was famous for its use of cursors to get at the
> data, although its been a while so I'd have to look up the Recordset
> object specs to find out for sure in that code. I would suspect that it
> is using a client-side cursor, at least.
> Here's a couple more links for you:
> http://www.4guysfromrolla.com/webtech/042606-1.shtml
> http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
> Google up some "SQL Server paging speed" or some such... This problem has
> been attacked by a lot of people all over the place through the years, so
> there's a lot of good info. out there on it.
>|||For some reason, the data at the end of the article (including the important
summary of performance comparisons) shows up blank. You can get it if you
download the PDF of all articles from http://www.aspfaq.com/downloads.asp
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
> A
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eTQ%23493VHHA.5060@.TK2MSFTNGP06.phx.gbl...
>> I must have been drinking that night. I thought ASPFAQ had an article
>> comparing performance of different paging techniques;
> It does;
> http://www.aspfaq.com/2120
>
I thought your article also showed performance differences between
server-side paging techniques like using dynamic SQL, cursors, temp tables,
etc. in a side-by-side format. I'm probably confusing your article with
another one I've seen somewhere, but I'll be darned if I'm able to find it
now. Or it might have just been a heavy night of drinking :)|||>> It does;
>> http://www.aspfaq.com/2120
> I thought your article also showed performance differences between
> server-side paging techniques like using dynamic SQL, cursors, temp
> tables, etc. in a side-by-side format.
It did (see my follow-up).
I talked to the site owners and they were supposed to have fixed it today,
but haven't yet.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OlVBKX9VHHA.392@.TK2MSFTNGP06.phx.gbl...
> It did (see my follow-up).
> I talked to the site owners and they were supposed to have fixed it today,
> but haven't yet.
I thought you owned it? What's going on 'round here? :)|||>> I talked to the site owners and they were supposed to have fixed it
>> today, but haven't yet.
> I thought you owned it? What's going on 'round here? :)
No, I handed it over to new owners last year.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23gwTXeOWHHA.3568@.TK2MSFTNGP06.phx.gbl...
> No, I handed it over to new owners last year.
Well that's a kick in the pants! Hope you made a killin' :)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||>> No, I handed it over to new owners last year.
> Well that's a kick in the pants! Hope you made a killin' :)
Sure, but I'll be paying dearly for it on April 15th. :-)|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23A7bClqWHHA.3332@.TK2MSFTNGP04.phx.gbl...
> Sure, but I'll be paying dearly for it on April 15th. :-)
Sounds like it's time for a trip to Vegas... They'll help you if you have
too much money :)