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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-04 : 20:15:43
|
| Kyle Heon writes " Hello! I have an issue which I'm struggling to help a collegue solve. The project requires a paged view of millions of records of data. I've tried the various "paging stored procedure" examples that I have found scattered around (this site included) but the performance is unacceptable -- in fact, never has the procedure completed (cancelled after 2 minutes by myself). This is a web application, and a 2 minute delay is not a possibility. What can you suggest as an alternative to the *temp table* solutions that are used so frequently? Is there a way to, without moving data into temp tables set the beginning and ending row in T-SQL? Please help. Thanks in advance." |
|
|
KHeon
Posting Yak Master
135 Posts |
Posted - 2002-04-17 : 08:21:29
|
| I have had to revisit this same need for another client and have been fairly successful with implementing the temp table solution using SQL Server 2000. I have applied a clustered index to the table which is most important, create a temp table that does the "RowNum" functionality and stores the "ID" field for the main table. I then join the temp table back to the main table for only the records that fall between the RowNum's that I need. This works pretty well, even for 1-2 million records but the performance is "front-loaded" in that I determine the max number of rows to return based on the current page and page size which means that as you move up the dataset (in pages) the performance degrades. Currently I have tested this solution with 1.5 million records, and was able to retrieve the last page via a webpage.I agree, while the need to work with that much data is often ludicrous, the nature of business for our client unfortunately dictates it, at least in the design of this program, it is ultimately a full listing of everything (usefulness is undetermined as this is a prototype project).I have also spent somewhere in the vacinity of 40 hours trying to find a better way. A way to simulate Oracle's "RowNum" functionality in SQL Server w/out having to use temporary tables. I fear that as system load increases the tempdb log files will become enormous which could cause problems.Oh, if you are interested, with optimization I have can query the last page of the 1.5 million record dataset in 11 seconds through QA, and it builds in about 12 on the webpage (50 records per page). The system is a beast though, spec'd out accordingly (Dual 1.4's, 1GB RAM, RAID 5 15k rpm drives for data, RAID 1 15k drives for transaction logs).I explored derived tables, correlated subqueries, and even user-defined functions to try and built a function. I was hoping that with the UDF I could pass in a value, have the function increment it and return it (inside a SELECT) statement) which would simulate the functionality of IDENTITY but alas, functions are one-way, you pass in, it returns, scope of the variable change inside the functions are not visible outside the function.Any suggestions? Anyone? :)I thank you for your time.Kyle HeonPixelMEDIA, Inc.kheon@pixelmedia.comEdited by - KHeon on 04/17/2002 08:21:59 |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
|
|
Paging Millions of Records