Reputation: 3829
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
Upvotes: 1
Views: 236
Reputation: 5999
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??
Bad parameter sniffing then:
http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/
SQL poor stored procedure execution plan performance - parameter sniffing
Parameter sniffing was bad in 2005, but better in 2008.
Upvotes: 4
Reputation: 58491
You union is selecting CalculatedMilestoneDate equal to NULL and not equal to Null.
This is redundant, the entire UNION can be removed by just removing the condition on CalculatedMilestoneDate from the where clause.
Other than that, you should verify that both databases have the same indexes defined.
-- 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)
Upvotes: 3
Reputation: 9381
If the schemas match then perhaps you are missing important indexes in the sql server 2005 instance. Try running the sql server tuning advisors and applying its index recommendations.
Upvotes: 0