我只能把代码优化成这样
Dim stst As Date = Date.Now '将开始时间保存在变量st中
Dim Dic As new Dictionary(of DataRow, Integer)
Dim v As Double
For Each dr As DataRow In DataTables("试验临时数据").DataRows
Dim ly As String
Dim yx As Integer
ly = dr("来源")
yx = dr("有效序")
v = DataTables("试验临时数据").Compute("Count([编号])", "[来源] = '" & ly & "' And [有效序] = '" & yx & "' And 结果 = '合格'")
dic.Add(dr, v)
Next
For Each dr As DataRow In dic.Keys
dr("合格数量") = dic(dr)
Next
MessageBox.Show("耗时: " & (Date.Now - stst).TotalSeconds & "秒") '计算并显示执行代码所花费的秒数
处理4104条数据花费时间0.17秒
改用上述代码后,整体代码为:
'删除临时数据表报有行
Dim stst As Date = Date.Now '将开始时间保存在变量st中
For i As Integer = Tables("试验临时数据").Rows.count-1 To 0 Step -1
Tables("试验临时数据").Rows(i).Delete
Next
'生成表名
Dim st As Date = Date.Now
Dim d As Date = Date.Today
Dim dd As String
dd = Format(d, "yyMMdd")
dd = CStr(dd)
dd = "t" & dd
'合并外部数据源中的数据到临时数据表中
For Each cn As Connection In Connections
Dim s As String = cn.ConnectionString
If Connections.TryConnect(s) = True Then
'MessageBox.Show("数据源无法连通!")
If left(cn.Name,1) = "M" Then
If cn.GetTableNames.Contains(dd) Then
Dim mg As New Merger
mg.ConnectionName = cn.Name '指定数据源名称
mg.SourceTableName = dd '指定要合并的表
mg.DataTableName = "试验临时数据" '指定接收数据的表
mg.Merge() '开始合并
End If
End If
For Each dr1 As DataRow In DataTables("试验临时数据").Select("来源 is null or 来源 = ''")
dr1("来源") = cn.Name
dr1("日期") = st
Next
End If
Next
'生成数据源序
For Each ly As String In DataTables("试验临时数据").GetValues("来源")
Dim i As Integer = 1
For Each dr As DataRow In DataTables("试验临时数据").Select("来源 = '" & ly & "'", "_Identify")
dr("数据序") = i
dr("有效序") = Math.Ceiling(i/3)
i += 1
Next
Next
For Each ly2 As String In DataTables("试验临时数据").GetValues("来源")
Dim d1, d2, d3 As DataRow
d1 = DataTables("试验临时数据").find("来源 = '" & ly2 & "'", "数据序 desc", 0)
If d1("数据序") Mod 3 <> 0 Then
d2 = DataTables("试验临时数据").find("来源 = '" & ly2 & "'", "数据序 desc", 1)
If d2("数据序") Mod 3 <> 0 Then
d2.Delete
End If
d1.Delete
End If
Next
Dim Dic As new Dictionary(of DataRow, Integer)
Dim v As Double
For Each dr As DataRow In DataTables("试验临时数据").DataRows
Dim ly As String
Dim yx As Integer
ly = dr("来源")
yx = dr("有效序")
v = DataTables("试验临时数据").Compute("Count([编号])", "[来源] = '" & ly & "' And [有效序] = '" & yx & "' And 结果 = '合格'")
dic.Add(dr, v)
Next
For Each dr As DataRow In dic.Keys
dr("合格数量") = dic(dr)
Next
MessageBox.Show("耗时: " & (Date.Now - st).TotalSeconds & "秒") '计算并显示执行代码所花费的秒数
处理4104条数据,花费时间由原来的28秒减少到1.51秒,还能再优化不。
另将上述代码放在窗口 Afterload中 与命令窗口中(命令窗口中加上单元格暂停绘制)执行时间在命令窗口中明显有长于在窗口中执行时间。
如果是原未改进的代码 ,在窗口中需时间28秒左右,在命令窗口需要 51秒左右。为什么 ?