如何提高这组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()   ,这样一来数据量很大的时候就会很慢甚至超时

------解决方案--------------------
-------------最后的引用改成如下:
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
会不会快一点
------解决方案--------------------
把函数换成下面的子查询试试看：

Isnull(max(BillDate) from Balance, '2007-1-31 ')
再在这个field --CustomerNO上建个index试试
------解决方案--------------------
你改成这样试试

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.