Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

UDF table return performance problems


Hi I have a UDF table that takes about 10 mins to run it also calls other UDF
tables and passes values to them as well. I had looked at the show execution
query plan and have run index tuning wizard from QA on the query and no
recommendations.

I have read a few places that using the show execution plan on UDF tables
for optimization will not give any insight as its designed for PURE sql. Not
for computational items. IS this true?

I also read that all UDF's use row by row processing is this also definately
fact?

How would you recommend I profile these UDF tables to try and improve there
performance if you have any experience of these.

thank you for any advice

Sammy

Sammy, see inline

Sammy wrote:

> Hi I have a UDF table that takes about 10 mins to run it also calls other UDF
> tables and passes values to them as well. I had looked at the show execution
> query plan and have run index tuning wizard from QA on the query and no
> recommendations.

I assume, that with "UDF table" you a table-valued function (either
inline or multistatement).

> I have read a few places that using the show execution plan on UDF tables
> for optimization will not give any insight as its designed for PURE sql. Not
> for computational items. IS this true?

No, not true. At least not on SQL Server 2005 SP2. Even an inline
table-values function with no table access would show up in the
execution plan.

> I also read that all UDF's use row by row processing is this also definately
> fact?

Not all UDF's are equal. If you use a scalar UDF, then it is most likely
that the UDF will be called for each row that you apply it to. Or even
more than once, if you have (several) joins. But table-valued function
are something completely different, and act more like views.

> How would you recommend I profile these UDF tables to try and improve there
> performance if you have any experience of these.

Yes, tune it like you would normally do. Check the query plans, check
for proper and usuable indexes on your table, etc.

Also, if you have the choice between a multistatement and inline
table-valued functions, then go for the inline table-valued functions,
because they can be expanded, which means the optimizer has a better
chance to optimize the query.

Gert-Jan

Sammy (S@discussions.microsoft.com) writes:
> Hi I have a UDF table that takes about 10 mins to run it also calls
> other UDF tables and passes values to them as well. I had looked at the
> show execution query plan and have run index tuning wizard from QA on
> the query and no recommendations.

> I have read a few places that using the show execution plan on UDF
> tables for optimization will not give any insight as its designed for
> PURE sql. Not for computational items. IS this true?

> I also read that all UDF's use row by row processing is this also
> definately fact?

Scalar UDFs can indeed lead to row-by-row processing. This is particularly
noticeable if the UDF performs data access.

Table-valued UDFs do not have this problem.

If you are only using table-valued UDFs, I don't think you should have
that much problems. With scalar UDFs you may get very many events in
the Profiler trace.

Also keep in mind that inline table UDFs are not really functions at
all, but just parameterised views. They will never show up in the
trace as a separate statement, since are expanded right into the
query where they appear.

--
Erland Sommarskog, SQL Server MVP, esq@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

-----------------------------------------------Reply-----------------------------------------------

Inline table-valued UDFs will be expanded, and show up in the query plan
of the query that uses the UDF. However, multistatement table-valued
UDFs have their own query plan. At least, that is what one of my
experiments showed.

Gert-Jan

-----------------------------------------------Reply-----------------------------------------------

Gert-Jan Strik (s@toomuchspamalready.nl) writes:
> Erland Sommarskog wrote:
>> Also keep in mind that inline table UDFs are not really functions at
>> all, but just parameterised views. They will never show up in the
>> trace as a separate statement, since are expanded right into the
>> query where they appear.

> Inline table-valued UDFs will be expanded, and show up in the query plan
> of the query that uses the UDF. However, multistatement table-valued
> UDFs have their own query plan. At least, that is what one of my
> experiments showed.

That is correct, but I was thinking of a trace, and more precisely
the events SP:StmtCompleted and the like.

--
Erland Sommarskog, SQL Server MVP, esq@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

-----------------------------------------------Reply-----------------------------------------------

Gert-Jan Strik (s@toomuchspamalready.nl) writes:
> Inline table-valued UDFs will be expanded, and show up in the query plan
> of the query that uses the UDF. However, multistatement table-valued
> UDFs have their own query plan. At least, that is what one of my
> experiments showed.

Well, they may not show up as an item of their own in the query plan,
but as bits and pieces here and there, as the optimizer may recast the
computation order.

--
Erland Sommarskog, SQL Server MVP, esq@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc