Stored Procedure query slow in debug configuration.

[Guest]

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

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

 

[Guest]

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

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