|
|
Posted by Dan Guzman on 09/29/52 11:39
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]),
>
> 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]
|