問題描述

我已經用一個完整的例子來跟蹤這個問題，以防我從這個問題中不清楚我的意思.

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 條件?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！