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

    Unanswered: Reducing the Logical Reads for a table in stored procedure.

    Hi,

    I'm running a stored procedure which has lots of tables and i have set the 'statistics io on' to track the logical reads and to reduce the execution time.

    A table 'WorkMgmtPlanLineItems' shows the below details.
    'WorkMgmtPlanLineItems'. Scan count 4764, logical reads 612954,

    I've created nonclustered indexes for important columns,then too it shows high logical read value.

    How to reduce the logical reads for this table?

    Willfin

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To provide you with an accurate answer, you must provide us with the all the necessary information to help.

    DDL of table, and stored procedure code please.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2008
    Posts
    22
    The Stored procedure code is as follows:

    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,',')
    )
    -- -- group by Line.PcTaskID

    )

  4. #4
    Join Date
    Jun 2008
    Posts
    22
    The logical reads for WorkMgmtPlanLineItems and WorkMgmtPlanTaskAllocations are high.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh crumbs.

    Is this query taking too long then?

    1) Not a performance issue but makes things easier - convert those joins to ANSI
    2) Create a table variable and populate that with the output of dbo.split(@ProcessPlanID,',') and INNER JOIN to that table (rather than the IN() nonsense)

    There's other stuff but that is a good start. Once you've made changes please repost, this time wrapping the code in code tags ([ code] and [ /code] minus the space) to make it easier to read.

  6. #6
    Join Date
    Jun 2008
    Posts
    22

    How to go about this execution plan..

    Hi,

    Can u explain how to go around this execution plan,
    i mean which column in the table is affecting the performance... etc


    Willfin
    Attached Thumbnails Attached Thumbnails xx.bmp  

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You already know that Clustered Index Seek on PCWorkProducts has 44% cost associated with it. How many rows are you returning? And what table your pLANId belongs to?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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