Solved

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

Posted on 2004-09-17
3
420 Views
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
datagrid.

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.


QUESTIONS:

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.
0
Comment
Question by:k3n51mm
3 Comments
 
LVL 15

Accepted Solution

by:
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: http://www.search3w.com/Siteresources/data/MediaArchive/files/Google%2015000%20servers%20secrest.pdf
0
 
LVL 12

Expert Comment

by:kselvia
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.
0
 
LVL 17

Expert Comment

by:BillAn1
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now