InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 05:37:09 GMT20http://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxThanks a lot guys for your effort helping me solving this issue. Removing all the functions from the where clause solve the problem. The query ran a lot faster now. I consider this case close .Wed, 09 Apr 2008 07:44:52 GMTStanley Pagenelhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxas the people posted before:I would also avoid the use of a funktion in the From or Where Cause.Especially in the case of the function is not scalar.Further you should use = to compare RES_UID with 'Interne'. The Query Analyzer handles this better.I would replace the From and Where Clause with[quote]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 INNER JOIN MSP_RESOURCES as Resources2 ON MSP_RESOURCES.RES_EUID = Resources2.RES_UID INNER JOIN MSP_TEXT_FIELDS ON Resources2.RES_UID = MSP_TEXT_FIELDS.TEXT_REF_UID AND MSP_TEXT_FIELDS.PROJ_ID = Resources2.PROJ_IDWHERE -- year(MSP_WEB_WORK.WWORK_START)=2008 and year(MSP_WEB_WORK.WWORK_FINISH)=2008 MSP_WEB_WORK.WWORK_START BETWEEN '1/1/2008' And '12/31/08 11:59:59 PM' AND (MSP_WEB_WORK.WWORK_TYPE = 1 OR MSP_WEB_WORK.WWORK_TYPE = 2)-- AND (dbo.GetInterneExterne(MSP_RESOURCES.RES_EUID) LIKE 'Interne') AND (MSP_TEXT_FIELDS.PROJ_ID = 1) AND (MSP_TEXT_FIELDS.TEXT_FIELD_ID = 205521546) AND Resources2.RES_UID = 'Interne'[/quote]Wed, 09 Apr 2008 05:33:26 GMTw.lengenfelderhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxShort of recreating your environment on 2005, or attaching that database to a 2005 server and upgrading it to compatibility level 90 , no, not that I know of. Neither of the above is a trivial task, so they're not an option. Even if you did either, you would have to spend some time re-optimizing, reorganizing, etc... just to make sure that the database performance is up to snuff.Tue, 08 Apr 2008 14:42:55 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxMatt is there anyway I can do that in a 2000 environment.Tue, 08 Apr 2008 14:00:44 GMTStanley Pagenelhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspx[quote][b]Stanley Pagenel (4/8/2008)[/b][hr]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[/quote]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.Tue, 08 Apr 2008 13:56:02 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxCan 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.ThanksTue, 08 Apr 2008 13:36:48 GMTStanley Pagenelhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxThank you Tod your suggestion was apply and the query run faster than before.Tue, 08 Apr 2008 13:20:47 GMTStanley Pagenelhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxThe 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 FifieldTue, 08 Apr 2008 11:07:29 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxIm Using 2005 to display the plan, but my data its in 2000Tue, 08 Apr 2008 10:13:19 GMTStanley Pagenelhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspx[quote][b]Stanley Pagenel (4/8/2008)[/b][hr]Msg 195, Level 15, State 5, Line 2'SHOWPLAN_XML' is not a recognized option.[/quote]Are you using SQL 2005 or 2000?Tue, 08 Apr 2008 08:07:12 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxAs mention on microsoft site I try to save the Execution plan as follow http://technet.microsoft.com/en-us/library/ms190646.aspxBut I received those errorsMsg 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,StanTue, 08 Apr 2008 07:00:22 GMTStanley Pagenelhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxHopefully 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.Tue, 08 Apr 2008 05:25:11 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspx[quote][b]Grant Fritchey (4/7/2008)[/b][hr]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.[/quote]Yep... and the GetInterneExterne function, as well.Mon, 07 Apr 2008 20:14:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxIf 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.Mon, 07 Apr 2008 13:45:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxGrant 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.ThanksMon, 07 Apr 2008 08:56:37 GMTStanley Pagenelhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxBy 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.Mon, 07 Apr 2008 08:21:50 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxPlease post the execution plan.Mon, 07 Apr 2008 08:19:40 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic480807-360-1.aspxHello 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_UIDWHERE 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 ONGOSET QUOTED_IDENTIFIER ONGOALTER function [dbo].[CalculateRealHours] (@StartDate datetime, @EndDate datetime, @nb_hour float)returns float asbegin/*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 @ActualHoursendfunction 2 definition :----------------------USE [ProjectServer]GO/****** Object: UserDefinedFunction [dbo].[GetInterneExterne] Script Date: 04/07/2008 09:22:29 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOALTER 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_IDWHERE (MSP_TEXT_FIELDS.PROJ_ID = 1) AND (MSP_TEXT_FIELDS.TEXT_FIELD_ID = 205521546) AND (MSP_RESOURCES.RES_UID = @RID)return @rENDfunction 3 definition :----------------------USE [ProjectServer]GO/****** Object: UserDefinedFunction [dbo].[GetMatricule] Script Date: 04/07/2008 09:23:37 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOALTER 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_IDWHERE (MSP_TEXT_FIELDS.PROJ_ID = 1) AND (MSP_TEXT_FIELDS.TEXT_FIELD_ID = 205521542) AND (MSP_RESOURCES.RES_UID = @RID)return @rEND-------------------PS : Let me know if you need the Result from the execution plan :Mon, 07 Apr 2008 07:47:36 GMTStanley Pagenel