Improving the performance of a query

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)

Improving the performance of a query

Author

Topic

willfindavid
Starting Member

27 Posts

Posted - 2008-07-01 : 01:53:59

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,',')
)

)

willfindavid
Starting Member

27 Posts

Posted - 2008-07-01 : 01:54:26

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 01:55:34

quote:
Originally posted by willfindavid

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?

sorry you havent highlighted any part in your posted query.

willfindavid
Starting Member

27 Posts

Posted - 2008-07-01 : 01:56:14

--WorkMgmtPlanLineItems Line,
is the line commented and displayed in red colour.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 02:10:59

quote:
Originally posted by willfindavid

--WorkMgmtPlanLineItems Line,
is the line commented and displayed in red colour.

i dont think code will work if you comment this line. as you've used a column from this table in your select and also on your where statements it will throw an error column cannot be bound.

select
Convert(varchar(20), Line.PcTaskID ) + '~' + Convert(varchar(20),
Line.WorkMgmtPlanID)

&

where
Pln.PlanID = Line.WorkMgmtPlanID
and Al.PlanLineItemID = Line.LineItemID

willfindavid
Starting Member

27 Posts

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

It is working 'cause its using the "WorkMgmtPlanLineItems Line," which is declared in the main select statement above at the top of the query...

I need to have more understanding of how this query executes leading to reduction in execution time.

Willfin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 02:18:16

quote:
Originally posted by willfindavid

It is working 'cause its using the "WorkMgmtPlanLineItems Line," which is declared in the main select statement above at the top of the query...

I need to have more understanding of how this query executes leading to reduction in execution time.

Willfin

which table are columns ActualStartDate and CurrentStatusID coming from?I'm trying if you could reduce joins on main query.

willfindavid
Starting Member

27 Posts

Posted - 2008-07-01 : 02:37:09

They are coming from WorkMgmtPlanLineItems table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources