469,332 Members | 6,678 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,332 developers. It's quick & easy.

ASP 3 timeouts when queries run fast in Query Analyzer

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

Feb 13 '06 #1
2 1411
je********@gmail.com wrote:
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/pro...comp.mspx#EEAA

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Feb 13 '06 #2
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.

Feb 14 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by wireless | last post: by
3 posts views Thread by George Homorozeanu | last post: by
2 posts views Thread by =?Utf-8?B?Q29ubnVsbA==?= | last post: by
4 posts views Thread by Akhenaten | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by haryvincent176 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.