|
|
Posted by ramnadh nalluri on 10/01/29 11:39
Hi,
Below is the query that causing problems
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
Seek(OBJECT:([Viper63].[dbo].[WorkOrder].[IX_WorkOrder] AS [WO]),
SEEK:([WO].[AssignedTo]=[@ResourceId]) ORDERED FORWARD)
| |--Clustered Index
Seek(OBJECT:([Viper63].[dbo].[StateMaster].[PK_StateMaster]),
SEEK:([StateMaster].[Id]=[WO].[StatusId]),
WHERE:(Convert([StateMaster].[IsInDashboard])=1) ORDERED FORWARD)
|--Index
Seek(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, it is
taking about 95% of the query time.
Regards,
ramnadh.
*** Sent via Developersdex http://www.developersdex.com ***
Navigation:
[Reply to this message]
|