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

"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"
> <BojanKu***@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"
>> <BojanKu***@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?
>> >
>>