Sql-Server

檢查相關表中數據的性能

  • October 10, 2015

注意:這個問題純粹是學術/幫助提高我對 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 
) child 
1           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 毫秒。

您需要盡可能少的掃描。我們掃描表的次數越多,所需的時間就越長。

引用自:https://dba.stackexchange.com/questions/72094