We are working on ASP.Net Website with SQL Server 2005 as backend.
One of the issues we are trying to solve is that in one of the page, it is taking more time to load the data and browser got not-responding for reasonable time. Because the count of records we are fetching is very high and the grid which displays data is not having paging feature.
Table have primary key and indexes. We enabled paging for grid, so that we can optimize the data pulling process by fetching only those records for the page that we select in the grid.
For eg: If user click on Page 10 (grid is having Page Size 5), we need to fetch only 46th record to 50th record from database.
The query, what I have is
SELECT Description, Date
FROM (SELECT TOP 50 ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
WHERE Row >= 46 AND Row <= 50
But the disadvantage I felt is that it will fetch the top 50 records and then only it filter 5 records. So in the case of when I need to fetch 1000001th record to 1000005th record, it will fetch all the top 1000005 record and then filter the 5 records, which will cause an performance issue.
Could you correct me, by suggesting the query that could fetch only the records between startrecordno and endrecordno ?