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

    Unanswered: Improving the performance of a query

    Code:
    select distinct Line.LineITemID, 17 'Error_Code', 'T' Error_Type                                                              
                                                                
    from                                                                                                             
     PCProcessPackages Pkg,
     PCProcessPackagePhases Phases,
     PCProcessPackageWorkflows Act,
     PCProcessPackageTasks Task,
     PCProcessPackageTaskWorkproducts taskWrkPrd,
     PCWorkProducts WrkPrd ,
     WorkMgmtPlanLineItems Line,
     WorkMgmtPlan Pln
    
     where
    
     Phases.ProcessPackageID = Pkg.ProcessPackageID
     and Act.PhaseID = Phases.PhaseID
     and Task.WorkflowID = Act.WorkflowID
     and taskWrkPrd.TaskID = Task.TaskID
     and WrkPrd.PCWorkProductID = taskWrkPrd.PCWorkProductID
     and taskWrkPrd.Type  ='O'
     and Pln.PlanID = Line.WorkMgmtPlanID
     and Pkg.ProliteProjecTID = Pln.ProliteProjectId
     and Task.TaskID  = Line.PCTaskID
     and ActualStartDate is not null
     and CurrentStatusID = 8
     and
     pLANId in 
     (
      select convert(Bigint,Items) from dbo.split(@ProcessPlanID,',')
      )
     and Convert(varchar(20),Task.TaskID ) + '~' + Convert(varchar(20),
    Line.WorkMgmtPlanID) not in
      (
       select
       Convert(varchar(20), Line.PcTaskID ) + '~' + Convert(varchar(20),
       Line.WorkMgmtPlanID)
        from
       WorkMgmtPlan Pln,
       WorkMgmtPlanLineItems Line,
       WorkMgmtPlanTaskAllocations Al,
       Artifacts Art
       where
       Pln.PlanID = Line.WorkMgmtPlanID
       and Al.PlanLineItemID = Line.LineItemID
       and Art.TaskAllocationID = Al.TaskAllocationID
       and
       pLANId in (
        select convert(Bigint,Items) from dbo.split(@ProcessPlanID,',')
        )
    
      )

  2. #2
    Join Date
    Jun 2008
    Posts
    22
    In the code displayed above if i comment the red coloured item,its compiling without any errors and the performance is also increased.
    The execution time has reduced from 5 seconds to 2 seconds.

    How this improvement in performance happens?

  3. #3
    Join Date
    Jun 2008
    Posts
    22
    The above query works even after commenting 'WorkMgmtPlanLineItems Line,' because 'WorkMgmtPlanLineItems Line,' is declared in the main select query at the top.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wow, you keep on bringing us really hiddeous SQL don't you!

    WorkMgmtPlanLineItems appears to be a table; so the danger with removing it is that your resultset may be different.

    Also, if you commented that line out you'd also have to get rid of other references to is; Ie
    Code:
    and Al.PlanLineItemID = Line.LineItemID
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you tell us what on earth this query is trying to do in simple steps; before you can optimise, first you must knowwhat you're trying to achieve.
    George
    Home | Blog

Posting Permissions

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