Temp Tables From Stored Procs Performance Problems

Posted on 2005-04-20
Last Modified: 2008-02-01
I have a stored procedure that creates a temp table and then populates the table from a stored procedure, like below: -

Col1      varchar(32),
Col2      varchar(16),
Col3      varchar(32),
Col4      varchar(3),

INSERT      #Table
EXEC      dbo.ProcName @Param1, @Param2, @Parem3

If I run dbo.ProcName on its own, it takes 2 seconds to return. If I run the stored proceure with the create table and then execute the stored proc, it takes approx 2 minutes to complete.

Can anyone shed any light on this or provide a more efficient way of doing this?


Question by:BrettPaton
    LVL 5

    Accepted Solution

    What you describe should not be the case.
    Can you please isolate the issue, by plotting (e.g., by PRINT statements for example) the datetime before and after the line that executes the stored proc?
    This way you can make sure that the execution of dbo.ProcName is indeed slow, and eliminate the possibility that other components within the main proc are slow.

    The problem with temp tables is typically the recompilcation. Once the table is created, if you have auto statistics set to ON, then every time you create and use the temp table, the procs that use the table recompile.
    If dbo.ProcName is a long proc, with a lot of code, then the scenario above is an outcome of recompilation. However these issues are only noticeable when the procs have a lot of complicated logic (since then it takes some time to compile them).

    Please repost with the time stamps.

    Hope this helps.


    Author Comment

    When I run dbo.ProcName on it's own, it returns under 2 seconds. However, when I run the outter procedure, it takes over a minute. The issue points towards the population of the temp table.
    LVL 5

    Expert Comment

    I did see this fact in the original posting, however, I think that the problem is elsewhere.
    For each command in either proc (or set of commands), please try to put




    (or such)

    to measure the actual time that it takes for each command to run.
    I truely believe that the problem is elsewhere, since the behaviour that you describe does not follow typical SQL performance practices.
    LVL 28

    Expert Comment

    How many records are being returned by your stored procedure?  Maybe there are a lot records being inserted to your temporary table that's why it's taking longer.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now