Fetch Custom Range of Records (eg: 10000th record to 10010th record)

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)
            AS LogWithRowNumbers
WHERE  Row >= 46 AND Row <= 50

Open in new window


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 ?
LVL 23
Rajkumar GsSoftware EngineerAsked:
Who is Participating?
 
Dhugal_LCommented:
SQL Server should optimise the query when using a CTE. It doesn't "Load" the entire result set.

If you put your where clause on the inner query you will be filtering the results then paging them which is what you require.

This is THE means of paging in SQL Server....

E.G.

DECLARE @Start INT
DECLARE @End INT
DECLARE @SearchParameter NVARCHAR(256)

-- Set the start/end parameters as required.
SET @Start	= 100000;
SET @End	= 100003;

-- Provide some search parameters:
SET @SearchParameter = 'Example';

WITH Paged_LogWithRowNumbers AS
(
	SELECT 
			ROW_NUMBER() OVER(
				ORDER BY [Date] DESC
			) AS [Row],
			[Description], 
			[Date]
	FROM	dbo.[LOG]
        WHERE    --- Your Filter...
                        [Description] LIKE '%'  + @SearchParameter + '%'
)
SELECT 
		[Row],
		[Description],
		[Date]
FROM	Paged_LogWithRowNumbers
WHERE 
		[Row] > @Start
		AND 
		[Row] < @End

Open in new window

0
 
Dhugal_LCommented:
I would recomend using a CTE over a derived table. Try something like this:

DECLARE @Start INT
DECLARE @End INT

-- Set the start/end parameters as required.
SET @Start	= 100000;
SET @End	= 100003;

WITH Paged_LogWithRowNumbers AS
(
	SELECT 
			ROW_NUMBER() OVER(
				ORDER BY [Date] DESC
			) AS [Row],
			[Description], 
			[Date]
	FROM	dbo.[LOG]
)
SELECT 
		[Row],
		[Description],
		[Date]
FROM	Paged_LogWithRowNumbers
WHERE 
		[Row] > @Start
		AND 
		[Row] < @End

Open in new window


0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Dhugal,
You query have removed 'TOP'. Again CTE query loads entire records from the table and then filter, right ?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Alpesh PatelAssistant ConsultantCommented:
Select * From (
Select *, ROW_NUMBER() Over (Order by ProductID) AS RN from Products ) B
Where RN > 10 And RN < 20
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Thank you guys for your suggestion.

I think you didn't read my question completely. The disadvantage of this query I mentioned there.

The sub-query
Select *, ROW_NUMBER() Over (Order by ProductID) AS RN from Products 

Open in new window

fetches all the records from table, then filter based on WHERE conditon.
For tables having huge data, there will be performance issues.

I am looking for a query, which can effectively load only the required records.

Your suggestion ?
Raj

0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Thanks Dhugal,
This is what I expected. I didn't thought about the advantages of CTE when used with ROW_NUMBER()

From this link - http://www.codeproject.com/KB/database/PagingResults.aspx, this Stored Procedure seems to be good example
CREATE PROC GetCustomersByPage

@PageSize int, @PageNumber int 

AS 

Declare @RowStart int 
Declare @RowEnd int 

if @PageNumber > 0 
Begin 

SET @PageNumber = @PageNumber -1 

SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 
     ( SELECT CustomerID, CompanyName, 
       ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
       FROM Customers ) 

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end

END
GO

Open in new window

0
 
Rajkumar GsSoftware EngineerAuthor Commented:
I have a table which is having 3,10,97,310 records. Let me test against this table's data and will post back

Raj
0
 
prajapati84Commented:
Really great and can help you, try once!

Article: A More Efficient Method for Paging Through Large Result Sets
URL: http://www.4guysfromrolla.com/webtech/042606-1.shtml
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Thanks Prajapati for the article - I will post back after reading this article.

Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
Thanks for the articles and suggestions. Hope they are the good solutions for paging.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.