• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Temp Tables From Stored Procs Performance Problems

I have a stored procedure that creates a temp table and then populates the table from a stored procedure, like below: -

CREATE TABLE #Table
(
Col1      varchar(32),
Col2      varchar(16),
Col3      varchar(32),
Col4      varchar(3),
)

SET ARITHABORT ON
INSERT      #Table
EXEC      dbo.ProcName @Param1, @Param2, @Parem3
SET ARITHABORT OFF

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?

Brett

0
BrettPaton
Asked:
BrettPaton
  • 2
1 Solution
 
obahatCommented:
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.

Omri.
0
 
BrettPatonAuthor Commented:
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.
0
 
obahatCommented:
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

SELECT GETDATE()

<Command>

SELECT GETDATE()

(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.
0
 
rafranciscoCommented:
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now