[quoted text, click to view] On Tue, 15 Mar 2005 16:39:03 -0800, Stingray wrote:
>Regarding your suggestion below "As an alternative, you could execute the
>stored procedure & get the results in a #temp table & then select from the
>#temp table as well." Can you give me an example of the syntax for this
>option or point me to the correct section of BOL? I have been searching for
>this very solution for some time now and I cannot find it.
Hi Stingray,
See my other message.
[quoted text, click to view] >Also, can you or someone else in the forum elaborate a bit on the
>User-defined functions option. It looks like an interesting option but I
>wonder what kind of system overhead there is for a function as opposed to an
>SP called in the manner mentioned above. Anyone have any info on this
>question?
General information about user-defined functions (UDF) can be found in
Books Online. If you have specific question after reading that, I'll
gladly answer them.
Re performance: this is different for the three types of UDF.
Inline table-valued functions are best - like views, the reference will
be replaced by the definition when a query gets executed, giving the
optimizer full freedom to rearrange as it sees fit.
Next best are multi-statement table-valued functions. They get invoked
once on execution of a statement holding it; the results are stored in
tempdb and used to execute the query that invoked the UDF. This means
that the optimizer has less freedom - it has to materialize the UDF
first before going on to the main query; it can't take shortcuts. Also,
you can't define indexes on it, so if the result of the UDF is large,
you may encounter severe slowness if it has to be scanned multiple
times.
The worst are scalar functions. If used in a query, they will be
executed at least once for each row returned, and possibly for rows not
returned as well (this depends on the execution plan chosen by the query
optimizer - you can't force it to postpone UDF evaluation until all
other parts of the WHERE clause are evaulated, and the optimizer has no
way to predict if a UDF invocation will be cheap or expensive).
Best, Hugo
--
