New Paging system for Forum v9.51

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic


   Hi 
I just wanted to know why did u add a TOP in your queries, I think this will decrease performance Its no need to use TOP coz WHERE already did what TOP is doing
like this


SELECT * FROM (SELECT TOP (@intPageSize * @intRecordPositionPageNum) my_table.*, ROW_NUMBER() OVER (ORDER BY my_table.id DESC) AS RowNum

FROM my_table WITH (NOLOCK)
WHERE my_table.id = 2) AS PagingQuery WHERE RowNum BETWEEN @intStartPosition + 1 AND @intStartPosition + @intPageSize


Following Query do the same and without TOP


SELECT * FROM (SELECT my_table.*, ROW_NUMBER() OVER (ORDER BY my_table.id DESC) AS RowNum

FROM my_table WITH (NOLOCK)

WHERE my_table.id = 2) AS PagingQuery WHERE RowNum BETWEEN @intStartPosition + 1 AND @intStartPosition + @intPageSize


Could u explain for me why did u use TOP in ur query
I just checked these in SQL 2005 and execution plan for second query (without TOP is better)
 
Thanks