RiCo技術農場

我在撰寫任何tsql都有看執行計畫和IO統計資料的習慣，同時我也會把相關tsql在本機(資料少)和test server(資料量接近production)執行看看，

因為我個人認為資料量分布是一個很重要的第一因素，

無論對write tsql、design indexes和execution plan tuning來說，

今天我們來聊聊no join predicate(無聯結述詞)的警告對效能有沒有影響呢?

 

我先簡短回答:對效能可能有影響，也可能沒有影響。

我使用SQL2014來模擬no join predicate狀況(排除bug)

Note:SQL2012以前本版有bug( missing_join_predicate event: behavior and description)

PS:下面測試我都不會考慮index

create table #t1(c1 int,name varchar(100),storeon datetime) 
create table #t2(c1 int,name varchar(100),storeon datetime) 
create table #t3(c1 int,name varchar(100),storeon datetime) 
set nocount on
declare @i int=0
while(@i<10000)
begin
insert into #t1 values (1,'rico'+cast(newid() as varchar(50)),getdate()) 
insert into #t2 values (@i,'rico'+cast(newid() as varchar(50)),getdate()) 
insert into #t3 values (1,'rico'+cast(newid() as varchar(50)),getdate()) 
set @i+=1
end

 

*join key資料重複性過高

需求:兩個資料表透過c1 join並過濾資料表t1條件如下

C1=1,name=’ rico87ABA74A-A202-4D3D-9174-A48ED3543864’

 

開發人員很直覺寫出以下Query

select a.c1,a.name
from #t1 a join #t3 b on a.c1=b.c1
where a.c1=1
and a.name='rico87ABA74A-A202-4D3D-9174-A48ED3543864'

由於c1=1在t3資料表等同沒有過濾返回所有資料，

這時會讓QO產生一點疑惑並警告你，確定就是你要的邏輯嗎(這case下就算你建立正確索引也還是會有該警告)

 

當然我們知道這不是我們要的結果，所以我們可以改寫如下幾種

Query1 rewrite

select distinct a.c1,a.name
from #t1 a join #t3 b on a.c1=b.c1
where a.c1=1
and a.name='rico87ABA74A-A202-4D3D-9174-A48ED3543864'

 

Query2 rewrite

select a.c1,a.name
from #t1 a
where a.c1=1
and a.name='rico87ABA74A-A202-4D3D-9174-A48ED3543864'
and exists (select 1 from #t3 b where a.c1=b.c1)

可以看到這裡還是有no join predicate警告，但這時我會忽略該警告(因為我就只有1筆資料，就算沒有過濾也無訪)，

但是否有方法可以消除該警告呢?在來看看下面的改寫

select a.c1,a.name
from #t1 a
cross apply
(
select distinct(c1) c1
from #t3 b where a.c1=b.c1
) b
where a.c1=1
and a.name='rico87ABA74A-A202-4D3D-9174-A48ED3543864'

由於我們要讓QO知道，我們已經知道c1=1在t3資料表重複資料過多，

所以我們改寫透過cross apply加上distinct(c1)告知QO，這讓QO不在出現no join predicate警告。

 

另外也可以透過hint來消除該警告，但我必須要再次強調，如非必要請盡量少使用hint，除非你知道你自己在做什麼

select a.c1,a.name
from #t1 a
where a.c1=1
and a.name='rico87ABA74A-A202-4D3D-9174-A48ED3543864'
and exists (select 1 from #t3 b where a.c1=b.c1)
option(merge join)

note:no join predicate只會發生在nested loop join運算子。

 

現在需求變更，現在我們必須在多join #t2 資料表，query 如下

select distinct a.c1,a.name
from #t1 a join #t2 b on a.c1=b.c1
join #t3 c on b.c1 = c.c1
where a.c1=1
and a.name='rico87ABA74A-A202-4D3D-9174-A48ED3543864'

雖然使用distinct function，但這次還是出現了no join predicate警告，

主要原因如前面所說不在重複(且distinct是最後運算)，但我相信你也應該知道如何改寫並提高查詢效能

select a.c1,a.name
from #t1 a join #t2 b on a.c1=b.c1
cross apply
(
select distinct(c1) c1
from #t3 c
where b.c1 = c.c1
) c
where a.c1=1
and a.name='rico87ABA74A-A202-4D3D-9174-A48ED3543864'

 

另外，我也很常看到where 1=1或no 1=1這些多餘的條件，

但我個人不太建議，因為你有可能會返回全部資料或無法使用簡單參數化。

select *
from SalesOrderHeader
WHERE SalesOrderID = 44572

select *
from SalesOrderHeader
WHERE 1=1 and SalesOrderID = 44572

 

*返回過多不必要資料

查詢中不小心cross join不必要table，這種情況就會對效能有巨大影響，

除非你真的確定這就是你要的需求(符合你的業務邏輯)，否則請改寫你的tsql。

SELECT *
FROM Sales.SalesOrderHeader AS t1
,Sales.SalesOrderDetail AS t2
,Product AS p
WHERE t1.SalesOrderID = 44572

估計的資料列數目超過6千萬。

 

Query rewrite

SELECT *
FROM SalesOrderHeader AS t1
JOIN SalesOrderDetail AS t2
ON t1.SalesOrderID = t2.SalesOrderID
JOIN Product AS p
ON t2.ProductId = p.ProductID
WHERE t1.SalesOrderID = 44572;

改寫後，可以看到估計的資料列數目，從61143800降到12。

 

結論

當發生no join predicate警告時，請先確認查詢條件過濾是否真的有過濾效果，

是否有產生過多不必要資料影響效能，如果沒有以上問題，我個人會選擇忽略該警告，

總之，你一定要清楚你在寫什麼，祝好運 :) 。

 

ps:透過下面TSQL找出前50有no join predicate警告

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  st.text,
        qp.query_plan
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//p:RelOp/p:Warnings[(@NoJoinPredicate[.="1"])]') AS q(n) ;

 

參考

[SQL SERVER][TSQL]UNION statement

No Join Predicate

Implied Predicates and Query Hints

Finding: No Join Predicate

Transact SQL – Missing Join Predicate Event – No Join Predicate