Post reply

Using a SP to load a temp table ...

  • July 29, 2003 at 3:16 pm

    #80764

    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

    http://www.PSAKIDS.com

    Edited by - csmith@psakids.com on 07/29/2003 3:16:53 PM




    Curtis Smith
    SQL Server DBA
    Well in worked in Theory ...

  • July 29, 2003 at 3:50 pm

    #467623

    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.

  • July 29, 2003 at 4:56 pm

    #467628

    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

  • July 30, 2003 at 7:36 am

    #467731

    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