Optimizing a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Optimizing a query

Author

Topic

willfindavid
Starting Member

27 Posts

Posted - 2008-06-30 : 06:14:39

select
0 DeviationID,
PlanID ,
PlanName ,
ErrorDeviationCategoryID ,
ErrorDeviationCategoryName ,
IsWarningOrInformation ,
isnull(LineItemID, '') 'LineItemID' ,
ProjectErrorID,
TaskName ,
Predecessortask ,
ErrorID ,
Error_Type ,
ErrorCode ,
ErrorDesc ,
ErrorSeverityID ,
ErrorSeverityName ,
case
when
ltrim(rtrim(Error_Type)) = 'T'
Then
dbo.PC_fnGetDeviationReportFields(LineItemID, 'U')
Else
UserName
End 'UserName',

case
when
Error_Type = 'T'
Then
dbo.PC_fnGetDeviationReportFields(LineItemID, 'D')
Else
DeliveryRole
End DeliveryRole ,
case
when
Error_Type = 'T'
Then
dbo.PC_fnGetDeviationReportFields(LineItemID, 'R')
Else
RequiredRole
End RequiredRole,DeviationRole ,
Reason ,
Status,
(
select
ErrorStatusDesc
from ErrorStatus a where a.ErrorStatusID = Status) 'StatusName',
isnull(UserID,'') UserID ,
isnull( dbo.PC_fnGetDeviationProcessPackage ( @ProjecTID, PlanID) , '') 'ProcessPackage'

from @ProjectError
where
ltrim(rtrim(ProjectErrorID)) +'~'+ ltrim(rtrim(UserID))
not in (
select
ltrim(rtrim(ProjectErrorID)) +'~'+ ltrim(rtrim(UserID))
from @DeviationReport_Saved
)
and planid in (
select Items from dbo.split(@ProcessPlanID,',')
)
desc
order by PlanID, ErrorCode desc

willfindavid
Starting Member

27 Posts

Posted - 2008-06-30 : 06:15:13

The code marked in red causes the query to execute for a long time.
It takes around 7 seconds to execute.

willfindavid
Starting Member

27 Posts

Posted - 2008-06-30 : 06:15:46

The PC_fnGetDeviationReportFields function is as follows:

ALTER function [dbo].[PC_fnGetDeviationReportFields]
(
@LineItemID bigint,
@Data varchar(2)
)
Returns varchar(2000)
As
Begin

declare @Result as varchar(2000)
set @Result = ''
if (@Data ='U') -- User Name
Begin
Select
@Result =
case
when
@Result = ''
then
ltrim(rtrim(mas_user.s_useR_name))
else
@Result + ';'+ ltrim(rtrim(mas_user.s_useR_name))
End
from
WOrkMgmtPlanLineItems

Inner Join
WorkMgmtPlanTaskAllocations
On
WorkMgmtPlanTaskAllocations.PlanLineItemID = WOrkMgmtPlanLineItems.LineItemID
Inner Join
Mas_user
On
mas_user.s_user_id = WorkMgmtPlanTaskAllocations.ResourceID
where
WOrkMgmtPlanLineItems.LineItemID = @LineItemID
and
WorkMgmtPlanTaskAllocations.IsActive = '1'
End
Else if @Data = 'D'
Begin

Select
@Result = @Result +
case
when @Result = ''
then ltrim(rtrim(RoleName))
else ';'+ ltrim(rtrim(RoleName))
end
from
(

Select
distinct ltrim(rtrim(RoleName))'RoleName'
from
EPFRoles
Inner Join
EPFTaskRoles
On
EPFRoles.RoleID = EPFTaskRoles.RoleID
Inner Join
PCprocessPAckageTasks
On
EPFTaskRoles.ProceessPackageTaskID = PCprocessPAckageTasks.EPFProcessPackageTaskID
Inner Join
WorkMgmtPlanLineItems
On
PCprocessPAckageTasks.TaskID = WorkMgmtPlanLineItems.PCTaskID
where
LineItemID = @LineItemID
) A
End
Else if @Data = 'R'
Begin

Select
@Result =
case
when
@Result = ''
then
ltrim(rtrim(RoleName))
else
@Result + ';'+ ltrim(rtrim(RoleName))
End
From
(
Select
distinct ltrim(rtrim(EPFRoles.RoleName)) 'RoleName'
from
WOrkMgmtPlanLineItems
Inner Join
WorkMgmtPlanTaskAllocations
On
WorkMgmtPlanTaskAllocations.PlanLineItemID = WOrkMgmtPlanLineItems.LineItemID
Inner Join
trn_user_allocation
On
trn_user_allocation.s_user_id = WorkMgmtPlanTaskAllocations.ResourceID
Inner Join
EPFRoles
On
trn_user_allocation.i_EPF_role_id = EPFRoles.RoleID
where
WOrkMgmtPlanLineItems.LineItemID = @LineItemID
and
WorkMgmtPlanTaskAllocations.IsActive = '1'
) A

End

return @Result
End

willfindavid
Starting Member

27 Posts

Posted - 2008-06-30 : 07:12:08

In the function above, I'm selecting values from tables

if (@Data ='U') i'm doing one select
else if(if @Data = 'D') i'm doing one select
else if(@Data = 'R') i'm doing one select

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-07-02 : 01:52:15

1)Is it absolutely necessary to have the function ? For example , if you embedded the function into the code , would the query run quicker. My guess is that it would - because the optimizer would be able to see the full code and possibly decide on a better query plan
2)Can the function information be held in a view?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources