HELP serious performance degradation compared to QA

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
Tue Jan 06 11:48:12 CST 2004

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 - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Bob" <noemail@nospam.net> wrote in message
news:%231BWNuG1DHA.2948@TK2MSFTNGP09.phx.gbl...
> 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
>

Bob
Tue Jan 06 12:05:38 CST 2004

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

"Miha Markic" <miha at rthand com> wrote in message
news:%23pgnD1H1DHA.3496@TK2MSFTNGP11.phx.gbl...
> 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 - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
>
> "Bob" <noemail@nospam.net> wrote in message
> news:%231BWNuG1DHA.2948@TK2MSFTNGP09.phx.gbl...
> > 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
Tue Jan 06 12:02:17 CST 2004

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

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Bob" <noemail@nospam.net> wrote in message
news:O1ilF7H1DHA.2408@tk2msftngp13.phx.gbl...
> You are wrong. Query analyzer returns all rows. It is Enterprise Manager's
table
> viewing tool that returns only a few.
>
> "Miha Markic" <miha at rthand com> wrote in message
> news:%23pgnD1H1DHA.3496@TK2MSFTNGP11.phx.gbl...
> > 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.

Bob
Tue Jan 06 12:29:33 CST 2004

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

"Miha Markic" <miha at rthand com> wrote in message
news:%23v3P78H1DHA.2224@TK2MSFTNGP10.phx.gbl...
> No, I don't think it does.
> It lets you scroll though. Try scrolling to end.
>
> --
> Miha Markic - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
>
> "Bob" <noemail@nospam.net> wrote in message
> news:O1ilF7H1DHA.2408@tk2msftngp13.phx.gbl...
> > You are wrong. Query analyzer returns all rows. It is Enterprise Manager's
> table
> > viewing tool that returns only a few.
> >
> > "Miha Markic" <miha at rthand com> wrote in message
> > news:%23pgnD1H1DHA.3496@TK2MSFTNGP11.phx.gbl...
> > > 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.
>
>

William
Tue Jan 06 13:07:50 CST 2004

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" <noemail@nospam.net> wrote in message
news:%231BWNuG1DHA.2948@TK2MSFTNGP09.phx.gbl...
> 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
>

Bob
Tue Jan 06 14:10:19 CST 2004

> 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

"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:O4vXlfI1DHA.2604@TK2MSFTNGP09.phx.gbl...
> 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?

Brad
Tue Jan 06 17:46:20 CST 2004

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
Tue Jan 06 22:04:34 CST 2004

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
"Bob" <noemail@nospam.net> wrote in message
news:Orh3wAJ1DHA.536@tk2msftngp13.phx.gbl...
> > 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
>
> "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> news:O4vXlfI1DHA.2604@TK2MSFTNGP09.phx.gbl...
> > 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?
>

Miha
Wed Jan 07 03:57:12 CST 2004

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

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Bob" <noemail@nospam.net> wrote in message
news:O1ilF7H1DHA.2408@tk2msftngp13.phx.gbl...
> You are wrong. Query analyzer returns all rows. It is Enterprise Manager's
table
> viewing tool that returns only a few.
>
> "Miha Markic" <miha at rthand com> wrote in message
> news:%23pgnD1H1DHA.3496@TK2MSFTNGP11.phx.gbl...
> > 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 - RightHand .NET consulting & development
> > miha at rthand com
> > www.rthand.com
> >
> > "Bob" <noemail@nospam.net> wrote in message
> > news:%231BWNuG1DHA.2948@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> >
> >
>

Bob
Wed Jan 07 11:05:51 CST 2004

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

"William Ryan" <dotnetguru@nospam.comcast.net> wrote in message
news:%23QnbOON1DHA.3436@tk2msftngp13.phx.gbl...
> 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
> "Bob" <noemail@nospam.net> wrote in message
> news:Orh3wAJ1DHA.536@tk2msftngp13.phx.gbl...
> > > 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
> >
> > "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> > news:O4vXlfI1DHA.2604@TK2MSFTNGP09.phx.gbl...
> > > 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
Thu Jan 08 13:16:44 CST 2004

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

"Bob" <noemail@nospam.net> wrote in message
news:ezyBV%23T1DHA.2544@TK2MSFTNGP10.phx.gbl...
> 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
>
> "William Ryan" <dotnetguru@nospam.comcast.net> wrote in message
> news:%23QnbOON1DHA.3436@tk2msftngp13.phx.gbl...
> > 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
> > "Bob" <noemail@nospam.net> wrote in message
> > news:Orh3wAJ1DHA.536@tk2msftngp13.phx.gbl...
> > > > 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
> > >
> > > "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> > > news:O4vXlfI1DHA.2604@TK2MSFTNGP09.phx.gbl...
> > > > 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?
> > >
> >
> >
>

Fredrik
Fri Jan 09 06:37:37 CST 2004

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


"Bob" <noemail@nospam.net> wrote in message
news:ezyBV%23T1DHA.2544@TK2MSFTNGP10.phx.gbl...
> 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
>
> "William Ryan" <dotnetguru@nospam.comcast.net> wrote in message
> news:%23QnbOON1DHA.3436@tk2msftngp13.phx.gbl...
> > 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
> > "Bob" <noemail@nospam.net> wrote in message
> > news:Orh3wAJ1DHA.536@tk2msftngp13.phx.gbl...
> > > > 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
> > >
> > > "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
> > > news:O4vXlfI1DHA.2604@TK2MSFTNGP09.phx.gbl...
> > > > 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?
> > >
> >
> >
>