472,805 Members | 914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

Different query plans for view and view definition statement

I compared view query plan with query plan if I run the same statement
from view definition and get different results. View plan is more
expensive and runs longer. View contains 4 inner joins, statistics
updated for all tables. Any ideas?

Mar 9 '06 #1
10 3877
which version?

Mar 9 '06 #2
SQL Server 2000, Enterprise Edition with SP4

Mar 9 '06 #3
ysfinks (ys*****@gmail.com) writes:
I compared view query plan with query plan if I run the same statement
from view definition and get different results. View plan is more
expensive and runs longer. View contains 4 inner joins, statistics
updated for all tables. Any ideas?


Since you didn't share anything close to a repro, I have little idea
of you what you are doing. Since a view essential is a macro, it should
not matter that much. Then again, I've been wrong before. Anyway, it
would help if you posted the view, and the two SELECT you run.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 9 '06 #4
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

Mar 10 '06 #5
ysfinks (ys*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 11 '06 #6
so if i am understanding, if you do a select against a view, it takes a
very long time.

but if copy that exact same code into query analyzer or a stored
procedure, it goes MUCH faster.

and If I am understanding correctly the issue, there will be an index
on eccrootid.

And, if I am understanding, the view won't use the index on ECCrootid,
but everything else will.
Do I have the issue correctly? If so, yup, it does that in SS2000. You
can try compiler hints in the view to FORCE it to sue the index, but
that only works sometimes.

Best workaround is to move all your views to stored procedures, and
pass the eccrootid parameter to the sproc.

I reported this 5 years ago, adn even discussed it with Erland at that
time.

Views suck.

Regards,
Doug

Mar 12 '06 #7
A VIEW is handled two ways in SQL. The text of the VIEW is "pasted"
into the query that uses it and then the parser and optimizer handle it
as if the query had been written with a derived table. The parser can
do a lot stuff at this point, so the original view text is "spread out
all over the place".

The second way is materialize the VIEW as a temporary table. The good
news is that this materialized table can be shared by multiple users,
so the overall processing time goes down, even if each user's plan is
not optimal for their query. This is a feature of larger SQL products
like Ingres, DB2 or Oracle.

Trust in the optimizer, Luke.

Mar 12 '06 #8
if you are going ot have a materialized view, why not just bite the
bullet and have a denormalized table hanging around that gets updated
all the time.

the optimizer is fine for 90 percent of the time.

Mar 13 '06 #9
View was created from Query Analyzer. If I remove nolock - same result.
Another fact - if I change condition value in where clause, for some
values it gives for the view the good query plan using index for
eccrootid.
For the query simulating the view - always good plan.

Mar 14 '06 #10
ysfinks (ys*****@gmail.com) writes:
View was created from Query Analyzer. If I remove nolock - same result.
Another fact - if I change condition value in where clause, for some
values it gives for the view the good query plan using index for
eccrootid.
For the query simulating the view - always good plan.


I will have admit that I am fairly stumped at this point. For this reason
I have consulted some other people offline. No promises, but keep watching
this space.

Nevertheless, you run the two queries bracketed by

set statistics profile on
set statistics profile off

If you can put that in a file as a attachment ot on web site, to avoid
that the output is mashsed in news transport, that would be great, but
anything goes.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 15 '06 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Thomas R. Hummel | last post by:
I have a stored procedure that suddenly started performing horribly. The query plan didn't look right to me, so I copy/pasted the code and ran it (it's a single SELECT statement). That ran pretty...
4
by: manning_news | last post by:
Using SQL2000. How do I format my select statement to choose one out of 24 different tables? Each table is slightly different and I was hoping I could use one select statement and format it...
0
by: plato | last post by:
I'm an end user of a siebel program and i want to get some info on questions relating to queries within the program. Who or where can I get some answers beyond basic query operators? Maybe I...
5
by: sql-db2-dba | last post by:
We have DB2 UDB v8.1 fixpak3 on AIX 5. Production and Development configuarations (at least for DB2) are identical albeit production is a 2-way server while development has only one processor....
22
by: Marc Mones | last post by:
Hello, I'working with IBM DB2 V8.1 and CLI/ODBC. I've got a problem with the following statement: ******************************************************************************** SELECT...
6
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too...
3
by: GL | last post by:
Hi, Is there a way to add a field to an existing table using a query of some sort (without needing to manually add a field to the table). I know how to do it with a make table query, but I have...
1
by: Dmitri | last post by:
Hi! I have a stored procedure that takes 22 minutes to run in one environment, that only takes 1 sec or so to run in another environment. Here is the exact situation: Database 1 on Server 1...
5
by: sqlgirl | last post by:
Hi, We are trying to solve a real puzzle. We have a stored procedure that exhibits *drastically* different execution times depending on how its executed. When run from QA, it can take as...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.