September 24, 2013 at 5:10 am
Just after some advice if possible.
I had a Stored Procedure which had a join to a Scalar Valued Function, returning 40 rows in its final data set. It was doing 300 thousand reads and taking 9 seconds to return its data.
Soooooooo….
I ripped the Function out of the Join and populated the results of the Function into a Local Temporary Table at the top of the Procedure. Reads came down to 1982, took less than 1 second to run. Now I know Scalar Functions are poor in SELECT and WHERE clauses due to the row by row processing and executes the Function, but I didn’t see why this would be a problem in the Join?!
Soooooooo…
I tested one other thing, I swapped the Local Temporary Table out for a Table Variable and ran the query again. This time the performance was back to how it was originally with the ridiculous amount of reads being performed.
Cache and Buffers dropped each time. STATISTICS IO showed the massively reduced reads when using the Local Temp Table.
The Execution Plan highlighted the difference, the Local Temporary Table version was using Parallelism and the Table Variable/Function versions were not. Both procedures were over the cost Threshold for Parallelism so not sure why one chose to use it and not the other?!
Any ideas?
Cheers guys
September 24, 2013 at 5:15 am
Functions are poor in joins for the same reason they're poor in where. It's non-SARGable and the functions are executed once per row. Horrid combo.
Table variables inhibit parallelism. Put one in and you'll get a serial plan and probably a bad one due to the lack of statistics on the table variable (if there's enough rows to make parallelism a viable option)
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
September 24, 2013 at 6:21 am
Thanks for the reply Gail. With there being such few rows I expected the Table variable to perform similar to the Local Temporary Table.
The more I use Table Variables (variant result sets) the less use I find for them.
September 24, 2013 at 7:09 am
I primarily only use table variables where I need to avoid recompiles for some reason. Otherwise, the cost incurred by the lack of statistics is almost always not worth 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
September 24, 2013 at 7:33 am
I very seldom use table variables. In functions because that's the only thing allowed, but then I avoid functions as far as possible. Only really useful when the lack of a recompile is necessary and the lack of statistics doesn't adversely affect the plan (which I'll test to ensure).
They don't have major advantages over temp tables and the lack of stats often hurts a whole lot.
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
September 24, 2013 at 7:59 am
Cheers guys.
I am working through the current environment at the moment in a bid to try and re-write the functionality of Scalar Functions and have come across instances where just swapping the Table Variables out for Local Temporary Tables has yielded huge performance gains alone.
Guess there is a lot of literature out there and stuff has been implemented on the back of it without proper testing.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply