When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?
BjornI've seen this too. In my case when I looked at the execution plan and the server trace it appears the udf is called for each row returned where the sub query doesn't. I was using a udf to calculate the status of the records. I ended up using a view instead to calculate the status and joined my original query to the view. This is similiar to a sub query and much much faster than the udf.|||Thanks! It makes sense. It seems that the sub query runs first and only once in an sproc, extracting all the data needed for the main query.
Bjorn
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment