今天写了一个很简单的存储过程，结果一执行，40多秒，后来调整了一句话写法，瞬间出来，其实差别不大，如下：
select item_no=vpc.ITEM_ID
,BL_QTY=sum(vpc.QTY_CONTRACT-vpc.QTY_Shipped)
,QTY_Dis=sum(vpc.QTY_Distributed-vpc.QTY_Shipped)
,QTY_Avail=sum(vpc.QTY_CONTRACT-vpc.QTY_Distributed)
from TUC_V_PC_Line_Status vpc
where
isnull((select QTY_Avail from EX_ITEM_INV where item_no=vpc.item_ID and Whse='CN'),0)<0
and ITEM_ID=case when isnull(@Item,'')='' then ITEM_ID else @Item end
and CUST_ID=case when isnull(@Vendor,'')='' then CUST_ID else @Vendor end
group by vpc.ITEM_ID
测试时间40s左右
改成如下，测试时间1s之内完成
select item_no=vpc.ITEM_ID
,BL_QTY=sum(vpc.QTY_CONTRACT-vpc.QTY_Shipped)
,QTY_Dis=sum(vpc.QTY_Distributed-vpc.QTY_Shipped)
,QTY_Avail=sum(vpc.QTY_CONTRACT-vpc.QTY_Distributed)
from TUC_V_PC_Line_Status vpc
left join EX_ITEM_INV INV on INV.item_no=vpc.item_ID and INV.Whse='CN'
where
isnull(INV.QTY_Avail,0)<0
and ITEM_ID=case when isnull(@Item,'')='' then ITEM_ID else @Item end
and CUST_ID=case when isnull(@Vendor,'')='' then CUST_ID else @Vendor end
group by vpc.ITEM_ID
上述的视图TUC_V_PC_Line_Status中仅仅只有4377条记录，EX_ITEM_INV中约40000条记录，时间上就可以差别这么多。