ADO vs Query Analyser -- sql server clients

Narayana Vyas Kondreddi
11/28/2003 5:44:32 PM

Can you show us an example of the problematic query?

Also, how is your application connecting to SQL Server? Is it using ODBC
driver or OLE DB Provider?

You could also use Profiler to catch the execution plan of the query when
run from both application and Query Analyzer to identify where the plan is
going wrong.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm

[quoted text, click to view]
Hi,

Is there any known issue about the situation when the same query runs way
much slower via ADO than via isqlw?

Sometimes when I have a comlicated query (with joined subqueries etc) the
query runs very slow via ADO. When I spy in Profiler it tells me, say
3000000 reads, 29 sec.
When I copy the query from Profiler (!) into Query Analyser, it runs 0.01
sec and implements 18000 reads. Since I copy the query from the Profiler it
is guaranteed to be absolutely the same as the one ADO sends to the server.

It looks like ADO tells the server not to use indexes and implement full
scans.

Any ideas?

Regards,
Alexander

Alison Furlong
1/9/2004 10:11:37 AM

I have had a similar issue - I am running a fairly extensive query =
against a sizable database, for the purposes of upgrading it. This =
involves copying large amounts of data to temp tables, dropping and =
recreating views, and the like. The Delphi app we are using to run the =
query groups it into chunks using the GO statements, and then executes =
them in sequence using ADO and OLE DB.

When I run the full query in Query Analyzer, it takes 3-4 minutes. For =
the Delphi app, it takes 3-4 HOURS.=20

Anyone with any idea why there would be such an enormous difference in =
speed gets my undying gratitude.=20

Alison

[quoted text, click to view]

Erland Sommarskog
1/11/2004 10:44:23 PM

Alison Furlong (anonymous@discussions.microsoft.com) writes:
[quoted text, click to view]

Without access to the code or the database, it's difficult to tell. But
if you are using indexed views, it could be that some setting is missing
when you run from Delphi. If you are using ADO, you should have all the
required settings but one: SET ARITHABORT ON.

If that doesn't help, I would use the Profiler to see where the bottlenecks
may be.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Alison
1/12/2004 3:11:01 AM

Erland Sommarskog <sommar@algonet.se> wrote on Sun, 11 Jan 2004
22:44:23 +0000 (UTC):

[quoted text, click to view]

Nope, no indexed views. I'll give profiler a go, but I am confused as
to why profiler bottlenecks would not slow down Query Analyzer the
same way...

Alison
If a person who indulges in gluttony is a glutton, and a person who commits a felony is a felon, then God is an iron.
Or else He's the dumbest designer that ever lived. -S. Robinson

Erland Sommarskog
1/12/2004 10:53:56 PM

Alison (alison@latin-for-window-dot.com) writes:
[quoted text, click to view]

Performance issue are often very confusing until you actually find them.

I had a surprise today myself, when tracking down a problem in our
application at one of our customer sites. I could see that the execution
time for a stored procedure was taking longer and longer time, as the
loop progressed. (The loop is one big transaction.) I came to the
conclusion that for now I just had to accept the facts, and see if
I could create some repro case that I use in communication with Microsoft.

But there were a few more things that I thought I should investigate,
which lead me to a trigger which I fiddled around with. And suddenly
the execution time was linear! And one procedure where I saw the problem
was only selecting from the table with the trigger, not inserting or
updating it!

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Alison
1/14/2004 6:23:20 PM

Erland Sommarskog <sommar@algonet.se> wrote on Mon, 12 Jan 2004
22:53:56 +0000 (UTC):

[quoted text, click to view]

Well, I took your advice and ran the batch script through Query
Analyzer, and there was a series of queries that were collectively
accounting for over 90% of the load. After some condensation and
optimization of that group, the script still took around 4 minutes in
QA. Haven't been able to test it from Delphi yet, but that's next.

Alison
If a person who indulges in gluttony is a glutton, and a person who commits a felony is a felon, then God is an iron.
Or else He's the dumbest designer that ever lived. -S. Robinson

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clients

sql server clients : ADO vs Query Analyser