现在数据量大,经常运行后无响应。如何优化下面语句,可以更高效率写入MSSQL 。(ASP+MSSQL)
<%
sub autoActfp()
i1=csng(request("i1"))
i2=csng(request("i2"))
response.write "<p style='height:30px;background:#483D8B;color:white;'><b> 根据数据量的大小,整个过程大约需要1-3分钟,请稍候……。</b></p>"
for i3=1 to i2
if i3 <> i2 then
Response.Write "<p> 第 <b>"&i3&"</b> 部分供需数据,已运算完成。</p>"
else
Response.Write("<p> 正在运算第 <b>"&i3&"</b> 部分供需数据,请等待...</p>")
end if
next
Response.Flush()
set r0 = conn.execute("select top 500 DD01 from ZDIDAN where DD03 = 'O' AND DD01 not in(select top "&i1&" DD01 from ZDIDAN where DD03 = 'O')")
Do While Not r0.Eof
set r1 = conn.execute("SELECT TA01,TA07 FROM ZDIDTA WHERE TA02 ='"&r0(0)&"' AND TA09='I' ORDER BY TA03,TA01")
Do While Not r1.Eof
call Setb(r0(0),r1(0),r1(1)) '循环子级分类 自动分配
r1.Movenext
Loop
r1.Close
Set r1 = Nothing
r0.Movenext
Loop
r0.Close
Set r0 = Nothing
i1=i1+500
i2=i2+1
ttx = conn.execute("select count(*) from ZDIDAN where DD03 = 'O'")(0)
if i1 < ttx then
response.write "<script>window.open('?nt=vActfp&ixt=1&i1="&i1&"&i2="&i2&"','NewWin1');</script>"
end if
Response.Write("<p> <font color=red size=3 COLSPAN=5>全部计算完毕。<input type=button value=关闭窗口 onclick=""window.close()""><p>")
Response.Flush()
response.end
end sub
sub Setb(x1,x2,x3) '自动分配
iz = CSng(x3)
i = "1"
set r2 = conn.execute("SELECT TA01,TA07-TA13 FROM ZDIDTA WHERE TA02 ='"&x1&"' AND TA09='O' and TA07-TA13>0 ORDER BY TA10,TA03,TA01")
if not r2.eof then
Do While Not r2.Eof
izs = CSng(r2(1))
if iz > izs then
conn.execute("insert into ZDIDTB(TB01,TB02,TB03,TB14,TB15) VALUES('"&x2&"','"&x1&"','"&r2(0)&"',"&i&","&izs&")")
conn.execute("Update ZDIDTA SET TA13=TA13+"&izs&" WHERE TA01='"&r2(0)&"' AND TA02='"&x1&"'")
iz = iz-izs
else
conn.execute("insert into ZDIDTB(TB01,TB02,TB03,TB14,TB15) VALUES('"&x2&"','"&x1&"','"&r2(0)&"',"&i&","&iz&")")
conn.execute("UPDATE ZDIDTA SET TA13=TA13+"&iz&" WHERE TA01='"&r2(0)&"' AND TA02='"&x1&"'")
Exit Sub
end if
i = i + 1
r2.Movenext
if r2.eof then exit do '防上造成死循环
Loop
else
exit sub
END IF
end sub
%>