SQL中执行1秒完成。
窗口中数据执行完再保存为3分多钟。
如果查询到的数据不写入到表中也要70多秒。
如果是每行都保存乌龟一行的慢。
可否直接保存在后台数据表中。
Dim st As Date = Date.Now
Dim tbd As DataTable = DataTables("单位成本")
Dim cmd As New SQLCommand
Dim dt As DataTable
tbd.StopRedraw()
cmd.C
cmd.CommandText = "S elect 成品代号,convert( Char(4),year(缴库日期)) + ltrim(convert( Char(2),month(缴库日期))) As 年月,round(sum(生产成本)/sum(数量),6) As 单位成本 " & _
"f rom 缴库明细 wh ere 缴库类型='1正常制令缴库' and 剔除 is null and 缴库日期 > '2016-12-31' gr oup by 成品代号,year(缴库日期),month(缴库日期) " & _
"union all " & _
"S elect 成品代号,convert( Char(4),year(缴库日期)) As 年月,round(sum(生产成本)/sum(数量),6) As 单位成本 from 缴库明细 " & _
"wh ere 缴库类型='1正常制令缴库' and 剔除 is null and 缴库日期 > '2016-12-31' group by 成品代号,year(缴库日期) "
dt = cmd.ExecuteReader
'不足行时,表增加相应行----------------------------------------------------------↓
If dt.DataRows.Count > tbd.DataRows.Count Then
tbd.AddNew(dt.DataRows.Count - tbd.DataRows.Count)
End If
'清空表中数据-------------------------------------------------------------------↓
For Each dr As DataRow In tbd.DataRows
For Each dc As DataCol In tbd.DataCols
dr(dc.Name) = Nothing
Next
Next
'进度条相关设置-------------------------------------------------------------------↓
Dim p As WinForm.ProgressBar
p = e.Form.Controls("ProgressBar1")
p.Maximum = dt.DataRows.Count '设置最大值
p.Minimum = 0 '设置最小值
'查询到的数据赋值----------------------------------------------------------------↓
For i As Integer = 0 To dt.DataRows.Count - 1
Dim tbdr As DataRow = tbd.DataRows(i)
Dim dtr As DataRow = dt.DataRows(i)
For Each tbc As DataCol In tbd.DataCols
tbdr(tbc.Name) = dtr(tbc.Name)
Next
'tbdr.Save
If i Mod 100 = 0 Then
p.Value = i '当前值为已经完成的行数
End If
Next
'设置数值列数字格式------------------------------------------------------------------↓
For Each tbc1 As DataCol In tbd.DataCols
If tbc1.IsNumeric Then
tbc1.SetFormat("0.0")
End If
Next
'-收尾设置-----------------------------------------------------------------------------↓
tbd.Save
Tables("单位成本").AutoSizeCols
p.Value = dt.DataRows.Count
tbd.ResumeRedraw
MessageBox.Show("耗时: " & (Date.Now - st).TotalSeconds & "秒") '计算并显示执行代码所花费的秒数
加好友 
发短信
Post By：2020/7/7 21:42:00 [只看该作者]
