悬赏:MSSQL 表连接长语句性能优化案例求解答？

Question

十分钟调用一次服务器数据库的长SQL语句,当数据量逐渐增大,运行效率是否会下降？求该怎么优化？？
(注释:D表为所有物料的限制车数,F为采购,销售合并的厂内车数,SIP_ScaleSales和SIP_ScalePurch 为流水,会逐渐增大 )

select D.FItemName,D.FLimitNum,ISNULL(F.FCurrentNum,0) FCurrentNum from
( select FItemName,FLimitNum from SIP_MaterialInsLimit WHERE FCancel =0 AND FDeleted = 0) D
LEFT JOIN
(
select A.FItemName,A.FLimitNum,COUNT(B.FID) FCurrentNum from SIP_MaterialInsLimit AS A
inner join SIP_ScalePurch AS B on A.FItemID = B.FItemID
Where A.FClassTypeID = 2 group by A.FLimitNum,A.FItemName
UNION
select A.FItemName,A.FLimitNum,COUNT(B.FID) FCurrentNum from SIP_MaterialInsLimit AS A
inner join SIP_ScaleSales AS B on A.FItemID = B.FItemID
Where A.FClassTypeID = 1 group by A.FLimitNum,A.FItemName
) F ON D.FItemName=F.FItemName

该怎么做才能让SQL运行效率提高？

Answer 1

一：最显眼的，把union 改成 union all试试
二：你这sql写得太繁杂了，同一个表查了3次，而且3张表的关联条件明明就是FItemID，为啥还要转换成FItemName去关联？还有，如果同一个物料在销售和采购里都有数据，你这一匹配不会导致缺失或者翻倍？不是应该求和后再匹配么？还有一个问题，既然是流水表，难道每次都要对历史数据进行全量统计，就没个时间范围么？
慢不是因为sql长，而是这个sql本就写得有问题。
你比较一下下面这个sql和你的区别

select a.FItemName,
           a.FLimitNum,
           ISNULL( v.FCurrentNum, 0 ) FCurrentNum 
    from SIP_MaterialInsLimit a left join 
    (select FItemID ,sum(FCurrentNum) FCurrentNum from 
    (select FItemID,count(1) FCurrentNum from SIP_ScalePurch group by FItemID 
    union all
    select FItemID,count(1) FCurrentNum from SIP_ScaleSales group by FItemID ) b) v
  on a.FItemID=v.FItemID
    where a.FCancel = 0 AND a.FDeleted = 0;