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