如何提高这组SQL语句的效率
首先是自定义的一个函数
CREATE FUNCTION GetLastBalanceDate()
RETURNS Datetime AS
BEGIN
declare @Date datetime
select @Date=max(BillDate) from Balance
if @Date is null
set @Date='2007-1-31'
Return @Date
END
然后类似这样的视图多个:
CREATE VIEW dbo.VPayment
--本视图用于表示与供应商之间发生的应付加减情况
AS--收货<现金付款除外>
SELECT MainSeq,'收货' MeNO,SignDate BillDate,CustomerNO,Amt from StoreMain where BillType=1 and IsCash=0
UNION ALL--减去退货金额,由于存储的是负数,直接相加<除掉现金退货单据>
SELECT MainSeq,'退货',SignDate,CustomerNO,Amt from StoreMain where BillType=2 and IsCash=0
UNION ALL--减去结帐以后的付款
SELECT SeqNo,'付款',BillDate,CustomerNO,-Amt from Payment
UNION ALL--减去结帐以后店面直接汇款给供应商的
SELECT SeqNO,'直接汇款',BillDate,CustomerNO,-Amt from RecPayment where IsTran=1
UNION ALL--加上采购点的公摊费用
SELECT SeqNO,'公摊费用',BillDate,UnderTaker,Amt from FeeStock where IsNULL(UnderTaker,0)<>0 and IsPool=1
UNION ALL--需要加上非公摊的费用<现金付款的采购费用不计算在内>
SELECT A.RelateSeq,B.CName,C.SignDate,A.CustomerNO,A.Amt from FeeStock A,FCode B,StoreMain C where ISNULL(A.RelateSeq,0)<>0
and A.RelateSeq=C.MainSeq and A.BillType=B.Code and B.Type=2 and A.IsPool=0 And A.IsCash=0
-------------最后的引用如下:
CREATE VIEW dbo.VPayable
AS--
select A.CustomerNO,IsNULL(B.Amt,0)+IsNULL(C.Amt,0) Amt
from Customer A
left join Payable B on A.CustomerNO=B.CustomerNO and B.BillDate=DBO.GetLastBalanceDate()
left join
(select CustomerNO,SUM(Amt) Amt from VPayment where BillDate>DBO.GetLastBalanceDate() group by CustomerNO) C
on A.CustomerNO=C.CustomerNO
where A.Type=2
SQL Server似乎在对比每条数据的时候都会去执行一次DBO.GetLastBalanceDate() ,这样一来数据量很大的时候就会很慢甚至超时