Optimizing SQL Query

  • Hello I wonder if someone can help me inprove the speed of the given query

    --- Primary Query

    -----------------

    SELECT MSP_RESOURCES.RES_EUID,MSP_WEB_PROJECTS.PROJ_NAME AS ProjName, MSP_TASKS.TASK_NAME AS TaskName, MSP_WEB_RESOURCES.RES_NAME AS ResName,

    MSP_WEB_WORK.WWORK_VALUE / 60000 AS [Work],

    --dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000)) as actual_work,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_all,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_all,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='January'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_jan,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='January'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_jan,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='february'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_feb,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='february'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_feb,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='march'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_mar,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='march'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_mar,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='April'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_apr,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='April'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_apr,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='May'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_may,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='May'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_may,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='June'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_jun,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='June'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_jun,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='July'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_jul,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='July'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_jul,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='August'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_aug,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='August'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_aug,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='September'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_sep,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='September'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_sep,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='November'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_Nov,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='November'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_Nov,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=1 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='December'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS regular_Dec,

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE=2 and DATENAME(month, MSP_WEB_WORK.WWORK_START)='December'

    THEN dbo.CalculateRealHours(MSP_WEB_WORK.WWORK_START,MSP_WEB_WORK.WWORK_FINISH,(MSP_WEB_WORK.WWORK_VALUE / 60000))

    ELSE 0

    END AS overtime_Dec,

    MSP_WEB_WORK.WWORK_START AS Start, MSP_WEB_WORK.WWORK_FINISH AS Finish,

    MSP_WEB_WORK.WWORK_UPDATE_STATUS AS UpdateStatus, MSP_WEB_WORK.WWORK_APPROVAL_STATUS AS SupervisorUpdate,

    MSP_WEB_WORK.WWORK_TYPE AS WorkType,

    dbo.GetTaskType(MSP_WEB_PROJECTS.PROJ_ID, MSP_WEB_ASSIGNMENTS.TASK_UID) AS TaskType, dbo.GetSite(MSP_RESOURCES.RES_EUID)

    AS Site, DATENAME(month, MSP_WEB_WORK.WWORK_START) AS 'Month Name',

    DATENAME(weekday, MSP_WEB_WORK.WWORK_START) AS 'Day Name',

    DATENAME(year, MSP_WEB_WORK.WWORK_START) AS 'Year Name',

    DATENAME(quarter, MSP_WEB_WORK.WWORK_START) AS 'Quarter Name'

    FROM MSP_WEB_WORK INNER JOIN

    MSP_WEB_ASSIGNMENTS ON MSP_WEB_WORK.WASSN_ID = MSP_WEB_ASSIGNMENTS.WASSN_ID INNER JOIN

    MSP_WEB_PROJECTS ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN

    MSP_WEB_RESOURCES ON MSP_WEB_WORK.WRES_ID = MSP_WEB_RESOURCES.WRES_ID INNER JOIN

    MSP_RESOURCES ON MSP_WEB_PROJECTS.PROJ_ID = MSP_RESOURCES.PROJ_ID AND

    MSP_WEB_RESOURCES.RES_EUID = MSP_RESOURCES.RES_EUID INNER JOIN

    MSP_TASKS ON MSP_WEB_PROJECTS.PROJ_ID = MSP_TASKS.PROJ_ID AND MSP_WEB_ASSIGNMENTS.TASK_UID = MSP_TASKS.TASK_UID

    WHERE year(MSP_WEB_WORK.WWORK_START)=2008 and year(MSP_WEB_WORK.WWORK_FINISH)=2008

    AND (MSP_WEB_WORK.WWORK_TYPE = 1 OR MSP_WEB_WORK.WWORK_TYPE = 2)

    AND (dbo.GetInterneExterne(MSP_RESOURCES.RES_EUID) LIKE 'Interne')

    ORDER BY dbo.GetMatricule(MSP_RESOURCES.RES_EUID)

    -------------------------------------------------------------------

    function 1 definition :

    ----------------------

    USE [ProjectServer]

    GO

    /****** Object: UserDefinedFunction [dbo].[CalculateRealHours] Script Date: 04/07/2008 09:19:31 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER function [dbo].[CalculateRealHours] (@StartDate datetime, @EndDate datetime, @nb_hour float)

    returns float

    as

    begin

    /*

    Description:

    Function designed to calculate the number of business days

    between two dates.

    */

    declare

    @ActualHours float

    if DATEDIFF(day, @StartDate, @EndDate) = 0

    return @nb_hour

    else

    BEGIN

    SELECT @ActualHours = (DATEDIFF(day, @StartDate, @EndDate)+1)* @nb_hour

    END

    return @ActualHours

    end

    function 2 definition :

    ----------------------

    USE [ProjectServer]

    GO

    /****** Object: UserDefinedFunction [dbo].[GetInterneExterne] Script Date: 04/07/2008 09:22:29 ******/

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ALTER FUNCTION [dbo].[GetInterneExterne] (@RID int)

    RETURNS varchar(50)

    AS

    BEGIN

    declare @r varchar(50)

    SELECT @r= MSP_TEXT_FIELDS.TEXT_VALUE

    FROM MSP_TEXT_FIELDS INNER JOIN

    MSP_RESOURCES ON MSP_TEXT_FIELDS.TEXT_REF_UID = MSP_RESOURCES.RES_UID AND

    MSP_TEXT_FIELDS.PROJ_ID = MSP_RESOURCES.PROJ_ID

    WHERE (MSP_TEXT_FIELDS.PROJ_ID = 1) AND (MSP_TEXT_FIELDS.TEXT_FIELD_ID = 205521546)

    AND (MSP_RESOURCES.RES_UID = @RID)

    return @r

    END

    function 3 definition :

    ----------------------

    USE [ProjectServer]

    GO

    /****** Object: UserDefinedFunction [dbo].[GetMatricule] Script Date: 04/07/2008 09:23:37 ******/

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ALTER FUNCTION [dbo].[GetMatricule] (@RID int)

    RETURNS varchar(50)

    AS

    BEGIN

    declare @r varchar(50)

    SELECT @r= MSP_TEXT_FIELDS.TEXT_VALUE

    FROM MSP_TEXT_FIELDS INNER JOIN

    MSP_RESOURCES ON MSP_TEXT_FIELDS.TEXT_REF_UID = MSP_RESOURCES.RES_UID AND

    MSP_TEXT_FIELDS.PROJ_ID = MSP_RESOURCES.PROJ_ID

    WHERE (MSP_TEXT_FIELDS.PROJ_ID = 1) AND (MSP_TEXT_FIELDS.TEXT_FIELD_ID = 205521542)

    AND (MSP_RESOURCES.RES_UID = @RID)

    return @r

    END

    -------------------

    PS : Let me know if you need the Result from the execution plan :

  • Please post the execution plan.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • By the way, from what I can see, GetMatricule is going to force the query to function like a cursor, running this select statement for each row in the result set. That alone could lead to major peformance problems.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant here is the Query execution plan :

    StmtText

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    SELECT MSP_RESOURCES.RES_EUID,MSP_WEB_PROJECTS.PROJ_NAME AS ProjName, MSP_TASKS.TASK_NAME AS TaskName, MSP_WEB_RESOURCES.RES_NAME AS ResName,

    MSP_WEB_WORK.WWORK_VALUE / 60000 AS [Work],

    CASE

    WHEN MSP_WEB_WORK.WWORK_TYPE

    (1 row(s) affected)

    StmtText

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    |--Sort(ORDER BY:([Expr1043] ASC))

    |--Compute Scalar(DEFINE:([Expr1012]=[MSP_WEB_WORK].[WWORK_VALUE]/60000, [Expr1013]=If ([MSP_WEB_WORK].[WWORK_TYPE]=1) then [dbo].[CalculateRealHours]([MSP_WEB_WORK].[WWORK_START], [MSP_WEB_WORK].[WWORK_FINISH], Convert([MSP_WEB_WORK].[WWORK_VALUE]/

    |--Merge Join(Inner Join, MERGE:([MSP_WEB_RESOURCES].[WRES_ID], [MSP_WEB_RESOURCES].[RES_EUID])=([MSP_WEB_WORK].[WRES_ID], [MSP_RESOURCES].[RES_EUID]), RESIDUAL:([MSP_WEB_WORK].[WRES_ID]=[MSP_WEB_RESOURCES].[WRES_ID] AND [MSP_WEB_RESOURCES].[RE

    |--Clustered Index Scan(OBJECT:([ProjectServer].[dbo].[MSP_WEB_RESOURCES].[PK_MSP_WEB_RESOURCES_WRES_ID]), ORDERED FORWARD)

    |--Nested Loops(Inner Join, OUTER REFERENCES:([MSP_WEB_ASSIGNMENTS].[TASK_UID], [MSP_WEB_PROJECTS].[PROJ_ID]) WITH PREFETCH)

    |--Sort(ORDER BY:([MSP_WEB_WORK].[WRES_ID] ASC, [MSP_RESOURCES].[RES_EUID] ASC))

    | |--Nested Loops(Inner Join, OUTER REFERENCES:([MSP_WEB_PROJECTS].[PROJ_ID]) WITH PREFETCH)

    | |--Nested Loops(Inner Join, OUTER REFERENCES:([MSP_WEB_ASSIGNMENTS].[WPROJ_ID]) WITH PREFETCH)

    | | |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([ProjectServer].[dbo].[MSP_WEB_ASSIGNMENTS]) WITH PREFETCH)

    | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([MSP_WEB_WORK].[WASSN_ID]) WITH PREFETCH)

    | | | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([ProjectServer].[dbo].[MSP_WEB_WORK]) WITH PREFETCH)

    | | | | |--Index Scan(OBJECT:([ProjectServer].[dbo].[MSP_WEB_WORK].[PK_MSP_WEB_WORK_WASSN_ID_WWORK_START_WWORK_FINISH_WWORK_TYPE]), WHERE:((datepart(year, [MSP_WEB_WORK].[WWORK_START])=2008 AND datepart(year, [M

    | | | |--Index Seek(OBJECT:([ProjectServer].[dbo].[MSP_WEB_ASSIGNMENTS].[PK_MSP_WEB_ASSIGNMENTS_WASSN_ID]), SEEK:([MSP_WEB_ASSIGNMENTS].[WASSN_ID]=[MSP_WEB_WORK].[WASSN_ID]) ORDERED FORWARD)

    | | |--Clustered Index Seek(OBJECT:([ProjectServer].[dbo].[MSP_WEB_PROJECTS].[PK_MSP_WEB_PROJECTS_WPROJ_ID]), SEEK:([MSP_WEB_PROJECTS].[WPROJ_ID]=[MSP_WEB_ASSIGNMENTS].[WPROJ_ID]) ORDERED FORWARD)

    | |--Filter(WHERE:(like([dbo].[GetInterneExterne]([MSP_RESOURCES].[RES_EUID]), 'Interne', NULL)))

    | |--Clustered Index Seek(OBJECT:([ProjectServer].[dbo].[MSP_RESOURCES].[I_MSP_RESOURCES]), SEEK:([MSP_RESOURCES].[PROJ_ID]=[MSP_WEB_PROJECTS].[PROJ_ID]) ORDERED FORWARD)

    |--Clustered Index Seek(OBJECT:([ProjectServer].[dbo].[MSP_TASKS].[I_MSP_TASKS]), SEEK:([MSP_TASKS].[PROJ_ID]=[MSP_WEB_PROJECTS].[PROJ_ID] AND [MSP_TASKS].[TASK_UID]=[MSP_WEB_ASSIGNMENTS].[TASK_UID]) ORDERED FORWARD)

    (17 row(s) affected)

    I tried remoiving the order by function but that did not solve the problem.

    Thanks

  • If you're running SQL 2005 (as I assume you are, based on the forum this is posted in), could you please save the exec plan as a .sqlplan file, zip it and attach. It's much easier to read than the text plan is.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Grant Fritchey (4/7/2008)


    By the way, from what I can see, GetMatricule is going to force the query to function like a cursor, running this select statement for each row in the result set. That alone could lead to major peformance problems.

    Yep... and the GetInterneExterne function, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hopefully you can post the xml as an attachment like Gail says.

    Just looking through the text plan, I see a clustered index scan on PK_MSP_WEB_RESOURCES_WRES_ID which could be a cause for concern, depending on how much of the overall cost it represents. You've also got a index scan, on PK_MSP_WEB_WORK_WASSN_ID_WWORK_START_WWORK_FINISH_WWORK_TYPE, followed by two bookmark lookups. These could also be a concern, depending on cost. The index scan seems to be caused by the functions being used against the columns as part of the where clause. You've got an extra sort inside the query that's reording the data for use in a join, again, that might add to the time. It's hard to say which of these is causing the problems. You've also got two functions, GetTaskType & GetSite, that could be causing the same problem that Jeff pointed out (I think the example he used is better than the one I used).

    That's what I can see, but I can't tell you which of these areas to concentrate on since the output from the text query plan is missing so much data. Post the XML and better choices & information can be provided.

    On a guess, the scan against MSP_WEB_RESOURCES is being caused by the InterneExterne function and that's the issue. It's just a guess though.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • As mention on microsoft site I try to save the Execution plan as follow

    http://technet.microsoft.com/en-us/library/ms190646.aspx

    But I received those errors

    Msg 195, Level 15, State 5, Line 2

    'SHOWPLAN_XML' is not a recognized option.

    Msg 195, Level 15, State 8, Line 1

    'XML' is not a recognized statistics option.

    Msg 195, Level 15, State 5, Line 1

    'SHOWPLAN_XML' is not a recognized option.

    , do you have any Idea?? Or do you no another way I can save the exec plan??

    Thanks,

    Stan

  • Stanley Pagenel (4/8/2008)


    Msg 195, Level 15, State 5, Line 2

    'SHOWPLAN_XML' is not a recognized option.

    Are you using SQL 2005 or 2000?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Im Using 2005 to display the plan, but my data its in 2000

  • The first thing I noticed was that you're using a function as the first part of your WHERE clause. This makes it difficult for the optimizer to us an index. Off the top of my head I would index MSP_WEB_WORK.WWORK_START and MSP_WEB_WORK.WWORK_FINISH in the table and change the WHERE to:

    MSP_WEB_WORK.WWORK_START BETWEEN '1/1/2008'

    And '12/31/08 11:59:59 PM'

    Do the same for the FINISH column.

    It seems that you only want records in 2008 anyhow.

    You may get a performance improvement if the query weeds out a lot of rows before applying other criteria.

    Todd Fifield

  • Thank you Tod your suggestion was apply and the query run faster than before.

  • Can someone help me I would like to save the execution plan using SQL management studio. But I'm still having the error telling me that :

    Msg 195, Level 15, State 5, Line 1

    'SHOWPLAN_XML' is not a recognized option.

    Msg 195, Level 15, State 8, Line 1

    'XML' is not a recognized statistics option.

    Thanks

  • Stanley Pagenel (4/8/2008)


    Can someone help me I would like to save the execution plan using SQL management studio. But I'm still having the error telling me that :

    Msg 195, Level 15, State 5, Line 1

    'SHOWPLAN_XML' is not a recognized option.

    Msg 195, Level 15, State 8, Line 1

    'XML' is not a recognized statistics option.

    Thanks

    SET SHOWPLAN_XML is a SQL Server 2005-specific option (or rather - introduced in 2005 and later versions). It needs to be run against a SQL 2005 database engine. Your data is running against a SQL 2000 engine, so that setting doesn't exist.

    Your options would be SHOWPLAN_TEXT or SHOWPLAN_ALL.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt is there anyway I can do that in a 2000 environment.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply