Stored Procedure query slow in debug configuration.

I have an old and proven stored procedure that takes on average 0.2 seconds
to execute. I always develop and perfom all measurements using VS .NET debug
configuration. This SP has always performed the same being called from Query
Analyzer, Enterprize manager or VB.NET code.

Recently I've done some changes (that should not affect the speed) to it and
from then the SP takes 10 seconds to execute from VB.NET. It still takes 0.2
seconds from Query Analyzer.

It took me whole day to figure out that when I change the configuration to
Release that query goes back to 0.2 seconds.

I have tried to recompile the SP drop all execution plans, change the query
from using parameters to text form (EXEC sp_xxxx @Param1=1, @Param2='A') and
all sort of other things but no luck, still 10 seconds in debug mode.

The funny thing is that when I put a break point in the SP, I can step
through it and see which SELECT statement takes 9 secs to execute. If I copy
that statement to Query Analyzer it takes 0.1 seconds.

I've searched the Net for an answer and although I have found simmilar
questions (Query Analyzer vs .NET code query speed ) nobody has linked this
to the debug configuration.

Any comments?

Why do you care about performance in debug mode? You're not going to
deploy it that way. The purpose of debugging is to catch code and
logic errors, not tune performance. Different tools use different
debug symbols, so you shouldn't expect performance to be equivalent
between .NET and QA. Once you've finished debugging, test perf using
the tuning advisor tool in QA and let it recommend appropriate
indexes.

Also, changing your code to use dynamic execution (EXEC) is exposing
your server to attack. I would recommend that you put the
parameterized stored procedures back.

--Mary

On Mon, 6 Feb 2006 01:10:29 -0800, "Bojan Kuhar"
<BojanKuhar@discussions.microsoft.com> wrote:

>I have an old and proven stored procedure that takes on average 0.2 seconds
>to execute. I always develop and perfom all measurements using VS .NET debug
>configuration. This SP has always performed the same being called from Query
>Analyzer, Enterprize manager or VB.NET code.
>
>Recently I've done some changes (that should not affect the speed) to it and
>from then the SP takes 10 seconds to execute from VB.NET. It still takes 0.2
>seconds from Query Analyzer.
>
>It took me whole day to figure out that when I change the configuration to
>Release that query goes back to 0.2 seconds.
>
>I have tried to recompile the SP drop all execution plans, change the query
>from using parameters to text form (EXEC sp_xxxx @Param1=1, @Param2='A') and
>all sort of other things but no luck, still 10 seconds in debug mode.
>
>The funny thing is that when I put a break point in the SP, I can step
>through it and see which SELECT statement takes 9 secs to execute. If I copy
>that statement to Query Analyzer it takes 0.1 seconds.
>
>I've searched the Net for an answer and although I have found simmilar
>questions (Query Analyzer vs .NET code query speed ) nobody has linked this
>to the debug configuration.
>
>Any comments?
>

I care because I don't have time to wait 10 seconds for something that should
take 0.2 seconds and I spend most of the development time in the debug config.

I've figured out that If I clear the SQLServer Debugiing check box in
project properties->Debugging the SP runs in .2 seconds again (rest of the
project in debug, obviously no T-SQL debugging but I can live with that).

I was just wandering if anybody else has the same problem. Maybe some debug
table for SQL server or PDB equivalent has got corrupted or overbloated,hence
the difference in speed. Bear in mind that I was always developing in debug
and the SP was fast.


Thanks in any case.


"Mary Chipman [MSFT]" wrote:

