Returning smaller chunks of large result sets in SQL Server (verbose)

Posted on 2004-09-17
Last Modified: 2011-04-14

We have a web application that connects to SQL Server 2000.
Platform: ASP.Net/VB - SQL Svr 2000 - Windows 2000 Server.

We have a large, highly normalized database that has indexes where needed
and handles most queries in good form. However, we have recently come across
some limitations we did not anticipate in our Custom Query module.

Some of our queries are required to be executed using dynamic SQL, where the
query string is constructed based on approximately 10-15 parameters, chosen
by the user in the UI of the application. These parameters are passed to our
sprocs, which then construct the query strings and execute them.

At any rate, when our queries returned very large result sets, it was taking
the browser way too long to render the resultant datagrid. So, I implemented a
paging system in the UI that has been working fine until now, displaying 100
records at a time from a temporary table, with page navigation links near the

However, we have a client who has recently uploaded a rather large amount of
archival data from past years, and some of the result sets can reach 90,000
or so records.

What we are now experiencing is that the webserver is passing the parameters to
the SQL server, and it's taking somewhere on the order of 8 minutes for the SQL
server to respond.

I have an idea of how we might be able to get around it, and I wanted to ask what
you all think of it, and I invite any better ideas - we're running out of them.

Here's my idea:

1) If the result set will have a record count of more than say 4000 records, we go
ahead and respond to the UI with the usual output parameters for implementing the
paging system: temp table name, record count, et cetera. We do this when we reach
4000 records in the temp table, to keep the response time low. So, the first response
from SQL server will always be a record count of 4000 if the row count is greater.
(the user will see the first 100 records in a datagrid, "Page 1 of 40".)

2) Then we keep filling the temp table with the rest of the results.

3) As the user clicks on the paging links near the datagrid, I update the page count
to reflect the correct number of pages, based on a new set of output parameters from
SQL. (maybe on their second click it would now display, "Page 2 of 71".)

This amounts to a sort of paging system in SQL as well, I guess.


1) 88,000 records doesn't seem like very much to me, but I lack the experience to make
a judgement. Should we simply tell the user to narrow down their query to return
a smaller number of rows, or should we go forward with trying to make this work?

2) Is this do-able, or is there another better way to handle it?

3) Is it possible to use a trigger or function in SQL to make it respond after 4000 records
are reached in the result set's temp table? I guess we can't get a complete record count
until the entire result set has been copied to the temp table, which is what we're trying
to avoid already.

Thanks, this is a hard one, for us anyway. But if Google can do it, then so can we.
Question by:k3n51mm
LVL 15

Accepted Solution

jdlambert1 earned 500 total points
ID: 12090036
1. Automated processes can make use of zillions of records, but people? Manually evaluating each of 88k records? I seriously doubt anyone can make serious use of even 4,000 records that way. If they don't have a valid reason for needing so many records, then yeah, filter them.

2. You could use a sequential numeric column with an index to do your paging with a where clause on that column.

3. You can create a table that tracks table names and record counts and update it periodically. Here's some code based on a version from arbert that does it:

CREATE TABLE TableTracker (
name varchar(255),
numrows int,
reservedk varchar(50),
datak varchar(50),
index_size varchar(50),
unused varchar(50)

USE master
TRUNCATE TABLE DatabaseA.dbo.TableTracker
INSERT INTO DatabaseA.dbo.TableTracker
EXEC sp_msforeachtable "sp_spaceused '?'"

4. How Google does it:
LVL 12

Expert Comment

ID: 12090130
A few things to consider.
You can limit rows with set rowcount.

set rowcount 4000

select * from mytable (index =  col_to_order_by)
where p1 = @p1
and p2 = @p2
-- do not include an ORDER BY clause

set rowcount can accept a variable parameter if you want to.  Adding the index hint will prevent the creation of a temporary table and sorting of results. That might cause your query performance to be much worse though.  You can try it and find out.
LVL 17

Expert Comment

ID: 12091106
if it is taking 8 minutes to pull the records, chances are it will still be very slow no matter what restrictions you put on the # or rows returned, e.g. using rowcount, or a temp table. The delay is almost suredly in running the original SQL to find the records, not in presenting them across the network etc. Unless your table is absolutley huge (terabytes), or your query is exremely complex (joining lots of tables, in strange ways) 8 minutes sounds extremy long to return 90000 rows.
I think you should focus on improving the query speed by examining what indexes you ahve in place on your table.

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query help 3 24
SQL works but want to get the XML node data separately 11 26
Need help with another query 10 36
Related to SQL Query 5 19
Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question