Using a SP to load a temp table ...
-
this one has me puzzled, so I'll ask the experts. Kinda hard to explain: I'm calling an SP that calls another SP which populates a temp table [INSERT into #data_tmp EXEC usp_mytest]. Works fine when done once, but performance crawls when done back to back in same SP.
Example
CREATE PROC usp_test1
AS
SET NOCOUNT ON
create table #data_tmp
(row_id int not null, description char(60) not null )
INSERT INTO #data_tmp
EXEC usp_test2
INSERT INTO #data_tmp
EXEC usp_test3
select * from #data_tmp
go
I can mail sample code to anyone who can help.
Curtis Smith
App. Dev. Manager, DBA
Edited by - csmith@psakids.com on 07/29/2003 3:16:53 PM
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
Can you post the error?
Usually temp tables created in an sp are deleted after the execution of it.
But is not bad to add an IF EXISTS DROP TABLE clause to be sure.
Maybe it isn't droping the #table.
-
Check for recompiles on sp.
"We" always drop temp tables explicitly.
If it is recompiles have a look at http://www.databasejournal.com/features/mssql/article.php/2218451
-
the problem isn't with dropping the table, and I'm not getting an error. performance just slows down. [will check for recompiles, though] I have two SP's that basically return the same data structure. what I'm doing is calling those to load a temp table with the results of both SP's and returning the combined data.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply