Performance Problem

Hi,
This is query that is causing problem
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
WHERE WO.AssignedTo = 1020
AND StateMaster.IsInDashboard = 1
1. WorkOrder table is master table which consists of 155986 rows.
2. Task table is the child table refering to workorder (Id) which is
having 516060 rows.
3. Statemaster is the master table consists of about 500 rows.
Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
the condition StateMaster.IsInDashboard = 1 the result set
will be minimized to 30 rows.
For executing the above query it is taking 1.7 sec.
This is the execution Plan i got when i run this query
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
| |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
| | |--Index
S(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
| |--Clustered Index
S(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
SEEK:([StateMaster].[Id]=[WO].[StatusId]),
WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
|--Index
S(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)
I am not understanding why it is doing bookmark lookup on workorder
table when i joined StatusId column with Id column in statemaster table
and checking the condition Statemaster.IsinDashboard = 1.
These are the indexes we have on these tables.
1. In Task table "Id" is the primary Key and it is having
non-clustered index on workorderid
2. In Workorder table "Id" is the primary Key and it is having
non-clustered index on statusid
3. In Statemaster table "Id" is the primary Key
Could anyone help me out why the bookmark lookup is happening because
bookmark lookup on workorder table is taking above 95% of the total
query time.
Regards,
Ramnadh.
*** Sent via Developersdex http://www.examnotes.net ***I'd try creating an indexed view with the "StateMaster.IsInDashboard = 1"
condition on the dbo.StateMaster table.
I.e.:
create view <view name>
with schemabinding
as
select dbo.StateMaster.Id
,<column list>
from dbo.StateMaster
where (dbo.StateMaster.IsInDashboard = 1)
go
create unique clustered index <index name>
on <view name>
(
Id
)
go
Also consider using the READPAST hint instead of the notorious NOCOUNT hint.
Look up the details in Books Online.
ML
http://milambda.blogspot.com/|||> I am not understanding why it is doing bookmark lookup on workorder
> table when i joined StatusId column with Id column in statemaster table
> and checking the condition Statemaster.IsinDashboard = 1.
Does the StatusId column in WorkOrder table have an index?
"ramnadh nalluri" <ramnadh_nalluri@.semanticspace.com> wrote in message
news:e5p573KLGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Hi,
> This is query that is causing problem
> SELECT T.Id AS TaskId
> FROM dbo.Task T (NOLOCK)
> INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
> INNER JOIN dbo.StateMaster (NOLOCK) ON StateMaster.Id = WO.StatusId
> WHERE WO.AssignedTo = 1020
> AND StateMaster.IsInDashboard = 1
> 1. WorkOrder table is master table which consists of 155986 rows.
> 2. Task table is the child table refering to workorder (Id) which is
> having 516060 rows.
> 3. Statemaster is the master table consists of about 500 rows.
> Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows with
> the condition StateMaster.IsInDashboard = 1 the result set
> will be minimized to 30 rows.
> For executing the above query it is taking 1.7 sec.
>
> This is the execution Plan i got when i run this query
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id]) WITH
> PREFETCH)
> |--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[StatusId]))
> | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
> OBJECT:([Viper63].[dbo].[WorkOrder] AS [WO]) WITH PREFETCH)
> | | |--Index
> S(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
> SEEK:([WO].[AssignedTo]=[@.ResourceId]) ORDERED FORWARD)
> | |--Clustered Index
> S(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
> SEEK:([StateMaster].[Id]=[WO].[StatusId]),
> WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
> |--Index
> S(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
> SEEK:([T].[WorkOrderId]=[WO].[Id]) ORDERED FORWARD)
>
> I am not understanding why it is doing bookmark lookup on workorder
> table when i joined StatusId column with Id column in statemaster table
> and checking the condition Statemaster.IsinDashboard = 1.
> These are the indexes we have on these tables.
> 1. In Task table "Id" is the primary Key and it is having
> non-clustered index on workorderid
> 2. In Workorder table "Id" is the primary Key and it is having
> non-clustered index on statusid
> 3. In Statemaster table "Id" is the primary Key
> Could anyone help me out why the bookmark lookup is happening because
> bookmark lookup on workorder table is taking above 95% of the total
> query time.
> Regards,
> Ramnadh.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||> Totally the condition WO.AssigendTo = 1020 satisfies 1042 rows
Since you have no index on AssigendTo, indexes can't be used to satisfy this
predicate and I suspect IsinDashboard isn't very selective. I suggest you
create a composite nonclustered index on AssigendTo and IsinDashboard.
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFLTGNLLGHA.2276@.TK2MSFTNGP15.phx.gbl...
> Does the StatusId column in WorkOrder table have an index?
>
> "ramnadh nalluri" <ramnadh_nalluri@.semanticspace.com> wrote in message
> news:e5p573KLGHA.1424@.TK2MSFTNGP12.phx.gbl...
>|||Yep,Dan, I have missed that AssigendTo column has no index.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uhRp1RLLGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Since you have no index on AssigendTo, indexes can't be used to satisfy
> this predicate and I suspect IsinDashboard isn't very selective. I
> suggest you create a composite nonclustered index on AssigendTo and
> IsinDashboard.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eFLTGNLLGHA.2276@.TK2MSFTNGP15.phx.gbl...
>|||I had defined a view by giving the query
create view taskview with schemabinding as
select dbo.StateMaster.Id
, IsTerminal
from dbo.StateMaster
where StateMaster.IsInDashboard = 1
and statemaster.isterminal = 0
and i had defined the clustered index on Id column of the view.it worked
fine and it was reduced to 400 ms.
1) How does the optimizer avoided the bookmark lookup by creating a view
and defining an index on the Id column .
2) i am having another problem when i added another table in the join
SELECT T.Id AS TaskId FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN taskview (NOLOCK) ON taskview.Id = WO.StatusId
inner join objectdetail OD(nolock) on wo.id = OD.objectid
WHERE WO.AssignedTo = 1020
and taskview.isterminal = 0
and OD.objecttypeid = 10
OR ( WO.AssignedTo = 1020 AND ISNULL(taskview.IsTerminal,0) = 0
AND( CONVERT(CHAR(8),'12/01/2005',112) <= CONVERT(CHAR(8),
DATEADD(DD,1,OD.LastStatusUpdDt) , 112) )
)
OR ( WO.AssignedTo = 1020 AND ISNULL(taskview.IsTerminal,0) = 1
AND( CONVERT(CHAR(8),'12/01/2005',112) <= CONVERT(CHAR(8) ,
OD.LastStatusUpdDt , 112) )
)
(ObjectTypeId,ObjectId) is the primary key on ObjectDetail table.It
consists of around 228313 rows. The condition Objecttypeid = 10 satifies
around 156000 rows.
It was taking above 3 sec for retrieving the 30 rows. The execution plan
is showing as below
|--Nested Loops(Inner Join, OUTER REFERENCES:([OD].[ObjectId]) WITH
PREFETCH)
|--Hash Match(Inner Join, HASH:([WO].[Id])=([OD].[ObjectId]),
RESIDUAL:(((Convert([taskview].[IsTerminal])=0 AND
[OD].[ObjectTypeId]=10) OR (Convert([taskview].[IsTerminal])=0 AND
'12/01/20'<=Convert(dateadd(day, 1, [OD].[LastStatusUpdDt])))) OR (Co
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([WO].[StatusId]))
| | |--Hash Match(Inner Join, HASH:([WO].[Id])=([WO].[Id]))
| | | |--Index
S(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=1020) ORDERED FORWARD)
| | | |--Index
Scan(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder_StatusId] AS
[WO]))
| | |--Clustered Index
S(OBJECT:([Viper63].[dbo].[taskview].[taskid]),
SEEK:([taskview].[Id]=[WO].[StatusId]),
WHERE:(Convert([taskview].[IsTerminal])=0 OR
Convert([taskview].[IsTerminal])=1) ORDERED FORWARD)
| |--Clustered Index
Scan(OBJECT:([Viper63].[dbo].[ObjectDetail].[PK_ObjectDetail] AS [OD]),
WHERE:(([OD].[ObjectTypeId]=10 OR '12/01/2005'<=Convert(dateadd(day, 1,
[OD].[LastStatusUpdDt]))) OR
'12/01/2005'<=Convert([OD].[LastStatusUpdDt])))
|--Index
S(OBJECT:([Viper63].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[OD].[ObjectId]) ORDERED FORWARD)
It was doing clustered Index scan on above 2 million rows of the
ObjectDetail table which is taking above 58% of the total query time.
I had defined Non-Clustered Index on "LastStatusUpdDt" column in the
Objectdetail table. It reduced to around 2 sec. It is showing the same
execution plan but now Clustered Index s on Task table and the first
hash match join are taking most of the query time. I am not getting why
the hashmatch join and clustered index s is taking much time in
executing the query.
Thanks a lot for the help.
*** Sent via Developersdex http://www.examnotes.net ***|||A few basic suggestions:
- where status columns are used (e.g. in your case "isTerminal",
"IsInDashboard") and values have low selectivity, consider adding indexed
views for each status value (as I've illustrated in a previous post). This
will improve performance of queries where a specific value for this status
column is used as a restrictive condition;
- avoid using functions in conditions (such as the ISNULL and CONVERT in
your where clause), especially if other methods can be used, since functions
cause scans, wile other methods enable use of indexes;
- avoid complex OR conditions - try using UNION - e.g.:
select ... from
where <one set of conditions>
union --all -- use all or not - depending on actual requirements (test,
test, test...)
select ... from
where <other set of conditions>
As Uri and Dan suggested - put an index on the AssignedTo column. A
nonclustered would do.
ML
http://milambda.blogspot.com/|||Thanks for the information. I left out the basic concepts of using
functions in the where clause which will affect the performance. thanks
for reminding me. i will check by removing the functions which will
enable using of indexes by engine and will check by replacing OR
condition with the union also in the query which will affect performance
badly.
Sorry Uri and Dan for not giving the complete information. there is a
non-clustered non-unique index on assignedto column on the workorder
table.
*** Sent via Developersdex http://www.examnotes.net ***|||I had changed the query by removing usage of functions.
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN taskview ON taskview.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD (NOLOCK) ON OD.ObjectId = WO.Id AND
OD.ObjectTypeId = 10
WHERE ( WO.AssignedTo = 1020 and ((taskview.IsTerminal = 1 AND
@.seldate <= OD.LastStatusUpdDt )
or (taskview.IsTerminal = 0 AND @.seldate <=
OD.LastStatusUpdDt+1 )
)
)
Replaced OR with the UNION using below query
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN TaskView ON TaskView.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD (NOLOCK) ON OD.ObjectId = WO.Id AND
OD.ObjectTypeId = 10
WHERE WO.AssignedTo = 1020
AND Taskview.IsTerminal = 0
AND @.seldate <= OD.LastStatusUpdDt + 1
UNION
SELECT T.Id AS TaskId
FROM dbo.Task T (NOLOCK)
INNER JOIN dbo.WorkOrder WO (NOLOCK) ON T.WorkOrderId = WO.Id
INNER JOIN TaskView ON TaskView.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD (NOLOCK) ON OD.ObjectId = WO.Id AND
OD.ObjectTypeId = 10
WHERE WO.AssignedTo = 1020
AND taskview.IsTerminal = 1
AND @.seldate <= OD.LastStatusUpdDt
i replaced the OR condition with UNION, but i am seeing same execution
plan using by optimizer.below is the execution plan generated
|--Nested Loops(Inner Join, OUTER REFERENCES:([OD].[ObjectId]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([WO].[Id],
[TASKVIEW].[isterminal]) WITH PREFETCH)
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([WO].[StatusId]))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([Changeware].[dbo].[WorkOrder] AS [WO]))
| | | |--Index
S(OBJECT:([Changeware].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=1020) ORDERED FORWARD)
| | |--Clustered Index
S(OBJECT:([Changeware].[dbo].[TASKVIEW].[taskindex]),
SEEK:([TASKVIEW].[id]=[WO].[StatusId]),
WHERE:(Convert([TASKVIEW].[isterminal])=1 OR
Convert([TASKVIEW].[isterminal])=0) ORDERED FORWARD)
| |--Clustered Index
S(OBJECT:([Changeware].[dbo].[ObjectDetail].[PK_ObjectDetail] AS
[OD]), SEEK:([OD].[ObjectTypeId]=10 AND [OD].[ObjectId]=[WO].[Id]),
WHERE:(((Convert([TASKVIEW].[isterminal])=1 AND
[@.seldate]<=[OD].[LastStatusUpdDt]) OR (
|--Index
S(OBJECT:([Changeware].[dbo].[Task].[IX_Task_WorkOrderId] AS [T]),
SEEK:([T].[WorkOrderId]=[OD].[ObjectId]) ORDERED FORWARD)
It is doing bookmark lookup on the workorder table which is costing
around 92% of the total query cost. I am still not getting, with the
proper indexes on the statusid column and the assignedto column of the
workorder table why optimizer is doing bookmark lookup on workorder
table.
*** Sent via Developersdex http://www.examnotes.net ***|||have you tried indexes on WorkOrder(Id, AssignedTo) and
WorkOrder(AssignedTo, iD) ?