I have a stored procedure that I have developed on a SQL2008 server that runs <1sec. On another server which is SQL2005 the same sp on the same database takes ~1minute. Without going into the details of the database schema can anyone see anything obvious in this SP that may cause this performance discrepancy? Could it be the use of the CTE? Is there an alternative?
EDIT - I have now noticed that if I run the SQL directly on SQL 2005 it runs in ~4secs but executing the SP still takes over a minute?? Looks like the problem may like in the SP execution??
CREATE PROCEDURE Workflow.GetTopTasks
-- Add the parameters for the stored procedure here
@ownerUserId int,
@topN int = 10
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET ROWCOUNT @topN;
-- Insert statements for procedure here
WITH cteCalculatedDate (MilestoneDateId, CalculatedMilestoneDate)
AS
(
-- Anchor member definition
SELECT md.MilestoneDateId, md.SpecifiedDate
FROM Workflow.MilestoneDate md
WHERE md.RelativeMilestoneDateId IS NULL
UNION ALL
-- Recursive member definition
SELECT md.MilestoneDateId, CalculatedMilestoneDate + md.RelativeDays
FROM Workflow.MilestoneDate md
INNER JOIN cteCalculatedDate cte
on md.RelativeMilestoneDateId = cte.MilestoneDateId
)
-- Statement that executes the CTE
select
we.*
from Workflow.WorkflowElement we
left outer join cteCalculatedDate cte
on cte.MilestoneDateId = we.DueDateId
inner join Workflow.WorkflowInstance wi
on wi.WorkflowInstanceId = we.WorkflowInstanceId
left outer join Workflow.SchemeWorkflow sw
on sw.WorkflowInstanceId = wi.WorkflowInstanceId
left outer join Workflow.Scheme s
on s.SchemeId = sw.SchemeId
inner join Workflow.WorkflowDefinition wd
on wd.WorkflowDefinitionId = wi.WorkflowDefinitionId
where
we.OwnerId = @ownerUserId -- for given owner
and we.CompletedDate is null -- is not completed
and we.ElementTypeId <= 4 -- is Action, Data, Decision or Document (Not End, Start or KeyDate)
and cte.CalculatedMilestoneDate is not null -- has a duedate
UNION
select
we.*
from Workflow.WorkflowElement we
left outer join cteCalculatedDate cte
on cte.MilestoneDateId = we.DueDateId
inner join Workflow.WorkflowInstance wi
on wi.WorkflowInstanceId = we.WorkflowInstanceId
left outer join Workflow.SchemeWorkflow sw
on sw.WorkflowInstanceId = wi.WorkflowInstanceId
left outer join Workflow.Scheme s
on s.SchemeId = sw.SchemeId
inner join Workflow.WorkflowDefinition wd
on wd.WorkflowDefinitionId = wi.WorkflowDefinitionId
where
we.OwnerId = @ownerUserId -- for given owner
and we.CompletedDate is null -- is not completed
and we.ElementTypeId <= 4 -- is Action, Data, Decision or Document (Not End, Start or KeyDate)
and cte.CalculatedMilestoneDate is null -- does NOT have a duedate
SET ROWCOUNT 0
END
