Function Using table Variabels degrades perfomance in SQL 2008

Question

Post reply

Function Using table Variabels degrades perfomance in SQL 2008

gil_adi-803685

Grasshopper

Points: 12
More actions
June 30, 2010 at 12:50 am

#237153

We are now int the process of checking our environment in Sql server 2008.
The server for the 2008 is mauch more pwoerfull then the one used with 2008 (including the storage)
The tests on the 2008 environment are beeing made using an Isolated server with no users connected to it except the one user which perform the test and the server load does not come even close to the Production server.
The function is using a table variables
Runing on the production server (sql 2000) the function runs 16 time Faster the in the test Server (sql 2008)
We have tried to change the function into an sp and use temporary tables
the proformance where the same as the function running in the production (not better)
converting the function into a stored procedure is not an option , because using it as a stored procedure will force us to rewrite the hole system.
Are we forgetting somethings ?
Did any one stumbled on this problem ?
Is there a threshold (numbe of recordes , sixe of record , size of tbale) that if crossed degrades performance when using table varaible.

Viewing 14 posts - 1 through 14 (of 14 total)

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

IMO , table variables should be avoid unless you are using a small (100 ish) number of rows.

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

That being said , Function calls are generally also wrong as they lead to a more procedural based solution than a set based system. None of which you probably wanted to hear.

So for your problem in hand , how do the query plans on 2008 look ? Are they effectively the same as on 2000 ?

Update the statistics on the 2008 box and re-execute the query and see if you get the same plan.

Also is it each and every execution that is slow ? Could be due to parameter sniffing.

If this doesnt help , then please post DDL , Code and Plans as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Clear Sky SQL
My Blog[/url]

gil_adi-803685 Grasshopper Points: 12 More actions · Answer 2

Thanks for the quick answer

we are using function to return sets of dats (as tables);

to avoid breaking the set based thinking

Thw paramters assignd to the function each execution does not impact the performance sagnificaly/

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

I have heard that some functions, usually ones that are problematic in nature anyway, do perform slower on 2008 than on 2000. I have not seen or heard of any precise metric for exactly when this occurs, but it does seem to be an isolated, but not uknown, issue.

Sometimes though, people have not done a manual update on statistics as part of the database migration and so sQL Server has to manually rebuild stats on each query call as it identifies them being old-style statistics in need of update. This is frequently identified as "SQL Server 2008 runs slower" when it's actually just a need for new stats. If you didn't explicitly update all stats, you might try that first, before you start looking at rewriting your procs.

"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

HowardW One Orange Chip Points: 29109 More actions · Answer 4

Do you want to share the code for the function with some sample data?

It would be interesting to see if this could be converted to an Inline Table Valued Function that avoided using table variables - that's the way to really improve the performance.

Using multi-statement TBV's can be as bad as a non-set based approach

Kit G SSCrazy Points: 2888 More actions · Answer 5

HowardW (6/30/2010)
Do you want to share the code for the function with some sample data?
It would be interesting to see if this could be converted to an Inline Table Valued Function that avoided using table variables - that's the way to really improve the performance.
Using multi-statement TBV's can be as bad as a non-set based approach

What does TBV stand for? Or is that a typo? (I'm not familiar with that abbreviation which is why I'm asking. 🙂 )

-- Kit

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 6

Kit G (6/30/2010)
HowardW (6/30/2010)
Do you want to share the code for the function with some sample data?
It would be interesting to see if this could be converted to an Inline Table Valued Function that avoided using table variables - that's the way to really improve the performance.
Using multi-statement TBV's can be as bad as a non-set based approach
What does TBV stand for? Or is that a typo? (I'm not familiar with that abbreviation which is why I'm asking. 🙂 )

I'm pretty sure he mean TVF, table valued function.

"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

HowardW One Orange Chip Points: 29109 More actions · Answer 7

Grant Fritchey (6/30/2010)
Kit G (6/30/2010)
HowardW (6/30/2010)
Do you want to share the code for the function with some sample data?
It would be interesting to see if this could be converted to an Inline Table Valued Function that avoided using table variables - that's the way to really improve the performance.
Using multi-statement TBV's can be as bad as a non-set based approach
What does TBV stand for? Or is that a typo? (I'm not familiar with that abbreviation which is why I'm asking. 🙂 )
I'm pretty sure he mean TVF, table valued function.

Yes, clearly haven't had enough coffee today. It was a typo!

Kit G SSCrazy Points: 2888 More actions · Answer 8

heh. I understand. Personally I prefer my caffiene in tea flavors, but I understand. Thanks for clearing that up. Wanted to make sure I wasn't missing something somewhere. 🙂

-- Kit

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 9

HowardW (6/30/2010)
It would be interesting to see if this could be converted to an Inline Table Valued Function that avoided using table variables - that's the way to really improve the performance.

Have you compared performance between using a Table Variable and a TVF?

It might have been in 2005 (been a while since I've done comparisons) but, Joins to TVF are/where even slower than joining to a Table Variable. Which seemed strange to me since SQL doesn't maintain statistics and such on table variables. But, it was literally an order of magnitude faster to join to a Temp Table or Table Variable than to join to a TVF. But, again there are lots of variables involved.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 10

Lamprey13 (6/30/2010)
HowardW (6/30/2010)
It would be interesting to see if this could be converted to an Inline Table Valued Function that avoided using table variables - that's the way to really improve the performance.
Have you compared performance between using a Table Variable and a TVF?
It might have been in 2005 (been a while since I've done comparisons) but, Joins to TVF are/where even slower than joining to a Table Variable. Which seemed strange to me since SQL doesn't maintain statistics and such on table variables. But, it was literally an order of magnitude faster to join to a Temp Table or Table Variable than to join to a TVF. But, again there are lots of variables involved.

Yeah, if you're talking about multi-statement TVF's, I'm not at all surprised. Table variables, while problematic, don't have to generate their data in addition to working with the query. TVF's have to generate their data AND perform the JOIN, or whatever, within the query. They're dangerous.

But the inline TVF's are generally pretty safe, but they can be problematic too.

"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

Kit G SSCrazy Points: 2888 More actions · Answer 11

Forgive the question and the possible hijack of the thread, but I've a question that BOL isn't easily answering. What's the difference between an "inline" TVF and a "multi-statement" TVF?

edit: to fix punctuation.

-- Kit

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 12

Check out simon sabins recent 24 hours of pass session

http://www.sqlpass.org/24hours/2010/Sessions/HighPerformanceFunctions.aspx

Clear Sky SQL
My Blog[/url]

Kit G SSCrazy Points: 2888 More actions · Answer 13

Dave Ballantyne (7/1/2010)
Check out simon sabins recent 24 hours of pass session
http://www.sqlpass.org/24hours/2010/Sessions/HighPerformanceFunctions.aspx

Thanks for the link, it made for an interesting hour. 🙂

-- Kit