Running EXEC( ) command in Stored Procedure

[BradEdwards]

I made 2 simple changes to my stored procedure and now it takes 3 times as long to run it. Here is the part of the code where I've made the changes:

Here was my code before:

CREATE PROCEDURE spStaffingModelsRpt
(
@StartDate datetime,
@EndDate datetime
)
AS

CREATE TABLE #Data
(
BRN smallint,
SEQ int,
StartTime datetime,
StopTime datetime,
TimeDiff datetime
)
INSERT INTO #Data (BRN, SEQ, StartTime, StopTime, TimeDiff)
(SELECT BRN, SEQ, StartTime, StopTime, (StopTime-StartTime) as TimeDiff
FROM tblJournal
WHERE (StartTime Between @StartDate and @EndDate))

And here is my code now:

CREATE PROCEDURE spStaffingModelsRpt
(
@Month varchar(20),
@StartDate datetime,
@EndDate datetime
)
AS

CREATE TABLE #Data
(
BRN smallint,
SEQ int,
StartTime datetime,
StopTime datetime,
TimeDiff datetime
)
INSERT INTO #Data (BRN, SEQ, StartTime, StopTime, TimeDiff)
Exec ('SELECT BRN, SEQ, StartTime, StopTime, (StopTime-StartTime) as TimeDiff
FROM ' + @Month +
' WHERE (StartTime Between ''' + @StartDate + ''' and ''' + @EndDate + '''))

The first code took about 18 seconds to run, and the latter took about 52 seconds and the only thing I changed was passing the @Month variable and using the EXEC() command. Does using the EXEC() command take longer than just a regular SELECT statement?? Is there anything I can do to speed it up?? Thanks.

 

[rajeevnandanmishra]

Hi BradEdwards,
Defenitely, using the EXEC command is slow, but not such slow.
It seems that tblJournal is having less data and/ or having the index created.
Just try to execute the new procedure, without the table name parameter and writing the statement without exec.

 

[tlbroadbent]

Move the Insert portion of your SQL Statement inside the EXECute. The query performance should then be similar to the previous query.

Exec ('INSERT INTO #Data (BRN, SEQ, StartTime, StopTime, TimeDiff)
SELECT BRN, SEQ, StartTime, StopTime, (StopTime-StartTime) as TimeDiff
FROM ' + @Month +
' WHERE (StartTime Between ''' + @StartDate + ''' and ''' + @EndDate + '''))
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.