Execution Time is different

I am having a table ObjectDetail which is having following columns
ObjectTypeId, ObjectId,StatusId,StateId,Release, LastStatusUpdDt
ObjectTypeId,ObjectId is Primary Key.
This table contains about 1 million records in it.
1. When i removed the Primary Key and kept the Clustered Index on
ObjectTypeId,ObjectId and LastStatusUpd
the below query is taking about 1.5 sec.
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 SM (NOLOCK) ON SM.Id = WO.StatusId
INNER JOIN dbo.ObjectDetail OD(nolock) on WO.Id = OD.objectid AND
OD.ObjectTypeId = 10
WHERE WO.AssignedTo = @.ResourceId
AND ( SM.IsInDashboard = 1
OR ( SM.IsTerminal = 0 AND @.SELDATE <= (OD.LastStatusUpdDt +
SM.DaysInDashBoard) )
OR ( SM.IsTerminal = 1 AND @.SELDATE <= OD.LastStatusUpdDt )
But when i created the view and created the clustered index on
ObjectTypeId,ObjectId and LastStatusUpd. It is taking about 300 ms to
350 ms.
I am not understanding why it is taking much time when we define the
index on the table rather than on the view. Does the Execution Time
differs having the index on the table and view ?
*** Sent via Developersdex http://www.examnotes.net ***Well, look at the number of rows in the table and the number of rows returne
d
by the view. Are they the same?
Have you compared the execution plans? Have you traced the execution in SQL
Profiler to more accurately see the difference in CPU time and the number of
reads?
The answer is much closer than you think. :)
ML
http://milambda.blogspot.com/|||ramnadh nalluri (ramnadh_nalluri@.semanticspace.com) writes:
> I am having a table ObjectDetail which is having following columns
> ObjectTypeId, ObjectId,StatusId,StateId,Release, LastStatusUpdDt
> ObjectTypeId,ObjectId is Primary Key.
> This table contains about 1 million records in it.
> 1. When i removed the Primary Key and kept the Clustered Index on
> ObjectTypeId,ObjectId and LastStatusUpd
> the below query is taking about 1.5 sec.
> 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 SM (NOLOCK) ON SM.Id = WO.StatusId
> INNER JOIN dbo.ObjectDetail OD(nolock) on WO.Id = OD.objectid AND
> OD.ObjectTypeId = 10
> WHERE WO.AssignedTo = @.ResourceId
> AND ( SM.IsInDashboard = 1
> OR ( SM.IsTerminal = 0 AND @.SELDATE <= (OD.LastStatusUpdDt +
> SM.DaysInDashBoard) )
> OR ( SM.IsTerminal = 1 AND @.SELDATE <= OD.LastStatusUpdDt )
> But when i created the view and created the clustered index on
> ObjectTypeId,ObjectId and LastStatusUpd. It is taking about 300 ms to
> 350 ms.
> I am not understanding why it is taking much time when we define the
> index on the table rather than on the view. Does the Execution Time
> differs having the index on the table and view ?
What view? Do you create a view from the query above?
It's very difficult to answer the question without full knowledge of
the tables. (And it may not be that easy, even with that information,
as the optimizer's work is a lot about estimates.) You give some information
on ObjectDetail, but you are silent on the other tables.
Looking at the query, I would maybe prefer to write it as:
SELECT T.Id AS TaskId
FROM dbo.Task T
WHRE EXISTS (SELECT *
FROM dbo.WorkOrder WO
JOIN dbo.StateMaster SM ON SM.Id = WO.StatusId
JOIN dbo.ObjectDetail OD(nolock) on
WO.Id = OD.objectid
AND OD.ObjectTypeId = 10
WHERE T.WorkOrderId = WO.Id
AND WO.AssignedTo = @.ResourceId
AND (SM.IsInDashboard = 1 OR
SM.IsTerminal = 0 AND
@.SELDATE <= OD.LastStatusUpdDt + SM.DaysInDashBoard
OR
SM.IsTerminal = 1 AND @.SELDATE <=
OD.LastStatusUpdDt)
But that is mainly a question of esthetics; to have the query more clearly
express what the purpose is. (And ensure that I don't get duplicates in the
result set.) Performance may be better or worse.
As ML suggestions, comparing query plans, and look at estimates may give
some clues.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx