December 11, 2003 at 3:27 pm
Has anyone ever experienced a query to run extremely slow when you use 'set statistics time on', verses not having time stats turned on? I am experiencing a that a query will run about 8 times slower with statistics time on. Any explaination?
Not all queries, just one in particular, and there is a function call withing the select statement, if that makes a difference.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 12, 2003 at 1:14 am
Have experienced exactly the same myself.
I'll bet that your function is a Scalar function, in which case, SQL Server seems to execute the function just like a stored procedure.... once for each row. In that case, you are actually getting the statistics time for each execution of the function, and SQL Server is (I think) summing each execution to give you a total time for the query.
Table functions seem to execute differently, and I haven't seen the same performance degradation with them.
December 15, 2003 at 11:25 am
You are exactly right about the the overhead related to a scaler function. My query does in fact use a scaler function and not a table function.
If your assumption is correct then the another method of calculating the elapsed time should be use instead of "set statistics time on", something like this:
declare @sd datetime
set @sd = getdate()
select a, b, scaler_function(c) from tablexyz
select 'Elapsed time: datediff(ms,@sd,getdate())
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply