General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
David,
I've run into this a lot. If you take the scalar function and change it to a table-valued function, you can keep the reusability benefits without the insane performance hit (scalar functions can be 100's of times slower than straight code).
What I mean is that you can re-design the function so that it performs the row-level calculations over the entire data set instead of one row at a time and join the result to the original query.
The only remaining performance implication from using a table-valued function is that the entire result set must be generated before the calling query can begin using the results. By contrast, the result from a sub-query or stored procedure (in an INSERT statement, for instance) is used as soon as the first row of the result set is returned.
>Thats what I was afraid of, basically I have a sp that returns a number of items, it then runs these items through a UDF that returns its tax value. The number of items is variable, could be 1, could 10,000. The udf does access data, while using the udf makes coding A LOT easier, I'm just not sure on "best practice".
>
>Thanks for the response Sergey!
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only