What is the best solution to retrieve large recordsets - more than 3000 rows

Question

I have a SQL Server table with 3000 rows in it. When I retrieve those rows it is taking time using a Select Statement. What is the best solution to retrieve them?

Answer 1

It is essential to port your SQL query here for this question but assuming simple select statement my answers would be

1) First select the limited number of columns that are required. Don't use Select *. Use specific columns if all columns are not required in your desired output

2) If your select statement has a filter then use the filter in such an order that it does the minimum number of operations and gets the optimum result (if you post SQL statements then I can surely help on this)

3) Create an index for the specific field that will also help to improve your query performance

Hope this helps

Answer 2

Since you don't want to show all 3000 records at one time, use paging in your SQL statement. Here is an example using the AdventureWorks database in SQL Server. Assuming each of your webpage shows 25 records, this statement will get all records required in the 5th page. The "QueryResults" is a Common Table Expression (CTE) and I only get the primary keys to keep the CTE small in case you had millions of records. Afterwards, I join the QueryResult (CTE) to the main table (Product) and get any columns I need. @PageNumber below is the current page number. Perform your "WHERE" and sort statements within the CTE.

DECLARE @PageNumber int, @PageSize int;
SET @PageSize = 25;
SET @PageNumber = 5;

; WITH QueryResults AS
(
    SELECT TOP (@PageSize * @PageNumber) ROW_NUMBER() OVER (ORDER BY ProductID) AS ROW,
    P.ProductID
    FROM Production.Product P WITH (NOLOCK)
)
SELECT QR.ROW, QR.ProductID, P.Name
FROM QueryResults QR WITH (NOLOCK)
INNER JOIN Production.Product P WITH (NOLOCK) ON QR.ProductID = P.ProductID
WHERE ROW BETWEEN (((@PageNumber - 1) * @PageSize) + 1) AND (@PageSize * @PageNumber)
ORDER BY QR.ROW ASC

Answer 3

3000 records is not a big deal for sql server 2008 you just need to:- 1>avoid * in a select statement. 2>proper indexing is needed ,you my try include coloum 3>try to use index on primary as well as foregin coloum.

and you can also try query in different way as same query can be written in different way and the compare both queary cost and setting time statistics on.