Just a quick little one here. When doing Row_Number() paging of data, it's advantageous to include the TOP() function in the sql as well, as we 'know' the depth of the data that we are going to be querying. So, if we look at the query in the DAL\SQLClient\SqlForumsProvider\GetThreads method, we see the following query:

string sql = string.Format(@"SELECT * FROM
(
SELECT tbh_Posts.PostID, tbh_Posts.AddedDate, tbh_Posts.AddedBy, tbh_Posts.AddedByIP,
tbh_Posts.ForumID, tbh_Posts.ParentPostID, tbh_Posts.Title, tbh_Posts.Approved,
tbh_Posts.Closed, tbh_Posts.ViewCount, tbh_Posts.ReplyCount, tbh_Posts.LastPostDate,
tbh_Posts.LastPostBy, tbh_Forums.Title AS ForumTitle, tbh_Posts.Priority, 0 as Rating,
     ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum
     FROM tbh_Posts INNER JOIN tbh_Forums ON tbh_Posts.ForumID = tbh_Forums.ForumID
     WHERE ParentPostID = 0 AND Approved = 1
) ForumThreads
WHERE ForumThreads.RowNum BETWEEN {1} AND {2}
ORDER BY RowNum ASC", sortExpression, lowerBound, upperBound);

This can be changed to (new addition in RED):

string sql = string.Format(@"SELECT * FROM
(
SELECT TOP {2} tbh_Posts.PostID, tbh_Posts.AddedDate, tbh_Posts.AddedBy, tbh_Posts.AddedByIP,
tbh_Posts.ForumID, tbh_Posts.ParentPostID, tbh_Posts.Title, tbh_Posts.Approved,
tbh_Posts.Closed, tbh_Posts.ViewCount, tbh_Posts.ReplyCount, tbh_Posts.LastPostDate,
tbh_Posts.LastPostBy, tbh_Forums.Title AS ForumTitle, tbh_Posts.Priority, 0 as Rating,
     ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum
     FROM tbh_Posts INNER JOIN tbh_Forums ON tbh_Posts.ForumID = tbh_Forums.ForumID
     WHERE ParentPostID = 0 AND Approved = 1
) ForumThreads
WHERE ForumThreads.RowNum BETWEEN {1} AND {2}
ORDER BY RowNum ASC", sortExpression, lowerBound, upperBound);

The format function uses param {2} and plugs in the maximum rows to be queried. THe same can be done in any of the stored procedures. For example, the tbh_Articles_GetArticles would have the following line amended:

SELECT * FROM
(
    SELECT tbh_Articles.ArticleID,
// ..etc...

to:

SELECT * FROM
(
    SELECT TOP ((@PageIndex+1)*@PageSize) tbh_Articles.ArticleID,
// ..etc...

Hope this is helpful. Obviously, in small datasets (of under a few thousand rows), this effect is negligible. In large datasets, the benefits are more profound.




jimi

http://www.jamestollan.com

__________________
jimi

http://www.originaltalent.com