473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

A query runs fast in Query analuser but slow in APplication

AG
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?
Ajay

Jul 23 '05 #1
2 6201
Which application are you using? How many users are using that? You
have to consider these also. Did you use index?

Madhivanan

Jul 23 '05 #2
AG (aj*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Bruce D | last post by:
Could someone please help to explain why the following query isn't using the index... explain select id from kbm where state = 'MA' table type possible_keys key key_len ref rows Extra...
7
by: Thomi Baechler | last post by:
Hello Everybody I run the following query against to identical databases. Execution time on the first DB is 0 seconds, on the other 6 seconds! SELECT dbo.HRMABZ.EMPKEY ,...
3
by: krystoffff | last post by:
Hi I would like to paginate the results of a query on several pages. So I use a query with a limit X offset Y to display X results on a page, ok. But for the first page, I need to run the...
7
by: DFS | last post by:
This UNION query is very slow. With only 3,000 records in the Parent table and 7,000 records in the Child table, it takes about 60 seconds to run and returns about 2200 records. Access 97. All...
21
by: CSN | last post by:
I have a pretty simple select query that joins a table (p) with 125K rows with another table (pc) with almost one million rows: select p.* from product_categories pc inner join products p on...
0
by: Rog | last post by:
I have two append queries that each append records from an SQL table into a temporary Access table in the frontend. All of a sudden one of them has become very slow - it takes a minute before the...
4
by: shawno | last post by:
Hello, We are currently in the process of migrating our databases from a relic of a server to a new 4 processor dual-core box with 4 gigs of RAM. Overall, database performance is obviously...
29
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where...
8
by: SaltyBoat | last post by:
Needing to import and parse data from a large PDF file into an Access 2002 table: I start by converted the PDF file to a html file. Then I read this html text file, line by line, into a table...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.