存储过程中代码的优化问题？
以下内容是存储过程中的一段，由于数据量较多，后面的UPDATE语句执行很慢，请大家帮忙看看如何优化
SELECT orgnid,
orgnno,
orgnname
INTO #a001orgn
FROM a001orgn
WHERE a001orgn.orgnno LIKE @OrgnNO + '% '
SELECT accgyearid,
acctid,
pacctid,
acctno,
acctname,
nolevel,
islowestlevel,
balancedirection
INTO #a111acctitle
FROM a111acctitle
WHERE acctno LIKE @AcctNo + '% '
ORDER BY acctno
SELECT #a001orgn.orgnno,
#a001orgn.orgnname,
#a111acctitle.acctno,
#a111acctitle.acctname,
Cast(0 AS money) AS ncye,
Cast(0 AS money) AS qcye,
Cast(0 AS money) AS qmye,
Cast(0 AS money) AS bqjf,
Cast(0 AS money) AS bqdf,
Cast(0 AS money) AS bnlj,
Cast(0 AS money) AS bnld,
Cast(0 AS money) AS qmjf,
Cast(0 AS money) AS qmdf,
Cast(0 AS money) AS xjjf,
Cast(0 AS money) AS xjdf,
#a111acctitle.nolevel,
#a111acctitle.islowestlevel
INTO #orgnacct
FROM #a001orgn,
a110accg,
a111accgyear,
#a111acctitle
WHERE #a001orgn.orgnid = a110accg.orgnid
AND a110accg.accgid = a111accgyear.accgid
AND a111accgyear.accgyearid = #a111acctitle.accgyearid
AND a111accgyear.fiscalyear = @FiscalYear
ORDER BY #a001orgn.orgnno,
#a111acctitle.acctno
UPDATE #orgnacct
SET ncye = v1.ncye
FROM (SELECT #a001orgn.orgnno,
a111acctitle.acctno,
Isnull(Sum((1 - #a111acctitle.balancedirection * 2) * (a111voucherentry.bookrdmoney - a111voucherentry.bookrcmoney)),
0) AS ncye
FROM #a001orgn,
a110accg,
a111accgyear,
a111accperiod,
#a111acctitle,
a111acctitle,
a111voucher,
a111voucherentry
WHERE #a001orgn.orgnid = a110accg.orgnid
AND a110accg.accgid = a111accgyear.accgid
AND a111accgyear.accgyearid = a111accperiod.accgyearid
AND a111voucher.accpid = a111accperiod.accpid
AND a111voucher.voucherid = a111voucherentry.voucherid
AND a111voucherentry.acctid = #a111acctitle.acctid
AND (a111acctitle.accgyearid = #a111acctitle.accgyearid
AND (a111acctitle.acctno = #a111acctitle.acctno
OR #a111acctitle.acctno LIKE a111acctitle.acctno + '-% '))
AND (Isnull(a111voucher.vouchersortid, ' ') = ' '
OR a111voucher.vouchersorton = 0)
AND a111accgyear.fiscalyear = @FiscalYear
AND #a111acctitle.acctno LIKE @AcctNO + '% '
GROUP BY orgnno,
a111acctitle.acctno) v1
WHERE #orgnacct.orgnno = v1.orgnno
AND v1.acctno = #orgnacct.acctno
[解决办法]
SELECT orgnid,
orgnno,
orgnname
INTO #a001orgn
FROM a001orgn
WHERE a001orgn.orgnno LIKE @OrgnNO + '% '
SELECT accgyearid,
acctid,
pacctid,
acctno,
acctname,
nolevel,
islowestlevel,
balancedirection
INTO #a111acctitle
FROM a111acctitle
WHERE acctno LIKE @AcctNo + '% '
ORDER BY acctno --将数据插入临时表还要order by干嘛！？需要使用排序数据的时候，对#a111acctitle排序不应该在这里。这句去掉。
SELECT #a001orgn.orgnno,
#a001orgn.orgnname,
#a111acctitle.acctno,
#a111acctitle.acctname,
Cast(0 AS money) AS ncye, --Cast(0 AS money) 声明为一个变量，下面的 Cast(0 AS money) 都使用这个变量。
Cast(0 AS money) AS qcye,
Cast(0 AS money) AS qmye,
Cast(0 AS money) AS bqjf,
Cast(0 AS money) AS bqdf,
Cast(0 AS money) AS bnlj,
Cast(0 AS money) AS bnld,
Cast(0 AS money) AS qmjf,
Cast(0 AS money) AS qmdf,
Cast(0 AS money) AS xjjf,
Cast(0 AS money) AS xjdf,
#a111acctitle.nolevel,
#a111acctitle.islowestlevel
INTO #orgnacct
FROM #a001orgn,
a110accg,--没有在select中有引用的字段，建议使用 in方式判断，而不是join方式。这么join容易产生冗余数据。
a111accgyear,--同上。
#a111acctitle
WHERE #a001orgn.orgnid = a110accg.orgnid
AND a110accg.accgid = a111accgyear.accgid
AND a111accgyear.accgyearid = #a111acctitle.accgyearid
AND a111accgyear.fiscalyear = @FiscalYear
ORDER BY #a001orgn.orgnno, --同上。去掉！
#a111acctitle.acctno
--1、下面这句会很慢。建议将其中的SELECT #a001orgn.orgnno结果部分放到临时表中，然后用这个临时表再与#orgnacct进行关联更新。速度提升或下降和你的实际数据有关，强烈建议你试试。
--2、SELECT #a001orgn.orgnno语句中除了#a001orgn、a111acctitle、#a111acctitle、a111voucherentry这四个表作inner join，其他的都关联后放在in中。
UPDATE #orgnacct
SET ncye = v1.ncye
FROM (SELECT #a001orgn.orgnno,
a111acctitle.acctno,
Isnull(Sum((1 - #a111acctitle.balancedirection * 2) * (a111voucherentry.bookrdmoney - a111voucherentry.bookrcmoney)),
0) AS ncye
FROM #a001orgn,
a110accg,
a111accgyear,
a111accperiod,
#a111acctitle,
a111acctitle,
a111voucher,
a111voucherentry
WHERE #a001orgn.orgnid = a110accg.orgnid
AND a110accg.accgid = a111accgyear.accgid
AND a111accgyear.accgyearid = a111accperiod.accgyearid
AND a111voucher.accpid = a111accperiod.accpid
AND a111voucher.voucherid = a111voucherentry.voucherid
AND a111voucherentry.acctid = #a111acctitle.acctid
AND (a111acctitle.accgyearid = #a111acctitle.accgyearid
AND (a111acctitle.acctno = #a111acctitle.acctno
OR #a111acctitle.acctno LIKE a111acctitle.acctno + '-% '))
AND (Isnull(a111voucher.vouchersortid, ' ') = ' '
OR a111voucher.vouchersorton = 0)
AND a111accgyear.fiscalyear = @FiscalYear
AND #a111acctitle.acctno LIKE @AcctNO + '% '
GROUP BY orgnno,
a111acctitle.acctno) v1
WHERE #orgnacct.orgnno = v1.orgnno
AND v1.acctno = #orgnacct.acctno
至于索引，不知道你的表数据情况，请根据实际的执行计划自行建立吧。
