sites with download from sql

[jepatte]

I have a site where users can search voter records and then download results into a csv file. If the search is very large, ie. 150,000 records, obviously it takes a while to process (20-30 seconds or more). If someone else comes to the site at the same time and does a download, sometimes they timeout. I'm using ASP and SQL Server 2000, and I have the appropriate locks when opening and looping thru records.

So as a very small example, if my recordset has 3 records (A, B, C) and another person logs in and their recordset has (B, C, D) at the same time. Should they get a timeout?

 

[philhege]

When you say "appropriate locks", what TRANSACTION ISOLATION level are you using? Is the data subject to update while it's being queried?

The term "looping through records" concerns me. Is your procedure set-based?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[SQLSister]

I'm with Phil, records should never be looped through!

Check the execution plans on your queries, possibly the structure of your queries is inefficient and not properly using indexes.

You aren't using dynamic SQl are you or cursors?

"NOTHING is more important in a database than integrity." ESquared

 

[jepatte]

I have to loop thru the records, how else am I going to write them out to a csv file? All these tables are static, no updates are ever made. when I look at the execution plans, I see 5% here, 7% there, then 47% bookmark. I'm not quite sure if that's bad or not.

 

[philhege]

So you're looping in the app code, not the SQL procedure, correct? How large is the typical result set? (More importantly, do you know?)

The crux of the matter is to tune the procedure and underlying tables/indices.

What do you get when you run the Query Tuning Adviser against your procedure?

You can set your isolation level to READ UNCOMMITTED. This will take locking out of the equation.

150K records is not large. I think tuning will improve the performance.

 

[jepatte]

looping thru in the app code. It could 150K records. I've defragged my indexes by the way.

Here is a simple command that take a while.
Table: Voters
Fields Querying on: Race, Gender and Age (age is an int field that I will update nightly based on Birthdate, then rerun the index on it)

SQL: SELECT TOP 500 F1, F2, F3, etc FROM Voters WHERE Race = 'W' AND Age BETWEEN 18 and 25 AND Gender = 'M'

This of course might return 300,000 records but I display the first 500. This will take 2 minutes or more. I have an index on Gender, Age and Race.

 

[SQLDenis]

>>I have an index on Gender, Age and Race.

depends on your selectivity, for example
WHERE Race = 'W'
how many values does this have? With a selectivity less than .01% the index will be ignored

AND Gender = 'M'
this has only 2 possible values (sometimes 3 but that is another story) the index will be ignored here also because a scan is less costly since you will end up redaing half the table anyway

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jepatte]

So what you are saying is that since Race only has maybe 6 values out of 2.5 million records, indexes are pointless for this field and Gender??

 

[harebrain]

Now that you've had a chance to absorb the advice from the SQL gurus, let's ask the most germane question: are you using classic ASP?

If so, that's probably a bigger part of your problem than SQL Server. ASP 3.0 (and earlier) are execution dogs. Looping through 100 records would be slow; what you're doing is insane.

Clean-up your SQL, but resign yourself to this: the only way to get acceptable performance with your environment is to write an in-proc ActiveX server to process your looping logic.