CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008
David Ward
David Ward

Reputation: 3829

SQL Stored Procedure Performance Fine on SQL2008 but Awful on SQL2005

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

Answers (3)

Meff
Meff

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

Lieven Keersmaekers
Lieven Keersmaekers

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

Brian Scott
Brian Scott

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

Related Questions