Performance Problem

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 ***
.

Relevant Pages

  • Re: SQL tuning
    ... Does SQL Server optimizer use indexes to perfom the query? ... It might be that otpimizer uses a 'bad' execution plan ... > FROM dbo.QuarterlyRunPortfolio qrp (NOLOCK) INNER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: DTE recommends an index that already exists but with a different .
    ... Unit - Your first column in the existing index is only give a "not equal" compare, which means that everything except that 1 value will be included. ... then another index would be more useful to your query. ... FROM MS2.dbo.Transactions AS T WITH (NOLOCK) ... INNER JOIN MS2.dbo.TransactionItems AS TI WITH ON T.Unit = TI.Unit ...
    (microsoft.public.sqlserver.tools)
  • Re: NOLOCK
    ... INNER JOIN Event E ON A.EventID = E.EventID WITH (NOLOCK) ... Pro SQL Server 2000 Database Design - ... >>> From the query, it looked like he had NOLOCK on one table, but not all. ...
    (microsoft.public.sqlserver.programming)
  • Re: Optimizing query
    ... I looked into your query. ... SQL to regular SQL. ... INNER JOIN cCompany with (NoLock) ...
    (microsoft.public.sqlserver.programming)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)