SEO

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

WHEREConvert([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 ***

This question has been answered in microsoft.public.sqlserver.programming.
Please don't post the same question independently to multiple groups.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"ramnadh nalluri" <ramnadh_nalluri@semanticspace.com> wrote in message
news:hLlGf.1$gp2.651@news.uswest.net...
> 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]),
>
> WHEREConvert([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 ***