Does Table Value function caches execution plan?

Question

Does Table Value function caches execution plan?

Anamika

SSCertifiable

Points: 6202
More actions
January 15, 2012 at 11:58 pm

#391316

hi,
Does Table Value function caches execution plan?
one of my query is performing slow for the first time. i can't put this query in stored proc as it is getting called from Tableau.
but i can put this in Table Value function and call from Tableau.
My question Does Table Value function caches execution plan? so that i can gain performance?
Thanks,
Ami

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

Inline table valued funtion or multi-statement table valued function?

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

Anamika SSCertifiable Points: 6202 More actions · Answer 2

does it make a difference?

it is multi-statement table valued function

Thanks,

Regards,

Ami

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

Yes it does make a difference, if it didn't I wouldn't have asked.

Multi-statement table valued functions do cache execution plans, they're treated more like procedures. That said, they have performance problems of their own coming from their use of table variables for output.

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 4

The cached plan only saves you recompile time for performance. A multi-statement table valued function is only good for working with very small data sets. This is because, as Gail points out, it uses table variables. Table variables have no statistics. This lack of stats makes them very poor performers depending on what your'e doing with it.

"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

Anamika SSCertifiable Points: 6202 More actions · Answer 5

thanks Fritchey,

small set of value means may i take it as less than 100 rows or 500-1000 rows?

with the current execution plan can i force sql server to make use of the hints?

so that the first time itself i can get the performance?

thanks,

ami

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

Anamika (1/17/2012)
thanks Fritchey,
small set of value means may i take it as less than 100 rows or 500-1000 rows?

Sometimes even 1 row is too many.

with the current execution plan can i force sql server to make use of the hints?
so that the first time itself i can get the performance?

What hints?

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

Anamika SSCertifiable Points: 6202 More actions · Answer 7

Anamika

SSCertifiable

Points: 6202

January 18, 2012 at 4:02 am

#1435107

hints to make use of particular indexes

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

And why do you think that hints will

1) Make the first execution faster?

2) Be better than what the optimiser comes up with itself?

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 9

You're looking at a 500+ rows in a multi-statement UDF and you've got index hints inside the queries? I think you're digging a very deep hole. Hints should be used in very few situations. If you have multiple hints inside this query I think you're way off on the wrong track. And no, I wouldn't trust the UDF with 500+ rows.

"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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Anamika (1/15/2012)
hi,
Does Table Value function caches execution plan?
one of my query is performing slow for the first time. i can't put this query in stored proc as it is getting called from Tableau.
but i can put this in Table Value function and call from Tableau.
My question Does Table Value function caches execution plan? so that i can gain performance?
Thanks,
Ami

1) if it is performing slowly the FIRST time and not the SECOND+ time it could simply be a matter of doing physical IO the first pass to get the data off disk and into RAM.

2) You should absolutely get rid of your multistatement UDF. They are death from a performance standpoint.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service