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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
ASKER