有点甜老师：你好！
帮助看看下面跨表统计的代码，3000多行的数据统计需花费20分钟，如何修改代码提高运算速度？
Dim dr As DataRow = e.DataRow
If e.DataCol.Name = "存货编码" Then
Dim QueryMonth As Date '自定义日期
Dim QueryMonth1 As Date '自定义日期
Dim CKQueryMonth As Date '自定义日期
Dim CKJQueryMonth As Date '自定义日期
CKJQueryMonth=Forms("进销存统计").Controls("结束日期").value
CKQueryMonth =Forms("进销存统计").Controls("起始日期").value
Dim StartDate As New Date(QueryMonth.Today.Year, 1, 1) '当前日期的年度第一天
Dim EndDate As New Date(QueryMonth.Today.Year, 12 ,31) '当前日期的年度最后一天
Dim ShangDate As New Date(QueryMonth.Today.Year-1) '当前日期的上一年
Dim SDStartDate As New Date(QueryMonth.Today.Year-1, 1, 1) '当前日期的上年度第一天
Dim SZEndDate As New Date(QueryMonth.Today.Year-1, 12 ,31) '当前日期的上年度最后一天
Dim StartMonth As Date '月份第一天
'Dim khNum As String '员工编号
'khNum=e.DataRow("客户编码")
Dim Num As String '员工编号
Num=e.DataRow("存货编码")
QueryMonth=e.DataRow("日期")
StartMonth=CStr(Year(QueryMonth)) & "-" & CStr(Month(QueryMonth)) & "-1"
Dim FirstDay As Date = New Date(QueryMonth.Year,QueryMonth.Month,1) '当前月度第一天
Dim LastDay As Date = New Date(QueryMonth.Year,QueryMonth.Month,Date.DaysInMonth(QueryMonth.Year,QueryMonth.Month)) '当前月度最后一天
'Dim sFirstDay As Date = FirstDay.AddMonths(-1)'上月第一天
'Dim sLastDay As Date = New Date(QueryMonth.AddMonths(-1).Year,QueryMonth.AddMonths(-1).Month,Date.DaysInMonth(QueryMonth.AddMonths(-1).Year,QueryMonth.AddMonths(-1).Month)) '上月最后一天
Dim sFirstDay As Date = New Date(QueryMonth.Year,QueryMonth.Month-1,1) '上月第一天
Dim sLastDay As Date = New Date(QueryMonth.Year,QueryMonth.Month-1,Date.DaysInMonth(QueryMonth.Year,QueryMonth.Month-1)) '上月最后一天
Dim rksl As Double
Dim qcsl As Double
Dim cksl As Double
Dim ckje As Double
Dim rkje As Double
Dim qcje As Double
Dim ckdj As Double
Dim thsl As Double
Dim thje As Double
Dim tlsl As Double
Dim tlje As Double
Dim kcsl As Double
Dim kcje As Double
Dim sgcksl As Double
Dim sgckje As Double
Dim rq =dr("日期")
'提取当前日期前,期初库存中的期初数量
Dim cmd3 As New SQLCommand
cmd3.C
Dim dt3 As DataTable
If rq >= "2017-09-01 "
cmd3.CommandText = "SELECT sum([库存数量]) As 期初数量 F rom {进销存统计} Where [存货编码] = '" & num & "' And [日期] >= '" & sFirstDay & "' And [日期] <= '" & sLastDay & "' "
Else
cmd3.CommandText = "SELECT sum([期初数量]) As 期初数量 F rom {期初库存} Where [存货编码] = '" & num & "' And [日期] >= '" & sFirstDay & "' And [日期] <= '" & sLastDay & "' "
End If
dt3 = cmd3.ExecuteReader
If dt3.DataRows.Count > 0 Then
qcsl = Math.Round((dt3.DataRows(0)("期初数量")),2)
'dr("期初数量") = qcsl
End If
'提取当前日期前,期初库存中的期初金额
Dim cmd4 As New SQLCommand
cmd4.C
Dim dt4 As DataTable
If rq >= "2017-09-01 "
cmd4.CommandText = "SELECT sum([库存金额]) As 期初金额 F rom {进销存统计} Where [存货编码] = '" & num & "' And [日期] >= '" & sFirstDay & "' And [日期] <= '" & sLastDay & "' "
Else
cmd4.CommandText = "SELECT sum([期初金额]) As 期初金额 F rom {期初库存} Where [存货编码] = '" & num & "' And [日期] >= '" & sFirstDay & "' And [日期] <= '" & sLastDay & "' "
End If
dt4 = cmd4.ExecuteReader
If dt4.DataRows.Count > 0 Then
'qcje = dt4.DataRows(0)("期初金额")
qcje = Math.Round((dt4.DataRows(0)("期初金额")),2)
'dr("期初金额") = qcje
End If
'提取当前日期前,出库明细中的入库数量
Dim cmd5 As New SQLCommand
cmd5.C
Dim dt5 As DataTable
cmd5.CommandText = "SELECT sum([入库数量]) As 入库数量 F rom {入库明细} Where [存货编码] = '" & num & "' And [日期] >= '" & FirstDay & "' And [日期] <= '" & LastDay & "' and [客户编码] is null "
dt5 = cmd5.ExecuteReader
If dt5.DataRows.Count > 0 Then
rksl = Math.Round((dt5.DataRows(0)("入库数量")),2)
'dr("入库数量") = rksl
End If
'提取当前日期前,出库明细中的入库金额
Dim cmd6 As New SQLCommand
cmd6.C
Dim dt6 As DataTable
cmd6.CommandText = "SELECT sum([入库金额]) As 入库金额 F rom {入库明细} Where [存货编码] = '" & num & "' And [日期] >= '" & FirstDay & "' And [日期] <= '" & LastDay & "' and [客户编码] is null "
dt6 = cmd6.ExecuteReader
If dt6.DataRows.Count > 0 Then
rkje = Math.Round((dt6.DataRows(0)("入库金额")),2)
'dr("入库金额") = rkje
End If
'
'提取当前日期前,退货明细中的入库数量
Dim cmd7 As New SQLCommand
cmd7.C
Dim dt7 As DataTable
cmd7.CommandText = "SELECT sum([退货数量]) As 退货数量 F rom {退货明细} Where [存货编码] = '" & num & "' And [日期] >= '" & FirstDay & "' And [日期] <= '" & LastDay & "' "
dt7 = cmd7.ExecuteReader
If dt7.DataRows.Count > 0 Then
thsl = Math.Round((dt7.DataRows(0)("退货数量")),2)
'dr("退货数量") = thsl
End If
'提取当前日期前,退货明细中的入库金额
Dim cmd8 As New SQLCommand
cmd8.C
Dim dt8 As DataTable
cmd8.CommandText = "SELECT sum([金额]) As 金额 F rom {退货明细} Where [存货编码] = '" & num & "' And [日期] >= '" & FirstDay & "' And [日期] <= '" & LastDay & "' "
dt8 = cmd8.ExecuteReader
If dt8.DataRows.Count > 0 Then
thje = Math.Round((dt8.DataRows(0)("金额")),2)
'dr("退货金额") = thje
End If
'提取当前日期前,出库明细中的入库数量
Dim cmd1 As New SQLCommand
cmd1.C
Dim dt1 As DataTable
cmd1.CommandText = "SELECT sum([出库数量]) As 出库数量 F rom {申购明细} Where [存货编码] = '" & num & "' And [日期] >= '" & FirstDay & "' And [日期] <= '" & LastDay & "' and [AB分类] = 'A' "
dt1 = cmd1.ExecuteReader
If dt1.DataRows.Count > 0 Then
cksl = Math.Round((dt1.DataRows(0)("出库数量")),2)
'dr("出库数量") = cksl
'rksl = cksl
'dr("入库数量") = rksl
End If
'提取当前日期前,出库明细中的入库金额
Dim cmd2 As New SQLCommand
cmd2.C
Dim dt2 As DataTable
cmd2.CommandText = "SELECT sum([进货金额]) As 进货金额 F rom {申购明细} Where [存货编码] = '" & num & "' And [日期] >= '" & FirstDay & "' And [日期] <= '" & LastDay & "' and [AB分类] = 'A' "
dt2 = cmd2.ExecuteReader
If dt2.DataRows.Count > 0 Then
ckje = Math.Round((dt2.DataRows(0)("进货金额")),2)
'dr("出库金额") = ckje
'rkje = ckje
'dr("入库金额") = rkje
End If
'提取当前日期前,退料明细中的入库数量
Dim cmd9 As New SQLCommand
cmd9.C
Dim dt9 As DataTable
cmd9.CommandText = "SELECT sum([退料数量]) As 退料数量 F rom {退料明细} Where [存货编码] = '" & num & "' And [日期] >= '" & FirstDay & "' And [日期] <= '" & LastDay & "' "
dt9 = cmd9.ExecuteReader
If dt9.DataRows.Count > 0 Then
tlsl = Math.Round((dt9.DataRows(0)("退料数量")),2)
'dr("退料数量") = tlsl
End If
'提取当前日期前,退料明细中的入库金额
Dim cmd10 As New SQLCommand
cmd10.C
Dim dt10 As DataTable
cmd10.CommandText = "SELECT sum([金额]) As 金额 F rom {退料明细} Where [存货编码] = '" & num & "' And [日期] >= '" & FirstDay & "' And [日期] <= '" & LastDay & "' "
dt10 = cmd10.ExecuteReader
If dt10.DataRows.Count > 0 Then
tlje = Math.Round((dt10.DataRows(0)("金额")),2)
'dr("退料金额") = tlje
End If
dr("期初金额") = qcje
dr("期初数量") = qcsl
If qcsl <> 0 Then
dr("期初单价") = qcje / qcsl
Else
dr("期初单价") = 0
End If
If rksl-thsl <> 0 Then
dr("入库数量") = rksl - thsl
dr("入库金额") = rkje - thje
dr("入库单价") = (rkje - thje) / (rksl - thsl)
Else
dr("入库单价") = 0
End If
If cksl - tlsl <> 0 Then
dr("出库数量") = cksl - tlsl
dr("出库金额") = ckje - tlje
dr("出库单价") = (ckje - tlje) / (cksl - tlsl)
Else
dr("出库单价") = 0
End If
'
End If
Select Case e.DataCol.Name
Case "存货编码"
dr("库存数量") = dr("期初数量") + dr("入库数量") - dr("出库数量")
dr("库存金额") = dr("期初金额") + dr("入库金额") - dr("出库金额")
dr("库存单价") = Math.Abs(dr("库存金额") / dr("库存数量"))
End Select
Select Case e.DataCol.Name
Case "盘点数量"
If dr.IsNull("盘点数量") Then
dr("盘点金额") = Nothing
Else
dr("库存数量") = dr("盘点数量")
dr("出库数量") = dr("入库数量") - dr("库存数量")
dr("出库金额") = Math.Round(dr("出库数量") * dr("出库单价"),2)
dr("库存单价") = Math.Round((dr("期初金额") + dr("入库金额") ) / (dr("期初数量") + dr("入库数量") ),4)
dr("库存金额") = dr("库存单价") * dr("库存数量")
dr("盘点金额") = dr("库存金额")
End If
End Select
加好友 
发短信
Post By：2017/10/15 9:52:00 [显示全部帖子]
