SQL paging advice needed

OK, so saying that SQL Server isn't really my thing is kind of lame, but I'm not one to pretend I know everything. Generally I use a procedure like this to get paged data (parameters replaced with actual values for simplicity).

CREATE TABLE #PageIndex
(
 idnum int IDENTITY(1, 1),
 TopicID int
)

INSERT INTO #PageIndex (TopicID) SELECT TopicID FROM Topics
WHERE Topics.ForumID = 11
ORDER BY Topics.LastPostTime DESC

SELECT #PageIndex.idnum, Topics.* FROM #PageIndex JOIN Topics
ON #PageIndex.TopicID = Topics.TopicID
WHERE #PageIndex.idnum > 5000 AND #PageIndex.idnum < 5031
ORDER BY #PageIndex.idnum

This returns results quickly enough, but it seems to result in a bit of a CPU hit that I'm not entirely comfortable with. Is there a better way to go about this?

6 Comments

  • please use an in-memory temporary table. it is much faster. (declare table @blah (gfghfghf int, gfdfgfd bigint))



    you are using disk temp tables here

  • This appears a better alternative:



    DECLARE @Sort datetime

    SET ROWCOUNT 5001

    SELECT @SORT = LastPostTime FROM Topics ORDER BY LastPostTime DESC

    SET ROWCOUNT 30

    SELECT * FROM Topics WHERE LastPostTime &gt;= @Sort ORDER BY LastPostTime



    As mentioned in this article:

    http://codeproject.com/aspnet/paginglarge.asp



    The flaw is that it doesn't work right unless the sorting column is unique, but somehow I can't imagine that any two topics would be updated the same millisecond. I'll give the cursor method a shot too.

  • frans do you have any benchmarks, and can you define 'big'? And does it depend on the amount of RAM in your server.

  • YOu could speed up your query by definining a primarykey in your temp table. This will speed up the join operation in the second query because the optimizer will use the sorted unique ID column of your temp table for lookups. Maybe not that noticable with a pagesize of 30 but will be evident with larger page sizes.



    Also make sure that you have an index on the table to be paged for each kind of search path you use in your application. Use indexed views if possible.. Those will need more space in your database but will speed things up dramatically. But don't do this when you have very frequent inserts because all indexes and indexed views will have to be updated after each insert query.

  • It could also be important that the user is notified after an insert query has been run while the user is paging through the resultset of its original searchpattern.

  • When you use memory tables parallelism is disabled…so if you are working with large sets of data for example 10millions rows it is better to use #tables

Comments have been disabled for this content.