I have a stored procedure that essentially rebuilds a pivot table. It builds the new data in a temp table, then truncates the permanent table and inserts the new data, and finally drops the temp table.
When I execute the stored proc (or the T-SQL code directly) in Management Studio, it takes about a minute. While I know this isn't the most efficient of processes, I'm OK with it.
My problem comes in when I try to schedule this task to run every 20 minutes or so. When I setup a SQL Server Agent Job to execute the stored proc, its now taking almost an hour and a half... that's right, 90 TIMES SLOWER!
I found this post: SQL Server Agent Job Running Slow, which seems to be a similar issue, but set nocount on doesn't seem to have any effect whether I call it at the beginning of the stored proc or before the exec command in the SQL Agent Job. My query doesn't use any cursors, though I am doing a cross apply on a table valued function (which also doesn't use any cursors).
I'm obviously missing something, but I don't even know where to start on this. I thought by creating the stored proc I would have avoided these types of issues.
For reference, the stored proc looks something like the following:
create table #temp
(
ID int,
data1 float,
data2 float
)
insert into #temp(ID, data1, data2)
select t.ID, d.data1, d.data2
from tbl1 t
cross apply dbo.getInterestingData(t.ID, t.param1) d
where d.useMe = 1
truncate table dataPivot
insert into dataPivot(ID, data1, data2)
select ID, data1, data2
from #temp
drop table #temp
