SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimizing SQL Query


Optimizing SQL Query

Author
Message
Stanley Pagenel
Stanley Pagenel
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 166
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 :
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72557 Visits: 32911
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72557 Visits: 32911
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Stanley Pagenel
Stanley Pagenel
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 166
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 BYSad[Expr1043] ASC))
|--Compute Scalar(DEFINESad[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, MERGESad[MSP_WEB_RESOURCES].[WRES_ID], [MSP_WEB_RESOURCES].[RES_EUID])=([MSP_WEB_WORK].[WRES_ID], [MSP_RESOURCES].[RES_EUID]), RESIDUALSad[MSP_WEB_WORK].[WRES_ID]=[MSP_WEB_RESOURCES].[WRES_ID] AND [MSP_WEB_RESOURCES].[RE
|--Clustered Index Scan(OBJECTSad[ProjectServer].[dbo].[MSP_WEB_RESOURCES].[PK_MSP_WEB_RESOURCES_WRES_ID]), ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCESSad[MSP_WEB_ASSIGNMENTS].[TASK_UID], [MSP_WEB_PROJECTS].[PROJ_ID]) WITH PREFETCH)
|--Sort(ORDER BYSad[MSP_WEB_WORK].[WRES_ID] ASC, [MSP_RESOURCES].[RES_EUID] ASC))
| |--Nested Loops(Inner Join, OUTER REFERENCESSad[MSP_WEB_PROJECTS].[PROJ_ID]) WITH PREFETCH)
| |--Nested Loops(Inner Join, OUTER REFERENCESSad[MSP_WEB_ASSIGNMENTS].[WPROJ_ID]) WITH PREFETCH)
| | |--Bookmark Lookup(BOOKMARKSad[Bmk1002]), OBJECTSad[ProjectServer].[dbo].[MSP_WEB_ASSIGNMENTS]) WITH PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER REFERENCESSad[MSP_WEB_WORK].[WASSN_ID]) WITH PREFETCH)
| | | |--Bookmark Lookup(BOOKMARKSad[Bmk1000]), OBJECTSad[ProjectServer].[dbo].[MSP_WEB_WORK]) WITH PREFETCH)
| | | | |--Index Scan(OBJECTSad[ProjectServer].[dbo].[MSP_WEB_WORK].[PK_MSP_WEB_WORK_WASSN_ID_WWORK_START_WWORK_FINISH_WWORK_TYPE]), WHERESad(datepart(year, [MSP_WEB_WORK].[WWORK_START])=2008 AND datepart(year, [M
| | | |--Index Seek(OBJECTSad[ProjectServer].[dbo].[MSP_WEB_ASSIGNMENTS].[PK_MSP_WEB_ASSIGNMENTS_WASSN_ID]), SEEKSad[MSP_WEB_ASSIGNMENTS].[WASSN_ID]=[MSP_WEB_WORK].[WASSN_ID]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECTSad[ProjectServer].[dbo].[MSP_WEB_PROJECTS].[PK_MSP_WEB_PROJECTS_WPROJ_ID]), SEEKSad[MSP_WEB_PROJECTS].[WPROJ_ID]=[MSP_WEB_ASSIGNMENTS].[WPROJ_ID]) ORDERED FORWARD)
| |--Filter(WHERESadlike([dbo].[GetInterneExterne]([MSP_RESOURCES].[RES_EUID]), 'Interne', NULL)))
| |--Clustered Index Seek(OBJECTSad[ProjectServer].[dbo].[MSP_RESOURCES].[I_MSP_RESOURCES]), SEEKSad[MSP_RESOURCES].[PROJ_ID]=[MSP_WEB_PROJECTS].[PROJ_ID]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECTSad[ProjectServer].[dbo].[MSP_TASKS].[I_MSP_TASKS]), SEEKSad[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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)

Group: General Forum Members
Points: 152311 Visits: 45881
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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: General Forum Members
Points: 151648 Visits: 41740
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72557 Visits: 32911
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Stanley Pagenel
Stanley Pagenel
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 166
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)SSC Guru (152K reputation)

Group: General Forum Members
Points: 152311 Visits: 45881
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


Stanley Pagenel
Stanley Pagenel
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 166
Im Using 2005 to display the plan, but my data its in 2000
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search