Hallo everybody,
The question sounds very simple, but as I think it's not!
I searched about this subject a lot and I found the following approaches,
and I really don't know which is the best or at least when to use each
approach.
The first one and most used (as I noticed ) is to copy all records into a
temporary table with (clustered) index and then selecting the records from
the new table with using the new index to specify which records have to be
retrieved. As example this I copied query with some modifications from
http://www.4guysfromrolla.com/webtech/062899-1.shtml CREATE TABLE #TempItems
(
ID int IDENTITY,
Name varchar(50),
Price currency
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT Name , Price
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
The second approach is very similar to this one.
It has been used in "Community Server Forums" the asp.net based forum.
http://www.asp.net/Default.aspx?tabindex=6&tabid=41 In this approach you create a temporary table with just two columns,
first one the (new) index and the second is a numeric column,
then you copy only the IDs from the original table into this new table.
Then you select the IDs from this temporary table like in the first approach
and join the result to the original table.
Here I can't judge witch one is faster; to copy all records to temporary
table and then select or to copy just the ids and then perform a join
operation, any idea?
The third one I found it by myself ;-)
And it is
Select top @RecsPerPage * from mytable where id not in (select top
((@Page - 1) * @RecsPerPage ) id from my table)
Of course this is not a sproc because you can't pass parameters to top.
I have always used "set @@rowcount = @RecsPerPage" where I wanted to limit
the retrieved rows dynamically,
but I don't know how to use it here in the main and sub query at same time.
Another approach will work only on MSSQL 2005,
I found it in this article
http://msdn.microsoft.com/sql/archive/default.aspx?pull=/library/en-us/dnsql90/html/sql_05tsqlenhance.asp.
It simply uses the new introduced function Row_Number(), for example
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
Anyway I need a solution for sql 2000 and not 2005,
but I mentioned this approach because I read a interesting advice from the
author of that article.
Hr said
"For more efficient paging when the user might repeatedly request different
pages,
first populate a temporary table with all of the base table rows including
calculated row numbers and index the column containing the row numbers"
I think it makes no difference if you populated the table with the
calculated row number or just you created an identity column.
But I didn't understand how temporary table will be more efficient if the
user repeatedly request different pages.
I thought a temporary table exists only as long as the connection with the
database exists,
and will immediately be deleted when the connection closes. Is that right?
So please can anybody help to find which approach has the best performance,
or at least when to use which approach?
Any help will be appreciated.
Sorry for this long post and for my English
Stefan
