Hi all gurus.
We have an app using SQL Server 2000 (PC Side) and SQL Server CE 2.0 (PPC
Side); now I'm guessing what effort should be necessary to port the app on
PalmOS. We have used 4 PalmOS development CASL, but now we're having a look
into AppForge. However, I'd prefer not to move from SQL Svr to Sybase
iAnywhere, alt least on PC side. Do you have some experience in doing so?
Can some1 point me to the right direction? TIA
I guess this is kind of an OT. However, I wasn't able to find an MS group
related to Palm. So I posted this question on this sqlserver.replication and
..clients
try here.
http://www.palmos.com/dev/support/forums/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Saverio Tedeschi" <tesis@.tesis.org> wrote in message
news:O8LmKZOZFHA.1536@.TK2MSFTNGP10.phx.gbl...
> Hi all gurus.
> We have an app using SQL Server 2000 (PC Side) and SQL Server CE 2.0 (PPC
> Side); now I'm guessing what effort should be necessary to port the app on
> PalmOS. We have used 4 PalmOS development CASL, but now we're having a
look
> into AppForge. However, I'd prefer not to move from SQL Svr to Sybase
> iAnywhere, alt least on PC side. Do you have some experience in doing so?
> Can some1 point me to the right direction? TIA
> I guess this is kind of an OT. However, I wasn't able to find an MS group
> related to Palm. So I posted this question on this sqlserver.replication
and
> .clients
>
|||Thank you Hilary, for your suggestion.
Actually, as I've said, we already use SQL replication between CE
devices and SQL Server 2000, and now we need to sync with PalmOS
devices. I need to be pointed to the best/more affordable method. Have a
nice day.
P.S.We share the same time we are on IT business (20 years) and the same
number of childrens (5). Of course, I'm not MVP nor have your expertise,
but I'm trying to follow you :-) Have a nice day.
*** Sent via Developersdex http://www.codecomments.com ***
|||You should be able to use Appforge's Universal Conduit to sync against
a MSSQL Srv. If you need something more fancy you either need to switch
to iAnywhere, create your own sync or try the new DataSync from AF.
Join the Appforge forum (forum.appforge.com) to get more infos
|||Good luck Tesis. Give ur kids a hug from me.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"tesis" <nospam@.devdex.com> wrote in message
news:eQUJtGRZFHA.1152@.tk2msftngp13.phx.gbl...
> Thank you Hilary, for your suggestion.
> Actually, as I've said, we already use SQL replication between CE
> devices and SQL Server 2000, and now we need to sync with PalmOS
> devices. I need to be pointed to the best/more affordable method. Have a
> nice day.
> P.S.We share the same time we are on IT business (20 years) and the same
> number of childrens (5). Of course, I'm not MVP nor have your expertise,
> but I'm trying to follow you :-) Have a nice day.
> *** Sent via Developersdex http://www.codecomments.com ***
Showing posts with label app. Show all posts
Showing posts with label app. Show all posts
Tuesday, March 20, 2012
Paging, Performance and ADODB
I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, 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 I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, 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 I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
Paging, Performance and ADODB
I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, 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 I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, 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 I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
Paging, Performance and ADODB
I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, 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 I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?
Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.
|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, 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 I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?
Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.
|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.
Monday, March 12, 2012
Paging large result sets
I use SqlServer on my web app written in Java. I have went through quite a big effort in trying to realize a flexible paging algorithm that I can include in my set of libraries. However having to deal with too many unknows like do I want the RS ascending/descending, what is the first/last itemid, next/previous page (and then invert logic what is up what is down) I'm quickly going mand. I realise there is a fine line where this is a tsql problem (like, do I use TOP x or not, how do I create all varying parameters on which I'd like to filter on etc.) and architecting java code with DAO's and a matter of how to put all of this together, so in order to avoid this, can you suggest how should a general SQL query be to allow this kind of behaviour? I know that stored procedures did not allow TOP $var in SqlServer2k is this still the case? Should SQL allow for constructs that would allow easy paging?Hello!
Yes, paging is a missed feature in SQL Server and it is still missed with version 2005. I do not understand why MS does not work on a construct to allow querying a sliced result. although, there is a known possibility for efficient server side paging, working with some millions of records, if adequate indexes are existent. This solution is based on nested "select top x" statements. You can find a good description at ....
http://weblogs.asp.net/pwilson/archive/2003/10/10/31456.aspx
But i prefer my one extended version which additionally is based on a primary key, which allways allows me to find the exact next or previous record.
Regards,
Tom|||Hi,
I like this stored procedure, although there can be infinite solutions to this problem I've marked it as a correct answer. It even addresses the problem that you can't pass the TOP x where x is a parameter of the procedure. Even the comments are insightfull.
Good work ;)
PS. I somehow wonder if the Command and Builder pattern might be used as well on the server side. Any ideas or links?|||
There are lot of problems with the stored procedure from the link above. Here are some of the issues:
1. It doesn't protect you from SQL injection attack
2. Use of EXEC instead of sp_executesql. The later can produce cacheable plans for the dynamic SQL statement
3. SP returns multiple resultsets which requires more work from client-side to handle. It is best to avoid it unless necessary
Having said this, the stored procedure does demonstrate one technique to page resultsets in SQL Server 2000. And if you incorporate such technique, please make sure to protect against the problems mentioned above.
You may also want to consider avoiding paging of resultsets in the GUI. You can provide like a search and locate type of functionality which will result in less number of rows being pulled from the server to client. This might also provide a better user experience than going through 100 rows at a time to find the one of interest. These type of paging techniques typically produce more load on the server since you are processing more rows in every query to locate the ones of interest. In any case, if you still have a requirement to do paging of the resultset then a dynamic query using TOP is the best way to go in SQL Server 2000.
In SQL Server 2005, you can also use the ROW_NUMBER() function to do this slightly more efficiently. ROW_NUMBER function allows you to generate a sequential number for the each row in a resultset and you can apply filters on it to perform the paging. We are working on a white paper that will compare the various paging techniques that should appear in the Microsoft SQL Server and MSDN web sites.
Friday, March 9, 2012
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.googlegro ups.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.googlegro ups.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.googlegro ups.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.googlegro ups.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.googlegro ups.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.
>
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.googlegro ups.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.googlegro ups.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.googlegro ups.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.googlegro ups.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.googlegro ups.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.
>
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.
>
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.
>
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 :)
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 :)
Pagination of data
Hi
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
RegardsTake a look at this article.
http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
--|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
David Portas
SQL Server MVP
--|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>|||Check out:
http://www.aspfaq.com/show.asp?id=2120
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
RegardsTake a look at this article.
http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
--|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
David Portas
SQL Server MVP
--|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>|||Check out:
http://www.aspfaq.com/show.asp?id=2120
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
Pagination of data
Hi
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
RegardsTake a look at this article.
http://www.aspfaq.com/show.asp?id=2120
--
David Portas
SQL Server MVP
--|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
--
David Portas
SQL Server MVP
--|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>|||Check out:
http://www.aspfaq.com/show.asp?id=2120
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
RegardsTake a look at this article.
http://www.aspfaq.com/show.asp?id=2120
--
David Portas
SQL Server MVP
--|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
--
David Portas
SQL Server MVP
--|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>|||Check out:
http://www.aspfaq.com/show.asp?id=2120
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
Pagination of data
Hi
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
Regards
Take a look at this article.
http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>
|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
David Portas
SQL Server MVP
|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
|||Check out:
http://www.aspfaq.com/show.asp?id=2120
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
Regards
Take a look at this article.
http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>
|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
David Portas
SQL Server MVP
|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
|||Check out:
http://www.aspfaq.com/show.asp?id=2120
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
Subscribe to:
Posts (Atom)