Sql-Server
檢查相關表中數據的性能
注意：這個問題純粹是學術/幫助提高我對 SQL Server 性能的理解。
給定一個與一個或多個其他表相關的主表，您將如何確定查詢該主表以獲取記錄的最佳方法，其中包括相關表中是否存在記錄的指示符？
例如，假設我們有一個 Person 表，並且想要獲取所有人員的列表以及他們是否有孩子的指示符（在此範例中，Person 可以用作相關表）：
create table Person ( Id bigint not null constraint pk_Person primary key clustered , ParentId bigint null constraint fk_Person_Parent foreign key references Person(Id) , FirstName nvarchar(256) not null , LastName nvarchar(256) not null )我們可以執行以下任何查詢來檢查相關子項的存在：
--variables for restricting our result set, just to keep things interesting declare @LastName nvarchar(256) = 'Be%' , @FirstName nvarchar(256) = null範例 1
-- fairly straight forward, but requires grouping to account for the -- potential of a parent having multiple kids (which I don't care about here) -- which could be adding some inefficiency. select parent.Id , parent.FirstName , parent.LastName , case when max(child.Id) is null then 0 else 1 end HasChildren from Person parent left outer join Person child --1:n on child.ParentId = parent.Id where (@LastName is null or parent.LastName like @LastName) and (@FirstName is null or parent.FirstName like @FirstName) group by parent.Id, parent.FirstName, parent.LastName --resolve 1:n範例 2
-- avoid the need to group the results by first getting -- a single child per parent. -- may be inefficient because we get children for all parents -- even if we filter for only a few parents. select parent.Id , parent.FirstName , parent.LastName , coalesce(child.hasChildren, 0) HasChildren from Person parent left outer join --1:? (0 or 1) ( select distinct parentId, 1 hasChildren from Person where parentId is not null --not sure if this adds value ) child on child.ParentId = parent.Id where (@LastName is null or LastName like @LastName) and (@FirstName is null or FirstName like @FirstName) --group by removed since we're 1:?範例 3
-- same as #2 except we limit the child results to those -- related to the parents we're interested in / having stored -- them in a CTE to avoid querying for the same parent data -- in the inner query and outer query. -- Getting a bit silly now, but could overcome some inefficienies? ;with parentCTE as ( select Id, FirstName, LastName from person where (@LastName is null or LastName like @LastName) and (@FirstName is null or FirstName like @FirstName) ) select parentCTE.Id , parentCTE.FirstName , parentCTE.LastName , coalesce(child.hasChildren, 0) HasChildren from parentCTE left outer join --1:? (0 or 1) ( select distinct parentId, 1 hasChildren from Person where parentId in --reduce the amount of data we return here based on the records we're interested in ( select Id from parentCTE ) ) child on child.ParentId = parentCTE.Id範例 4
-- back to a simple one; just check for children on our parents -- but this time having brought back the full parent set. -- may be inefficient because we're querying the table once per -- matching parent to check for children. select parent.Id , parent.FirstName , parent.LastName , coalesce((select top 1 1 from Person child where child.parentId = parent.Id),0) HasChildren from Person parent where (@LastName is null or LastName like @LastName) and (@FirstName is null or FirstName like @FirstName)我正在尋找有關如何更好地理解在這種情況下所涉及的權衡的資訊，而不是簡單
example 3的最好的。也歡迎指向可能有助於我理解的文章。相關 SQL 小提琴：http ://sqlfiddle.com/#!6/edc17/3
範例 4 的掃描和讀取最少：
範例 1
SQL Server 解析和編譯時間：
CPU 時間 = 4 毫秒，執行時間 = 4 毫秒。
SQL Server 執行時間：CPU 時間 = 0 毫秒，經過時間 = 0 毫秒。
example1 Id FirstName ----------------------------- 1 2 Aaron 1 3 John 1 8 Aaron 1 9 John 1 14 Aaron 1 15 John 1 20 Aaron 1 21 John（受影響的 8 行）
表“人”。
掃描計數 9，邏輯讀取 27，物理讀取 0，
Rows Executes StmtText -------------------- -------------------- ------------------------ 8 1 select 1 example1, parent.Id , parent.FirstName , parent.LastName , case when max(child.Id) is null then 0 else 1 end HasChildren from Person parent left outer join Person child on child.ParentId = parent.Id where (@LastName is null or parent.Las 1 1 0 0 0 |--Compute Scalar(DEFINE:([Expr1005]=(1), [Expr1 8 1 |--Nested Loops(Left Outer Join, OUTER REFE 8 1 |--Clustered Index Scan(OBJECT:([sub]. 3 8 |--Stream Aggregate(DEFINE:([Expr1004] 7 8 |--Clustered Index Scan(OBJECT:([（6 行受影響）
SQL Server 執行時間：
CPU 時間 = 0 毫秒，經過時間 = 0 毫秒。
範例 2
SQL Server 解析和編譯時間：CPU 時間 = 0 毫秒，執行時間 = 4 毫秒。
SQL Server 執行時間：
CPU 時間 = 0 毫秒，經過時間 = 0 毫秒。
example2 Id FirstName ---------------------------- 2 2 Aaron 2 3 John 2 8 Aaron 2 9 John 2 14 Aaron 2 15 John 2 20 Aaron 2 21 John（受影響的 8 行）
表“人”。
掃描計數 9，邏輯讀取 27，物理讀取 0，
Rows Executes StmtText ------ ---------- ----------------------- 8 1 select 2 example2, parent.Id , parent.FirstName , parent.LastName , coalesce(child.hasChildren, 0) HasChildren from Person parent left outer join ( select distinct parentId, 1 hasChildren from Person where parentId is not null ) child1 1 0 NULL 0 0 |--Compute Scalar(DEFINE:([Expr1007]=(2), [Expr1 8 1 |--Nested Loops(Left Outer Join, OUTER REFE 8 1 |--Clustered Index Scan(OBJECT:([sub]. 3 8 |--Stream Aggregate(DEFINE:([Expr1006] 0 0 |--Compute Scalar(DEFINE:([Expr10 7 8 |--Clustered Index Scan(OBJE（7 行受影響）
SQL Server 執行時間：CPU 時間 = 0 毫秒，經過時間 = 0 毫秒。
範例 3
SQL Server 解析和編譯時間：CPU 時間 = 7 毫秒，執行時間 = 7 毫秒。
SQL Server 執行時間：CPU 時間 = 0 毫秒，經過時間 = 0 毫秒。
example3 Id FirstName ---- ------ ------------------------ 3 2 Aaron 3 3 John 3 8 Aaron 3 9 John 3 14 Aaron 3 15 John 3 20 Aaron 3 21 John（受影響的 8 行）
表“人”。掃描計數 9，邏輯讀取 41，物理讀取 0，r
Rows Executes StmtText ------ --------- ------------------ 8 1 with parentCTE as ( select Id, FirstName, LastName from person where (@LastName is null or LastName like @LastName) and (@FirstName is null or FirstName like @FirstName) ) select 3 example3 , parentCTE.Id , parentCTE.FirstName , parentCTE.La 1 1 0 NULL 0 0 |--Compute Scalar(DEFINE:([Expr1011]=(3), [Expr1 8 1 |--Nested Loops(Left Outer Join, OUTER REFE 8 1 |--Clustered Index Scan(OBJECT:([sub]. 3 8 |--Stream Aggregate(DEFINE:([Expr1010] 0 0 |--Compute Scalar(DEFINE:([Expr10 7 8 |--Nested Loops(Inner Join, 7 8 |--Clustered Index Scan 7 7 |--Clustered Index Seek（9 行受影響）
SQL Server 執行時間：CPU 時間 = 0 毫秒，經過時間 = 0 毫秒。
範例 4
SQL Server 解析和編譯時間：CPU 時間 = 3 毫秒，執行時間 = 3 毫秒。
SQL Server 執行時間：CPU 時間 = 0 毫秒，經過時間 = 0 毫秒。
example4 Id FirstName ---- ----- ------------- 4 2 Aaron 4 3 John 4 8 Aaron 4 9 John 4 14 Aaron 4 15 John 4 20 Aaron 4 21 John（受影響的 8 行）
表“人”。掃描計數 3，邏輯讀取 26，物理讀取 0，
Rows Executes StmtText -------- ---------- --------------------- 8 1 select 4 example4 , parent.Id , parent.FirstName , parent.LastName , coalesce((select top 1 1 from Person child where child.parentId = from Person parent where (@LastName is null or LastName like @LastName) and (@FirstName 1 1 0 NULL 0 0 |--Compute Scalar(DEFINE:([Expr1002]=(4), [Expr1 8 1 |--Nested Loops(Left Outer Join, PASSTHRU:( 8 1 |--Nested Loops(Left Outer Join, OUTER 8 1 | |--Clustered Index Scan(OBJECT:([ 0 0 | |--Compute Scalar(DEFINE:([Expr10 3 8 | |--Top(TOP EXPRESSION:((1))) 3 8 | |--Clustered Index Scan 0 0 |--Compute Scalar(DEFINE:([Expr1008]=( 3 3 |--Top(TOP EXPRESSION:((1))) 3 3 |--Clustered Index Scan(OBJE（11 行受影響）
SQL Server 執行時間：CPU 時間 = 0 毫秒，經過時間 = 0 毫秒。
您需要盡可能少的掃描。我們掃描表的次數越多，所需的時間就越長。