September 30, 2004 at 6:40 am
Having read this thread: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=139252
I have the following questions:
1/ Why is using SELECT... INTO #Table so costly?
2/ I am aware that using #Temp tables can cause an SP to recompile, buut what actually is involved in the recompile? Is all the data in previous tables retrieved again?
TIA, Carl.
October 4, 2004 at 8:32 am
I'll just give a shot of some short answers (the thread contained some pointers to deeper information)
1) It is 'costly' mainly because you're doing two things in one go - CREATE TABLE and INSERT.. This taxes resources and locks more than doing them separately.
2) When a proc recompiles resources are needed, it takes (some) time and a new plan is generated. Probably the latter is the most 'expensive' thing, sice we always strive for as few plans cached as possible - this in the long run yields higher performance.
For admin-type procs or late night batches, recompiles may not be as big issue, but when hundreds or more users hammer away at the same proc it can be a real showstopper.
In the end - it depends.
/Kenneth
October 4, 2004 at 11:05 pm
Recompiles can be caused by having DDL like table creation or Select/Into buried between normal DML code. Define all tables and temp tables at the beginning of your code to avoid this kind of problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply