HELP serious performance degradation compared to QA

[Bob]

I have a stored procedure that takes 4 seconds to execute (returning 3840 rows)
from Query Analyser, yet when I fill it using the following (assume the variable
SQL is the stored procedure)

Dim cmd As SqlCommand
Dim ds As New DataSet
Dim sa As SqlDataAdapter
cmd = New SqlCommand(SQL, cn)
sa = New SqlDataAdapter(cmd)
sa.Fill(ds)

it times out or takes much, much longer than 4 seconds - on the order of a
minute. This delay has a lot of corresponding server processor activity but
absolutely none for the client.

I've read some previous posts about timeout issues, but none of them explain
this performnace degradation. All I want is the data, and no extra overhead. Is
there any way in .Net I can get the *same* performance that I get from QA?

The same problem happens in VB6 using recordsets - the 4 second query takes 62
seconds.

I already looked at the DataSetSurrogate and it performance is worse.
http://support.microsoft.com/?id=829740

DataReader shows the same behavior/problem, only it times out at 30 seconds and
reports "system error".

Someone please help.

TIA,
Bob

 

[Miha Markic]

Hi Bob,

You have to understand that Query Analyzer shows you only first few rows of
data, while Fill fetches all of them.
So the difference is probably in traffic between server and client.

 

[Miha Markic]

No, I don't think it does.
It lets you scroll though. Try scrolling to end.

 

[Bob]

You are wrong. Query analyzer returns all rows. It is Enterprise Manager's table
viewing tool that returns only a few.

 

[Bob]

Of course EM lets you scroll through. It has a special cursor operating in what
they call 'firehose' mode, which I will not bother explaining here because it's
not relevant to my question.

Bob

 

[William Ryan]

Bob:

That sounds excessive by any measure, particularly with a DataReader. What
does the query look like? 30 seconds to pull <4,000 records with a reader
is definitely not good. Do all of your queries run this slow or is it
particular to this one?

 

[Bob]

Do all of your queries run this slow or is it particular to this one?

Oh, heavens, no. This is a production server and I'd be canned if all queries
ran that slow. It's only this one (a class of them, actually), and only under
the circumstance of retreiving a (relatively, for my application) large number
of rows.

30 seconds to pull <4,000 records with a reader is definitely not good.

Well not necessarily. It depends on the number of joins and what you're doing to
retreive the data. In this case the delay is not at all warranted; I'm just
doing a simple self-join from one table, and as I said before, it takes only 4
seconds from QA.

I remain baffled...

Bob

 

[Brad Williams]

Just stabbing here: Is there something about your code that would be
causing more/stronger locks to occur on that connection? You're not in
transaction or serviced component are you?

Brad Williams

 

[William Ryan]

Bob:

I know this is not an answer, but if all the other queries work well, why
would you come to the conclusion that it's ADO.NET's fault if all the other
queries work well. I'm probably a bit jaded b/c I was originally groomed as
a DBA and programmers were often quick to criticize the database, but is the
likely conclusion that it's really ADO.NET's fault?

So how about this. Kill all of the joins just to test a theory. I know
they are self-joins and I'm quite aware of how they work, but pull over two
copies of the db and relate them using a DataRelation (ie pull over a Select
Distinct on the first field that the self join is on).. just see how it
performs. I know what you are saying about self joins, but I've worked with
OLAP scenarios with well over 10 million records that don't take that long
or anywhere near it and if the DataReader is coughing like that, something
is wrong.

Can you show the table structures? I'd be glad to create the tables, fill
them with dummy data and try to replicate the problem. I can't promise a
solution but I'll definitely do what I can.

Cheers,

Bill

 

[Miha Markic]

Anyway, try this:
Instead of Fill invoke ExecuteReader - it will do only the select but not
fetch any data.

 

[Bob]

I (with some help from MS) have identified the problem to be that two different
execution plans are being generated for the same query depending on where it's
coming from; for some reason, a good one is generated from Query Analyser, while
a bad, very inefficient one is generated when executed from a client-side app
(as indicated from SQL Profiler). And this client-side problem is not limited to
ADO.Net - it happens from VB6, too.

Unfortunately the working repro is very ticklish - I created a database with one
table and one stored procedure copied from the production database, and it
works, the problem is reproduced. Only, if I delete any column or index, the
problem goes away. But get this - if I do the same on the production database (I
had hoped the solution were that simple) the problem remains.

I will post a repro when I find a stable one.

Bob

 

[Bob]

No repro. Here is the post-mortem.

The problem only happens on multiprocessor machines. The optimizer apparently
treats queries differently depending on where they're coming from - Quary
Analyzer or client-side application code. In some cases the optimizer can make
surprisingly bad decisions about quaries coming from applicatoin code. Index
hints can be added to fix this, but DBCC FREEPROCCACHE is required for these
changes to take effect, sp_recomiple is not sufficient.

MS insists this behavior is not a bug, that 'it happens' sometimes. I can
appreciate that the optimizer can make mistakes, it's not perfect. But I just
don't understand the necessity of treating queries from QA differently than
those coming from applications. It diminishes QA's usefullness.

Bob

 

[Fredrik Melin]

Hi,

I had simular problem, and found out that
Query analyzer has arithabort = on
Sql Server has arithabort = off default, and that is what .NET was using.

Setting arithabort on the server (connection properties, then restart
server) worked, Now I am getting the same execution plan for both QA and
..NET

Regards
Fredrik Melin