Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Optimizing SQL Query Expand / Collapse
Author
Message
Posted Monday, April 07, 2008 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:04 AM
Points: 16, Visits: 96
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 :



Post #480807
Posted Monday, April 07, 2008 8:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 14,802, Visits: 27,275
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #480861
Posted Monday, April 07, 2008 8:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 14,802, Visits: 27,275
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #480863
Posted Monday, April 07, 2008 8:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:04 AM
Points: 16, Visits: 96
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(WHERElike([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
Post #480909
Posted Monday, April 07, 2008 1:45 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #481124
Posted Monday, April 07, 2008 8:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #481262
Posted Tuesday, April 08, 2008 5:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 14,802, Visits: 27,275
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #481423
Posted Tuesday, April 08, 2008 7:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:04 AM
Points: 16, Visits: 96
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
Post #481496
Posted Tuesday, April 08, 2008 8:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #481564
Posted Tuesday, April 08, 2008 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:04 AM
Points: 16, Visits: 96
Im Using 2005 to display the plan, but my data its in 2000
Post #481710
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse