April 7, 2008 at 7:47 am
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 :
April 7, 2008 at 8:19 am
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
April 7, 2008 at 8:21 am
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
April 7, 2008 at 8:56 am
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
April 7, 2008 at 1:45 pm
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
April 7, 2008 at 8:14 pm
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
Change is inevitable... Change for the better is not.
April 8, 2008 at 5:25 am
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
April 8, 2008 at 7:00 am
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
April 8, 2008 at 8:07 am
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
April 8, 2008 at 10:13 am
Im Using 2005 to display the plan, but my data its in 2000
April 8, 2008 at 11:07 am
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
April 8, 2008 at 1:20 pm
Thank you Tod your suggestion was apply and the query run faster than before.
April 8, 2008 at 1:36 pm
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
April 8, 2008 at 1:56 pm
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?
April 8, 2008 at 2:00 pm
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