I am tuning an SP and re-written that to optimize. Now I am comparing old code and new code in terms of timing using "setting statistics time on/off".
While setting time statistics off, my new code is performing well at least 4 times better than old code (Old code is taking 4 secs and new code is taking 1 secs to execute), but when I am setting time statistics On, my new code is taking approx 12 secs and old code is taking around 7 secs.
Why new code is performing badly after setting time stats on? Looks like there is some cost of time stats also and in my new code, that cost is very higher in comparison to old code. Am I right? If so what is that?
Best Answer
If there are user defined functions in your code, there can be significant overhead to setting
STATISTICS TIME ON. See here.More importantly, be REALLY careful when using scalar UDFs. They will inhibit parallelism for your query, and have a nasty habit of running once per row returned by your query.
The impact they have on
STATISTICS TIMEshould be the least of your concerns.