Table variable in stored proc - query plan reuse
-
If I have table variables in a stored procedure, I'm sure I've heard somewhere that the query plan will be recompiled every time the procedure is called. Is this true, and will that potential performance problem go away if I replace all the table variables with #tmp tables?
The table variables will typically contain 1000 to 10,000 rows, so it could enhance performance to change them to #tmp tables anyway?
-
Can't say without seing the code.
So when you ran the proc with profiler on did you see the recompile?
Did you try both version and again compare the perf in profiler?
-
Tom Brown (11/15/2011)
If I have table variables in a stored procedure, I'm sure I've heard somewhere that the query plan will be recompiled every time the procedure is called.Nope. That's the one advantage of table variables. Because they don't have statistics they can't cause procedures to recompile. Temp tables will cause that (maybe on creation, definitely after rows are inserted)
The table variables will typically contain 1000 to 10,000 rows, so it could enhance performance to change them to #tmp tables anyway?
Depends how you're using them, but very likely yes. Test with a table variable, test with a temp table. See if there are major performance differences (use Statistics IO and statistics time).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I can't post the code - aside from the security issues, its 630 lines and contains selects and joins on 10 or 12 other tables.
How do I get the profiler to show recompiles When I run the profiler (standard) I just get
SQL:BatchStarting EXEC <myproc>
SQL:BatchCompleted EXEC <myproc>
When I run profiler (Tuning) I get loads of SP:StmtCompleted - but nothing with 'recompile' in it
-
Don't need the code. The recompiles is not an 'it depends' answer. Table Variables do not cause recompiles.
As for monitoring recompiles:
http://sqlinthewild.co.za/index.php/2010/11/18/recompiles/
http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You have the recompile event in the tsql category as well.
-
I wanted the code to see how the table was used to see if it was a bad use.
-
I've tested and Its definitely quicker with some of the @table variables converted to #tmp tables (the ones that are reused in joins) - I then added indexes on the #tmp table join columns for a further boost.
- over several runs
Before ~32/36 seconds
After ~28/34 seconds - convert to #tmp
After ~22/26 seconds + index on #tmp
I'm happy - even though Its not for the reasons I first supposed.
And thanks for the profiler tip. I hadn't noticed that "Show All Events" checkbox
-
Tom Brown (11/15/2011)
I've tested and Its definitely quicker with some of the @table variables converted to #tmp tables (the ones that are reused in joins) - I then added indexes on the #tmp table join columns for a further boost.- over several runs
Before ~32/36 seconds
After ~28/34 seconds - convert to #tmp
After ~22/26 seconds + index on #tmp
I'm happy - even though Its not for the reasons I first supposed.
And thanks for the profiler tip. I hadn't noticed that "Show All Events" checkbox
24 seconds is still slow for, almost anything. Want us to look at it further? We can help if you can post the actual execution plan.
-
Thanks for the offer.
My timings are for a developer machine - it generally takes around 50/60 seconds in production
And I know why its slow.
There is an update to a major table happening in a while loop, and a second update or insert to a 3rd party table (so we have no control over its structure) also inside the while loop.
Its a big example of how you're told not to do things.
However I've been told this is the way it has to be. In the production system hundreds of locks will be being set and released all the time on the tables I'm updating.
If I try configure the proc to do the update in one hit (we've tried it) in production
1) It takes longer as it has to wait for all the locks in production to clear.
2) Production grinds to a halt - because new locks have to wait for my process to finish - so loads of production processes start timing out.
3) The phone starts ringing, managers start panicing, people congregate looking on accusingly.
-
And I'm not going to try to get the actual execution plan of a while loop process again!! - big mistake.
-
Tom Brown (11/15/2011)
And I'm not going to try to get the actual execution plan of a while loop process again!! - big mistake.😀
1 loop would have been enough, but that's another story.
P.S. You can get the plan in a trace with => Performance, SHOWPLAN XML for statistic profile.
Doesn't kill the machine and gets the job done (don't try in prod) :hehe:.
-
Any reason why you're no using snapshot isolation?
-
I need to read up on SNAPSHOT stuff, as it could be a great benefit to some of the stuff we do.
As I understand it - correct me if I'm wrong, its a very recent copy of the DB - but its read-only, so you can run queries in almost real time, without any of the production locks - or is that replication?
Can SNAPSHOTs be used to update the original?
-
No snapshot isolation uses the row version store in tempdb to read the last "valid" version of the row while an update occurs in the base table. That way readers are not blocked by writers.
http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx
http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply