站长网 MsSql教程 sql-server – 这个NO JOIN PREDICATE警告我应该感到警惕吗?

sql-server – 这个NO JOIN PREDICATE警告我应该感到警惕吗?

我正在对一个性能不佳的存储过程的各个部分进行故障排除.该程序的这一部分是抛出NO JOIN PREDICATE警告 select method = case methoddescription when ‘blah’ then ‘Ethylene Oxide’ when NULL then ‘N/A’ else methoddescription end,testmethod = case m

我正在对一个性能不佳的存储过程的各个部分进行故障排除.该程序的这一部分是抛出NO JOIN PREDICATE警告

select
    method = 
        case methoddescription 
            when 'blah' then 'Ethylene Oxide'
            when NULL then 'N/A'
            else methoddescription
        end,testmethod = 
        case methoddescription 
            when 'blah' then 'Biological Indicators'
            when NULL then 'N/A'
            else 'Dosimeter Reports'
        end,result = 
        case when l.res is null or l.res <> 1 then 'Failed'
        else 'Passed'
        end,datecomplete = COALESCE(CONVERT(varchar(10),NULL,101),'N/A')
from db2.dbo.view ls
    join db1.dbo.table l
        on ls.id = l.id
    where item = '19003'
        and l.id = '732820'

视图([ls])调用远程服务器(计划右侧的远程查询A).

这是该计划的图像:

因为this blog post我只问这个问题而且我想确保以后再也不会再咬我了.

解决方法

因为我们知道l.id =’732820’和ls.id = l.id然后SQL Server派生出ls.id =’732820′

FROM   db2.dbo.VIEW ls
       JOIN db1.dbo.table l
         ON ls.id = l.id
WHERE  l.id = '732820'

是相同的

( /*...*/ FROM   db2.dbo.VIEW ls WHERE id = '732820'  )
   CROSS JOIN 
  ( /*...*/  FROM   db1.dbo.table l WHERE id = '732820'  )

这次重写是not bad for performance.

This derivation is a good thing. It allows SQL Server to filter out rows … earlier than would otherwise be possible.

本文来自网络,不代表站长网立场,转载请注明出处:https://www.tzzz.com.cn/html/jc/mssql/2021/0525/6234.html

作者: dawei

【声明】:站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。
联系我们

联系我们

0577-28828765

在线咨询: QQ交谈

邮箱: xwei067@foxmail.com

工作时间:周一至周五,9:00-17:30,节假日休息

返回顶部