如何提高这组SQL语句的效率

[wrl001]

首先是自定义的一个函数
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() ,这样一来数据量很大的时候就会很慢甚至超时

[CaptainV]

-------------最后的引用改成如下:
CREATE VIEW dbo.VPayable
AS--
Declare payDate datetime
select payDate=DBO.GetLastBalanceDate()
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=payDate
left join
(select CustomerNO,SUM(Amt) Amt from VPayment where BillDate>payDate group by CustomerNO) C
on A.CustomerNO=C.CustomerNO
where A.Type=2
会不会快一点

[wrl001]

to CaptainV:
视图中不可以这么定义吧?

[free_pop2k]

把函数换成下面的子查询试试看：

Isnull(max(BillDate) from Balance,'2007-1-31')
再在这个field --CustomerNO上建个index试试

[wrl001]

to:free_pop2k
这样修改没效果吧
我想这组语句的问题出在SQL Server在对比每条数据的时候都会去执行一次DBO.GetLastBalanceDate()

我暂时的解决办法是修改DBO.GetLastBalanceDate() ,直接 Return '2007-3-31'
每次有结帐或反结帐都去修改这个自定义函数.

[wrl001]

我会这么猜测自然是做过测试了

我之前说过了修改修改DBO.GetLastBalanceDate() ,直接 Return '2007-3-31'之后虽然速度也很慢,但暂时解决了超时的问题...

[ReViSion]

你改成这样试试

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.type=2 and 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

[ReViSion]

我想这组语句的问题出在SQL Server在对比每条数据的时候都会去执行一次DBO.GetLastBalanceDate()
-------------------------------------------------------------------
我想微软没这么傻吧，

你可以这样比较一下就知道啦
declare @d datetime
set @d=DBO.GetLastBalanceDate()

select A.CustomerNO,IsNULL(B.Amt,0)+IsNULL(C.Amt,0) Amt
from Customer A
left join Payable B on a.type=2 and A.CustomerNO=B.CustomerNO and B.BillDate=@d
left join
(select CustomerNO,SUM(Amt) Amt from VPayment where BillDate>@d group by CustomerNO) C
on A.CustomerNO=C.CustomerNO
where A.Type=2

和

select * from dbo.VPayable

[ReViSion]

看一下速度是不是相当

[yongyupost2000]

写存储过程看能不能解决？？

[wrl001]

不行,因为我很多地方引用视图
select * from VBalance.......

这用存储过程无法实现吧

[free_pop2k]

LZ可以用procedure来实现，同时可以用temp table 来帮你达到目标.
procedure 通常会比view速度快，
子查询通常会比函数快些.尽量不要用函数去实现