Joining views in SQLServer with strange query optimizer behavior

Question

I have a complex view that I use to get a list of primary keys that indicate rows in a table that have changed between two time points.

This view should query 13 related tables and look at the change table to determine if the object is dirty or not.

Even if all this happens, do a simple query:

select * from vwDirtyEntities; 

Only takes 2 seconds.

However, if I change it to

 select e.Name from Entities e inner join vwDirtyEntities de on e.Entity_ID = de.Entity_ID 

It takes 1.5 minutes.

However, if I do this:

 declare @dirtyEntities table ( Entity_id uniqueidentifier; ) insert into @dirtyEntities select * from vwDirtyEntities; select e.Name from Entities e inner join @dirtyEntities de on e.Entity_ID = de.Entity_ID 

I get the same results in just 2 seconds.

This makes me think that SQLServer evaluates the presentation of each row when combining with Entities, rather than constructing a query plan that includes joining one inner join above to other joins in the view.

Please note that I want to join the full set of results from this view, as it filters out only those keys that I want internally.

I know that I can do this in a materialized form, but this will be due to the binding of the scheme to the representation and dependencies, and I do not like that the overhead associated with the index will cause (this opinion is only requested for export, while there is much more entries to base tables).

So, besides using a table variable to cache view results, is there a way to tell SQL Server to cache the view when evaluating a connection? I tried to change the order of the connection (select from the view and join the entities), however this did not matter.

The presentation itself is also very effective, and there is no room for optimization.

Answer 1

There is nothing magical about the view. This is a macro that expands. The optimizer decides when JOINed extends the view in the main request.

I will talk about other points in your post:

• You excluded the indexed view. A view can only be a discrete entity when indexing

• SQL Server will never execute an RBAR query. Only developers can write loops.

• no caching concept: every query uses the latest data if you don't use temporary tables

• you insist on using the view you decide is very effective. But they have no idea how optimizer views are processed, and it has tables 13

• SQL is declarative: join order usually doesn't matter

• Many serious database developers do not use views because of restrictions like this: they cannot be reused because they are macros

Change, another opportunity. Clicking Predicate on SQL Server 2005. That is, SQL Server cannot bring the JOIN clause “deeper” into the view.