ASP 3 timeouts when queries run fast in Query Analyzer

[jeffguevin]

Hi all,

First post here, please let me know if I've missed any protocols.

I have a strange problem, where an ASP page that calls a few stored
procedures takes very long to run, and in fact times out. Each of the
procs that are called takes several minutes to run through IIS, but if
I run them with the same parameters in Query Analyzer, they only take a
few seconds. What's more, this same page works fine when pointed at
other databases with the same tables, indexes, and similar (or more)
amounts of data.

I'm using Windows 2003 Standard, with ASP 3. Connection is directly
through ADO in ASP. Database is MSSQL2000 on Windows 2003. Everything
else on the server, and everything else in the database in question,
runs fine when accessed with ASP.

I know I haven't included a lot of details about the queries / tables /
indexes themselves, because I feel I've eliminated the possible issues
there. But I'm stumped--what could cause a single page to run slowly
with one database, and not any others, when the queries themselves run
fine with other interfaces?

I have not rebooted my servers, and it will be several days before I
can reasonably do that, so I'm hopeful you can help me find a "real"
solution!

Thanks,

Jeff

 

[Bob Barrows [MVP]]

Hi all,

First post here, please let me know if I've missed any protocols.

I have a strange problem, where an ASP page that calls a few stored
procedures takes very long to run, and in fact times out. Each of the
procs that are called takes several minutes to run through IIS, but if
I run them with the same parameters in Query Analyzer, they only take
a few seconds. What's more, this same page works fine when pointed at
other databases with the same tables, indexes, and similar (or more)
amounts of data.

Check out what is going on with SQL Profile. Add the Execution Plan events
to the trace so you can see what is different between asp and query
analyzer.

I suspect you are running into a behavior called "parameter sniffing". See:
This blurb from Bart Duncan may be of interest to you:
http://tinyurl.com/h7aa

Also, take a look at this excellent whitepaper.
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#EEAA

 

[jeffguevin]

Brilliant! Thanks, Bob, that was exactly the issue, and I never would
have stumbled on that myself. The fix I implemented was to replace add
some local variables in the proc, set them equal to the input
parameters, and then reference the variables in the body of the proc
instead of the params.