SQL Server Performance Forum – Threads Archive

Avoid Temporary Table

Hello, I am working on fixing issue related to Temporary Table.Actually i am having three stored procedure as A,B & C and stored procedure A creates one Temporary Table using Create table #d.Also after performing "if" condition validation its call stored procedure B and C for inserted data and after inserting,its dump the data into permanent table and lastly at the end of stored procedure A its drops Temporary Table. In similar way stored procedure A gets called by others 28 stored procedure.Means 28 times atemporary table gets created & dropped.Due to this above reason, my process is taking one hours extra time(approx)to completed. I need your advice on how i can avoid creation of temporary table in above scenario. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Is there any reason why data are not inserted directly? Maybe they want all or nothing to be inserted but they want to avoid locking that would be caused if explicit transaction is involved. How many rows are inserted? Do you have more problems with stored procedures recompilations or with actual execution time? If I got right what your problem is, you could replace inner procedure calls with table function selects. Your proc:
create procedure A as begin
—
create table #d(…)
—
if condition begin
exec B parameters
exec C parameters
end
—
insert into permanentTable select * from #d
—
drop table #d
—
end
Improved proc:
create procedure A as begin
—
if condition begin
—
insert into permanentTable
select *
from tableFunctionB(parameters)
union –all to keep dups
select *
from tableFunctionC(parameters)
—
end
—
end

hi hemant,
After reading ur post i was unable to find any reason for creating and dropping temorary table. i think u shld plan ur procedure in a way that it should commit all the action queries if nothing goes wrong else rollback. try n use @@transaction variable well. i think u can avoid using those temporary tables by well structured SP.
as u have mentioned SP b & C is called by other 28 procedures. do those 28 proc. has same temp. issues.

]]>