Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2008
    Posts
    22

    Unanswered: Optimizing the query.

    Please find the code below:

    Code:
    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

  2. #2
    Join Date
    Jun 2008
    Posts
    22
    The code marked in red causes the query to execute for a long time.
    It takes around 7 seconds to execute.

  3. #3
    Join Date
    Jun 2008
    Posts
    22
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Using inline functions is bound to kill your performance.

    Can you explain in words what you're trying to achieve here?
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2008
    Posts
    22
    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

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by willfindavid
    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
    This is much easier to see if you wrap your code in [ code] tags. The more work respondents have to do the less help you will get.

  7. #7
    Join Date
    Jun 2008
    Posts
    22
    Code:
    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

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is the original code all left aligned? No indenting?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •