问题描述

如何优化下面语句，可以更高效率写入MSSQL 。（ASP+MSSQL）

现在数据量大，经常运行后无响应。如何优化下面语句，可以更高效率写入MSSQL 。（ASP+MSSQL）

<%

sub autoActfp()        i1=csng(request(""i1""))        i2=csng(request(""i2""))        response.write ""<p style='height:30px;background:#483D8B;color:white;'><b>&nbsp;根据数据量的大小，整个过程大约需要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 TA01TA07 FROM ZDIDTA WHERE TA02 ='""&r0(0)&""' AND TA09='I' ORDER BY TA03TA01"")            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.endend subsub Setb(x1x2x3)          '自动分配    iz = CSng(x3)    i = ""1""    set r2 = conn.execute(""SELECT TA01TA07-TA13 FROM ZDIDTA WHERE TA02 ='""&x1&""' AND TA09='O' and TA07-TA13>0 ORDER BY TA10TA03TA01"")    if not r2.eof then        Do While Not r2.Eof            izs = CSng(r2(1))            if iz > izs then                conn.execute(""insert into ZDIDTB(TB01TB02TB03TB14TB15) 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(TB01TB02TB03TB14TB15) 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 IFend sub

%>

解决方案

asp本身就是一种效率很低的解释语言，用asp.net改写下，就有3-5倍的提升。

解决方案二：
通过存储过程，数据传递给函数参数来调用插入