- Foxtable(狐表) (http://www.foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://www.foxtable.com/bbs/list.asp?boardid=2)
---- 请老师帮忙优化一下这段代码 (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=158279)
-- 发布时间：2020/11/14 8:47:00
-- 请老师帮忙优化一下这段代码
此主题相关图片如下：aacc.jpg
我想实现已付款的金额套算，目前已实现，但是如果当我的BOOK2的记录有3000行，book1有3万行时，
整个执行过程至少5到10分钟才可以完成
请老师帮我看一下，我的代码要如何优化
我的数据库是SQL的
Dim cmd As New SQLCommand
Dim dt As DataTable
cmd.C
cmd.CommandText = "S ELECT 单位,总付款 from book2 "
dt = cmd.ExecuteReader()
Dim cmd1 As New SQLCommand
Dim dt1 As DataTable
cmd1.C
cmd1.CommandText = "S elect _identify as 序号, 单位,月份,应付款,已付款 from book1 "
dt1 = cmd1.ExecuteReader()
Dim mxzje As Decimal
Dim je As Decimal
Dim sb As new StringBuilder
For Each dr As DataRow In dt.DataRows
Dim drs As List(Of DataRow)
drs = dt1.Select("单位 =\'" & dr("单位") & "\'","月份 asc")
je = dr("总付款")
For Each dra As DataRow In dt1.DataRows
If je>= dra("应付款") Then
sb.AppendLine( " u pdate book1 set 已付款 = " & dra("应付款") & " where _identify = " & dra("序号") )
je = je - dra("应付款")
Else
sb.AppendLine( " u pdate book1 set 已付款 = " & je & " where _identify = " & dra("序号"))
je = 0
End If
Next
Next
cmd.CommandText = sb.ToString`
cmd.ExecuteNonQuery
[此贴子已经被作者于2020/11/14 8:53:54编辑过]
-- 发布时间：2020/11/14 9:42:00
--
1、如果BOOK2的数据是手工录入的，应该在datacolchanged事件即时处理，而不是全部录入后再处理。
2、如果BOOK2数据是导入到额，这只能这样了，不过既然数据都已经加载了，试试直接更新统一保存，如
Dim cmd As New SQLCommand
Dim dt As DataTable
cmd.C
cmd.CommandText = "SELECT 单位,总付款 from book2 "
dt = cmd.ExecuteReader()
Dim cmd1 As New SQLCommand
Dim dt1 As DataTable
cmd1.C
cmd1.CommandText = "Select [_identify] , 单位,月份,应付款,已付款 from book1 "
dt1 = cmd1.ExecuteReader(true)
Dim mxzje As Decimal
Dim je As Decimal
Dim sb As new StringBuilder
For Each dr As DataRow In dt.DataRows
Dim drs As List(Of DataRow)
drs = dt1.Select("单位 =\'" & dr("单位") & "\'","月份 asc")
je = dr("总付款")
For Each dra As DataRow In dt1.DataRows
If je>= dra("应付款") Then
dra("已付款") = dra("应付款")
je = je - dra("应付款")
Else
dra("已付款") = je
je = 0
End If
Next
Next
dt1.save
3、如果会存储过程，建议直接在存储过程里处理