Information générale
Forum:
Microsoft SQL Server
Catégorie:
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!
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement