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