Foxtable(狐表)用户栏目专家坐堂 → 跨表统计的运算速度


  共有2488人关注过本帖树形打印复制链接

主题:跨表统计的运算速度

帅哥哟,离线,有人找我吗?
zhangxl1964
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:二尾狐 帖子:552 积分:5322 威望:0 精华:0 注册:2011/6/7 13:33:00
跨表统计的运算速度  发帖心情 Post By:2017/10/15 9:52:00 [只看该作者]

有点甜老师:你好!
     帮助看看下面跨表统计的代码,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

 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2017/10/15 14:43:00 [只看该作者]

1、你使用重置列的方式?

 

2、不要频繁使用sqlcommand,如果你datacolchanged使用了,那请不要麻木使用重置列。

 

3、sqlcommand的效率是很低的,比如执行一个,有可能要1秒。那3000行,就很长时间了。如果需要把表数据重新计算一次,那单独做个按钮,把需要统计的数据先加载出来,不要每次都去后台查。


 回到顶部