Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pwcphoto
Yak Posting Veteran
69 Posts |
 Posted - 2005-11-13 : 23:04:09
|
| I have asked similar questions in the past here about recordset paging through large tables. What I have seems to work but I am not sure if the speed at which it returns the information is normal or slow or even fast for what it is. Some insight would be helpful as to how this performance stacks up.I have a table that has about 10,000,000 rows of data. I page through the recordset in increments of 30 records per page. Of course as I get into the recordset it takes longer and longer for the results to come back. By the time I am at page 60,000 it is already up to 10 seconds to return the info. Here is a snippet of the stored procedure that does the work.SELECT @strFields FROM @strTables WHERE @strPK IN (SELECT TOP @strPageSize @strPK FROM @strTables WHERE @strPK NOT IN (SELECT TOP @strSkippedRows @strPK FROM @strTables WHERE @strFilterCriteria ORDER BY @strSortCriteria) WHERE @strFilterCriteria ORDER BY @strSortCriteria) WHERE @strFilterCriteria ORDER BY @strSortCriteriaObviously the above cannot work directly but it is a simplified flow of what I am doing in the stored procedure to page through the records.Is using the TOP function causing me performance? Is 10 seconds to return 30 records 60,000 pages into the table a reasonable time. The system is a dual P4 2.4ghz Xeon with 2gb of ram, MSSQL 2000, 2000 Server OS. We are only licensed for 2 processors would more be a big improvement? What of the 2gb limit in MSSQL 2000 is that 2gb just for SQL or can the machine have more but only dedicate 2gb to sqlserver? Help understanding the machine performance here would be greatly appreciated too.Not having prior experience with large tables such as this I have nothing to base it on. I really dont want to pre-generate 100,000 + html pages so that we have a rapid response, seems that would be kind of a brute force approach to the problem, not to mention a pain. Anyone have a better recordset paging approach that is faster? What do the big sites do?Thanks,Phil-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
|
|
angelesronald
Starting Member
4 Posts |
Posted - 2005-11-14 : 01:40:39
|
| I go for #6. It so simple. |
 |
|
|
pwcphoto
Yak Posting Veteran
69 Posts |
Posted - 2005-11-14 : 05:05:07
|
| "I go for #6. It so simple."What does that mean?-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
pwcphoto
Yak Posting Veteran
69 Posts |
Posted - 2005-11-14 : 06:56:54
|
| Well yes it is pagination, but it is more of a performance question, is this normal time for such a query. I don't have anything to go on other then giant sites like ebay that have millions of records returning things fairly quick. although to be fair they are not returning page 60,000 either, but they are a different animal all together so sites like that don't really apply here.I have been through many of the examples shown in the links you gave and tried them all many months ago. The sp I am using uses dynamic sql and it seems to work ok considering the size of the tables. I was just curious as to what kind of performance others get when dealing with large tables.Thanks for the infoPhilPhilPhil-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
|
|
|
|
|
|
|
|
Large recordset performance?