Posted by Erland Sommarskog on 03/11/06 20:53

ysfinks (ysfinks@gmail.com) writes:
> I narrowed down to one join. Same difference in query plans. Query cost
> for 1 is 5.48%, for 2 is 94.52%
> My view is:
> create view dbo.sf_test as
> SELECT
> dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
> CompanyAccounts.root_account_id AS ECCRootID
> FROM dbo.ManagedNodes WITH (NOLOCK)
> INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
> ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
>
> My queries are:
> 1.
> SELECT dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
> CompanyAccounts.root_account_id AS ECCRootID
> FROM dbo.ManagedNodes WITH (NOLOCK)
> INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
> ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
> where ECCRootID=15427
> 2.
> select NodeID, SubscriptionID, ECCRootID
> from dbo.sf_test where eccrootid=15427

I will have to admit that I don't have any good answers at this
point. But I still like to ask some questions, just to check:

Exactly how do you create the view? From Query Analyzer or Enterprise
Manager? If the latter, what happens, if you run a script in QA
where you first create the view, and then run the queries?

What happens if you take out the NOLOCK hints?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]