A Stored Procedure runs slow while it's SQL is fast. RECOMPILE won't help

Hi all
I have a SP that beahves strange. Originally it takes about 20 milliseconds
to complete, but sometimes it starts going slow and take about 5-7 seconds.
When this happens, it keeps going slow.
I tried to run the SQL body of the SP in the Query analyzer, and it runs
fast (20 ms), while the SP takes 5-7 seconds
I've tried recompiling the procedure, as well as drop and create it again,
but it doesn't help.
Does anyone knows what can cause this and what is the soloution ?
TIA
Boaz Ben-Porat
Milestone SystemsFirst thing: Google for "Parameter sniffing", make sure you understand that
concept.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message news:OerL4M8ZGHA.3880@.TK2MSFTNGP04.phx
.gbl...
> Hi all
> I have a SP that beahves strange. Originally it takes about 20 millisecond
s
> to complete, but sometimes it starts going slow and take about 5-7 seconds
.
> When this happens, it keeps going slow.
> I tried to run the SQL body of the SP in the Query analyzer, and it runs
> fast (20 ms), while the SP takes 5-7 seconds
> I've tried recompiling the procedure, as well as drop and create it again,
> but it doesn't help.
> Does anyone knows what can cause this and what is the soloution ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>
>|||Could be parameter sniffing, can you show the code?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi Denis
Disabling parameter sniffing seems to work here. If it is still too slow
I'll send the code (which a bit messy). If not, I wouldn't waist your time.
Thanks
Boaz Be-Porat
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145899699.966141.109220@.y43g2000cwc.googlegroups.com...
> Could be parameter sniffing, can you show the code?
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

A query runs fast in Query analuser but slow in APplication

I am able to run a query which runs FAst in QA but slow in the
application.It takes about 16 m in QA but 1000 ms on the
Application.What I wanted to know is why would the query take a long
time in the application when it runs fast on SQL server?
How should we try debugging it?

AjayWhich application are you using? How many users are using that? You
have to consider these also. Did you use index?

Madhivanan|||AG (ajayz90@.hotmail.com) writes:
> I am able to run a query which runs FAst in QA but slow in the
> application.It takes about 16 m in QA but 1000 ms on the
> Application.What I wanted to know is why would the query take a long
> time in the application when it runs fast on SQL server?
> How should we try debugging it?

There are a number of possible causes. First of all, do you run the
exactly same query in Query Analyzer as from the application? That is,
if you use parameterised queries (and you should), you are not, as the
query will be embedded in sp_executesql. You can use the Profiler to
catch exactly what is being sent to SQL Server, cut and past into
Query Analyzer. If you run a parameterized query from the application,
and use hardcoded values or variables from QA, you don't have the same
presumptions.

However, maybe the most probable cause. is that when the query runs frm
QA, the optimizer makes use of an indexed view or an index on a computed
column. Such indexes can only be used if a number of SET options are
ON and one is OFF. If you are running a normal client API such as
ODCC, ADO, OLE DB or ADO .Net, all these settings are in the right
position, except one: ARITHABORT, which must be ON.

One way to quickly test this, is to run this command from Query Analyzer
"SET ARITHABORT OFF". If the query now runs slow, this indicates that
you should add SET ARITHABORT ON to the application.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

A query runs 1 times slower from a .NET application the from Query

Just a guess.
It might be the delay in creating and opening the connection.
Why don't you log the current time just before calling the SP and after it
and find the time difference. That can narrow down on what the issue is.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Boaz Ben-Porat" wrote:

> Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server
> database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest
> table in the database contains 11,000,000 records.
> Framework: .NET 2.0
> I try to run a query against the database, selecting aggregated data from
> views based on the large table.
> When executed from the Query Analizer, it takes 13 seconds.
> When executed from a .NET application, it takes 140 seconds.
> The database is well tuned (or else the query analizer would go slowly), s
o
> I can't find the reason for this difference.
> Any suggestion ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>
>Thanks for a quick answer.
The time I refer to is after the connection is opened.
the relevant code:
DbDataReader dr = null;
try
{
// This method opens a connection, if not allready opened
Connect();
// dbCommand is an input parameter of type DbCommand. It contains the SQL
statement
dbCommand.Connection = _connection;
DateTime t1 = DateTime.Now;
dr = dbCommand.ExecuteReader();
DateTime t2 = DateTime.Now;
TimeSpan ts = t2 - t1;
int milli = (int)ts.TotalMilliseconds; // milli contains the execution time
of dbCommand.ExecuteReader();
Boaz Ben-Porat
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:49A213DD-227B-4602-81ED-5ADF4E32687E@.microsoft.com...
> Just a guess.
> It might be the delay in creating and opening the connection.
> Why don't you log the current time just before calling the SP and after it
> and find the time difference. That can narrow down on what the issue is.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Boaz Ben-Porat" wrote:
>