Prev: Estimated number of rows
Next: How to Find any connection over internet
From: Dan Holmes on 6 Aug 2010 14:11
i have a SELECT from a VIEW that is taking ~43 seconds. If i inline all the SQL (instead of using view) i can get the
same result set in under 1 sec.

The estimated plans for each select match the actual plans. What i don't know how to do is figure out why the optimizer
chose the plan in the VIEW's case. Why won't it expand the view to the same plan as the inline SQL. In a different DB
(with different data), the VIEW SQL works just as well as the inline SQL version. (the optimizer does it "right" - if
there is such a thing).

What i want to know is how to understand this.

The SQL, tables and data are too large to post. I can provide as much info as practical though.

danny
From: Erland Sommarskog on 6 Aug 2010 17:17
Dan Holmes (dan.holmes(a)routematch.com) writes:
> i have a SELECT from a VIEW that is taking ~43 seconds. If i inline all
> the SQL (instead of using view) i can get the same result set in under 1
> sec.
>
> The estimated plans for each select match the actual plans. What i
> don't know how to do is figure out why the optimizer chose the plan in
> the VIEW's case. Why won't it expand the view to the same plan as the
> inline SQL. In a different DB (with different data), the VIEW SQL works
> just as well as the inline SQL version. (the optimizer does it "right"
> - if there is such a thing).
>
> What i want to know is how to understand this.
>
> The SQL, tables and data are too large to post. I can provide as much
> info as practical though.

Without seeing any code, it is too much of a guessing game.

But a view works like a macro. That is, the view is expanded into the query,
and then the optimizer works with the expanded query. So there should
not be any difference.

The one exception is if you have an indexed view and use the NOEXPAND
hint.

One possibility is that you did not do the expansion of the view
correctly, so that the two queries are actually different. One way to test
this is to take the XML plan from on of the queries and then force
it on the other query with the USE PLAN hint. If you get results back,
the queries are indeed identical. If you get an error, they are probably
not. (The way SQL Server validates the USE PLAN hint is that it keeps
generating plans, until it generates the plan you are trying to force.)

Yet a possibility is that when the algebrizer expands the view, this
results in a somewhat different tree representation. The optimizer
never sees the query text, it sees a query tree.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Estimated number of rows
Next: How to Find any connection over internet