> Why do you care about performance in debug mode? You're not going to
> deploy it that way. The purpose of debugging is to catch code and
> logic errors, not tune performance. Different tools use different
> debug symbols, so you shouldn't expect performance to be equivalent
> between .NET and QA. Once you've finished debugging, test perf using
> the tuning advisor tool in QA and let it recommend appropriate
> indexes.
>
> Also, changing your code to use dynamic execution (EXEC) is exposing
> your server to attack. I would recommend that you put the
> parameterized stored procedures back.
>
> --Mary
>
> On Mon, 6 Feb 2006 01:10:29 -0800, "Bojan Kuhar"
> <BojanKuhar@discussions.microsoft.com> wrote:
>
> >I have an old and proven stored procedure that takes on average 0.2 seconds
> >to execute. I always develop and perfom all measurements using VS .NET debug
> >configuration. This SP has always performed the same being called from Query
> >Analyzer, Enterprize manager or VB.NET code.
> >
> >Recently I've done some changes (that should not affect the speed) to it and
> >from then the SP takes 10 seconds to execute from VB.NET. It still takes 0.2
> >seconds from Query Analyzer.
> >
> >It took me whole day to figure out that when I change the configuration to
> >Release that query goes back to 0.2 seconds.
> >
> >I have tried to recompile the SP drop all execution plans, change the query
> >from using parameters to text form (EXEC sp_xxxx @Param1=1, @Param2='A') and
> >all sort of other things but no luck, still 10 seconds in debug mode.
> >
> >The funny thing is that when I put a break point in the SP, I can step
> >through it and see which SELECT statement takes 9 secs to execute. If I copy
> >that statement to Query Analyzer it takes 0.1 seconds.
> >
> >I've searched the Net for an answer and although I have found simmilar
> >questions (Query Analyzer vs .NET code query speed ) nobody has linked this
> >to the debug configuration.
> >
> >Any comments?
> >
>

Why not just compile in release mode for those occasions when you care
about speed? You're never going to get the same performance out of
debug mode no matter what you try.

--Mary

On Tue, 7 Feb 2006 08:49:35 -0800, "Bojan Kuhar"
<BojanKuhar@discussions.microsoft.com> wrote:

>I care because I don't have time to wait 10 seconds for something that should
>take 0.2 seconds and I spend most of the development time in the debug config.
>
>I've figured out that If I clear the SQLServer Debugiing check box in
>project properties->Debugging the SP runs in .2 seconds again (rest of the
>project in debug, obviously no T-SQL debugging but I can live with that).
>
>I was just wandering if anybody else has the same problem. Maybe some debug
>table for SQL server or PDB equivalent has got corrupted or overbloated,hence
> the difference in speed. Bear in mind that I was always developing in debug
>and the SP was fast.
>
>
>Thanks in any case.
>
>
>"Mary Chipman [MSFT]" wrote:
>
>> Why do you care about performance in debug mode? You're not going to
>> deploy it that way. The purpose of debugging is to catch code and
>> logic errors, not tune performance. Different tools use different
>> debug symbols, so you shouldn't expect performance to be equivalent
>> between .NET and QA. Once you've finished debugging, test perf using
>> the tuning advisor tool in QA and let it recommend appropriate
>> indexes.
>>
>> Also, changing your code to use dynamic execution (EXEC) is exposing
>> your server to attack. I would recommend that you put the
>> parameterized stored procedures back.
>>
>> --Mary
>>
>> On Mon, 6 Feb 2006 01:10:29 -0800, "Bojan Kuhar"
>> <BojanKuhar@discussions.microsoft.com> wrote:
>>
>> >I have an old and proven stored procedure that takes on average 0.2 seconds
>> >to execute. I always develop and perfom all measurements using VS .NET debug
>> >configuration. This SP has always performed the same being called from Query
>> >Analyzer, Enterprize manager or VB.NET code.
>> >
>> >Recently I've done some changes (that should not affect the speed) to it and
>> >from then the SP takes 10 seconds to execute from VB.NET. It still takes 0.2
>> >seconds from Query Analyzer.
>> >
>> >It took me whole day to figure out that when I change the configuration to
>> >Release that query goes back to 0.2 seconds.
>> >
>> >I have tried to recompile the SP drop all execution plans, change the query
>> >from using parameters to text form (EXEC sp_xxxx @Param1=1, @Param2='A') and
>> >all sort of other things but no luck, still 10 seconds in debug mode.
>> >
>> >The funny thing is that when I put a break point in the SP, I can step
>> >through it and see which SELECT statement takes 9 secs to execute. If I copy
>> >that statement to Query Analyzer it takes 0.1 seconds.
>> >
>> >I've searched the Net for an answer and although I have found simmilar
>> >questions (Query Analyzer vs .NET code query speed ) nobody has linked this
>> >to the debug configuration.
>> >
>> >Any comments?
>> >
>>