SQL Server 是否在复杂视图中传播 WHERE 条件? [英] Does SQL Server propagate WHERE conditions in complex views?

问题描述

我已经用一个完整的例子来跟踪这个问题，以防我从这个问题中不清楚我的意思.

I've followed this question with a full example in case it isn't clear what I mean from the question.

我制作了一个视图，它连接了大约五个表中的数据.这些表有大量数据，查询运行缓慢.我的问题是，如果我这样做:

I've made a view which joins data from about five tables. The tables have vast amounts of data and the queries are slow to run. My question is that if I do:

SELECT * FROM myView WHERE PersonID = 1000 

SQL Server 是否知道我的意思"并自动将该条件传播到视图中的底层连接?这样它就不会为所有人运行，而是在正确的阶段最小化结果集.或者它会运行所有内容然后在完整结果集上执行 WHERE ID = 1000 ?

does SQL Server 'know what I mean' and automatically propagate that condition to the underlying joins in the view? So that it doesn't run for everybody, but minimizes the result set at the right stages. Or will it run for everything then do the WHERE ID = 1000 on the full result set?

示例

为了简化(...希望如此)我的意思，这里有一个伪 TSQL 场景示例:

To simplify (...hopefully) what I mean, here's an example pseudo-TSQL scenario:

TABLE People (
    ID,
    Surname,
    DOB
)
TABLE Activities (
    ID,
    TypeID,
    LocationID,
    Date
)
TABLE PersonActivityInvolvements (
    ID, 
    PersonID, 
    ActivityID
)
TABLE ActivityTypes (
    ID,
    Name
)
TABLE Locations (
    ID,
    Street,
    City
)

所以我想要一个视图，显示所有 People、他们参与的任何 Activity、ActivityType 和 地点 它发生了.尽管此设置并不十分复杂，但您可以看到，如果每个实体有数万个，则执行可能需要很长时间.

So I want a view which shows me all People, any Activities they were involved in, the ActivityType, and the Location it took place. Although this setup is not drastically complicated, you can see that it might take a very long time to execute if there are say tens of thousands of each entity.

视图可能是这样的:

SELECT 
    *
FROM 
    People LEFT OUTER JOIN PersonActivityInvolvement PA
    ON People.ID = PA.ID
        INNER JOIN Activity 
        ON PA.ID = Activity.ID
            INNER JOIN ActivityTypes AT
            ON A.TypeID = AT.ID
                INNER JOIN Locations 
                ON A.LocationID = Locations.ID

如果要这样做

SELECT * FROM myView WHERE DOB >= dateAdd(YEAR, -18, getDate())

视图中的查询是为所有人运行还是 SQL Server 知道它应该将其应用于 People.DOB 字段?

would the query inside the view run for everyone or would SQL Server know that it should apply it to the People.DOB field?

推荐答案

引擎会做任何它认为最快的事情.如果您将该字段编入索引，并且您的 JOIN 键都编入索引，则它可能会也可能不会先运行该过滤器.

The engine will do whatever it thinks is fastest. If you have that field indexed, and your JOIN keys are all indexed, it may or may not run that filter first.

如果 WHERE 子句的开销更高(即未编入索引)，它实际上可能会在 LAST 上运行过滤器 - 这样开销大的操作就会在最小的结果集上运行.

It may actually run the filter LAST if the WHERE clause is more expensive (i.e. unindexed) - that way the expensive operation is running on the smallest result set.

唯一确定的方法是运行查询并检查执行计划(实际未估计).

Ther only way to know for sure is to run the query and check the execution plan (ACTUAL not estimated).

这篇关于SQL Server 是否在复杂视图中传播 WHERE 条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！