Need Help: SP fast in QA, slow in APP

I have a strored procedure which takes .016 seconds in Query Analyzer,
but takes 16.8 seconds when run in my ASP.NET application. [yes, you rea
d
that right]
I've run profiler to find this info.
The machine has all the updates and service packs.
Where should I look next for an answer?
Thanks in advance.
Server:
Windows 2000 Server
SQL Server 2000 Standard
SP3I suspect that your problem might be related to parameter sniffing. Here
are a few links:
http://groups-beta.google.com/group...e4a2438bed08aca
http://groups-beta.google.com/group...eb556c8dfb6a82c
Keith
"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you r
ead
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
> Thanks in advance.
> Server:
> Windows 2000 Server
> SQL Server 2000 Standard
> SP3
>
>|||"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you r
ead
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
>
Also look at the SET settings used by ASP.NET. If you have any indexed
views or indexes on computed columns they cannot be used unless your
connection settings are correct.
Also when you say that it runs in .016 seconds in QA, how are you running
it? Just running the body of the stored procedure with hard-coded values is
not the same. You can always capture application's activity from Profiler
and replay it in QA.
David|||David:
Thanks for the response.
To answer your questions:
What I am doing is running Profiler,
1) watching the line " exec mySP_getMatch @.ClientID=2, @.thisOther=1 " on
Profiler, as the web app fires the sp.
2) Cutting and pasting the exact line that was run with ASP.NET into Query
Analyzer
3) Running the query. [returns a value immediately]
Also, the SP is just grabbing values from a table, with indexes where
necessary.
No views or computed columns.
What do you mean by the "SET" settings?
This one has me perplexed.
I've never had a case where performance what night and day when comparing a
query run in asp.net vs. query analyzer.
Thanks,
Rich
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uf2pAhvLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> "Rich Miller" <rooster575@.hotmail.com> wrote in message
> news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>
> Also look at the SET settings used by ASP.NET. If you have any indexed
> views or indexes on computed columns they cannot be used unless your
> connection settings are correct.
> Also when you say that it runs in .016 seconds in QA, how are you running
> it? Just running the body of the stored procedure with hard-coded values
> is not the same. You can always capture application's activity from
> Profiler and replay it in QA.
> David
>