At a loss over performance of a TBF

Question

At a loss over performance of a TBF

ccappel

SSC Enthusiast

Points: 172
More actions
September 4, 2014 at 6:57 am

#310690

I have a complex table-valued function with multiple CTEs. When I run it as such:
SELECT * FROM dbo.TableValuedFunction (Param1, Param2)
It takes just over 2 minutes to return just over 10,000 rows.
However, if I select only specific columns, it never finishes (I stopped the query at 40 minutes and it hadn't even returned half the rows.
SELECT ID FROM dbo.TableValuedFunction (Param1, Param2)
Any ideas where I could begin to troubleshoot this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

To start with, don't use multi-statement table-valued functions. They're notorious performance problems, especially when joined to other tables. That's because of the lack of stats on the table variables.

Try converting it to a procedure. If it still behaves badly, post the code, table definitions, index definitions and execution plan, as without those it's near-impossible to diagnose problems

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Pretty much what Gail says.

Because the optimizer is going to assume you're only returning 1 row ( the lack of statistics), it's going to create an execution plan for returning 1 row. The fact that you're returning 10,000 rows (or any number other than 1) means you're getting a bad execution plan. But unless you do what Gail suggests, there's nothing you can do about it because it really is down to the statistics (or lack of them anyway).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

ccappel (9/4/2014)
I have a complex table-valued function with multiple CTEs. When I run it as such:
SELECT * FROM dbo.TableValuedFunction (Param1, Param2)
It takes just over 2 minutes to return just over 10,000 rows.
However, if I select only specific columns, it never finishes (I stopped the query at 40 minutes and it hadn't even returned half the rows.
SELECT ID FROM dbo.TableValuedFunction (Param1, Param2)
Any ideas where I could begin to troubleshoot this?

Please post the function so we can see if it's an mTVF or an iTVF, to start with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Good stuff from the big 3 already. Here is an article to chew on that elaborates a bit more on the TVF perf problems

http://blog.waynesheffield.com/wayne/archive/2012/02/comparing-inline-and-multistatement-table-valued-functions/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events