我正在对一个性能不佳的存储过程的各个部分进行故障排除.该程序的这一部分是抛出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.