怎样加快统计运算速度 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

有点甜老师：你好！

下面是计算期初库存，有4000行，运算速度十多分钟，怎样加快？麻烦修改。

Dim QueryMonth As Date '自定义日期

Dim QueryMonth1 As Date '自定义日期

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 SDShangDate As New Date(QueryMonth.Today.Year-1, 1,1) '当前日期的上一年第一天

Dim SZShangDate As New Date(QueryMonth.Today.Year-1, 12,31) '当前日期的上一年最后一天

Dim StartMonth As Date '月份第一天

'Dim dr As DataRow = e.DataRow

QueryMonth=e.F orm.Controls("起始日期").Value

QueryMonth1=e.F orm.Controls("结束日期").Value

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 = New Date(QueryMonth.Year,QueryMonth.Month-1,1) '上月第一天

Dim sFirstDay As Date = FirstDay.AddMonths(-1)'上月第一天

'Dim sLastDay As Date = New Date(QueryMonth.Year,QueryMonth.Month-1,Date.DaysInMonth(QueryMonth.Year,QueryMonth.Month-1)) '上月最后一天

Dim sLastDay As Date = LastDay.AddMonths(-1)'上月最后一天

Dim jbqc As New SQLJoinTableBuilder("查询表1","存货目录")

jbqc.AddTable("存货目录","存货编码","期初库存","存货编码")

jbqc.C

Dim dtqc As DataTable

jbqc.AddCols("{存货目录}.大类","{存货目录}.小类","{存货目录}.存货编码","{存货目录}.存货名称","{存货目录}.大单位","{存货目录}.规格","仓库","{存货目录}.品牌产地","期初数量","期初金额","日期")

'jb1.AddCols("大类","小类","存货编码","存货名称","存货规格","存货单位","仓库")

jbqc.Filter = "{期初库存}.日期 >= '" & sFirstDay & "' And {期初库存}.日期 <= '" & sLastDay & "'"

dtqc = jbqc.Build(True) '生成一个DataTable

Dim jbrk As New SQLJoinTableBuilder("查询表1","存货目录")

jbrk.AddTable("存货目录","存货编码","入库明细","存货编码")

jbrk.C

Dim dtrk As DataTable

jbrk.AddCols("{存货目录}.大类","{存货目录}.小类","{存货目录}.存货编码","{存货目录}.存货名称","{存货目录}.大单位","{存货目录}.规格","仓库","{存货目录}.品牌产地","{入库明细}.入库数量","{入库明细}.入库金额","{入库明细}.日期")

jbrk.Filter = "{入库明细}.日期 >= '" & FirstDay & "' And {入库明细}.日期 <= '" & LastDay & "'"

dtrk = jbrk.Build(True) '生成一个DataTable

Dim jbck As New SQLJoinTableBuilder("查询表1","存货目录")

jbck.AddTable("存货目录","存货编码","出库明细","存货编码")

jbck.C

Dim dtck As DataTable

jbck.AddCols("{存货目录}.大类","{存货目录}.小类","{存货目录}.存货编码","{存货目录}.存货名称","{存货目录}.大单位","{存货目录}.规格","仓库","{存货目录}.品牌产地","{出库明细}.出库数量","{出库明细}.出库金额","{出库明细}.日期")

jbck.Filter = "{出库明细}.日期 >= '" & FirstDay & "' And {出库明细}.日期 <= '" & LastDay & "'"

dtck = jbck.Build(True) '生成一个DataTable

Dim jbth As New SQLJoinTableBuilder("查询表1","存货目录")

jbth.AddTable("存货目录","存货编码","退货明细","存货编码")

jbth.C

Dim dtth As DataTable

jbth.AddCols("{存货目录}.大类","{存货目录}.小类","{存货目录}.存货编码","{存货目录}.存货名称","{存货目录}.大单位","{存货目录}.规格","仓库","{存货目录}.品牌产地","{退货明细}.退货数量","{退货明细}.金额","{退货明细}.日期")

jbth.Filter = "{退货明细}.日期 >= '" & FirstDay & "' And {退货明细}.日期 <= '" & LastDay & "'"

dtth = jbth.Build(True) '生成一个DataTable

Dim jbtl As New SQLJoinTableBuilder("查询表1","存货目录")

jbtl.AddTable("存货目录","存货编码","退料明细","存货编码")

jbtl.C

Dim dttl As DataTable

jbtl.AddCols("{存货目录}.大类","{存货目录}.小类","{存货目录}.存货编码","{存货目录}.存货名称","{存货目录}.大单位","{存货目录}.规格","仓库","{存货目录}.品牌产地","{退料明细}.退料数量","{退料明细}.金额","{退料明细}.日期")

jbtl.Filter = "{退料明细}.日期 >= '" & FirstDay & "' And {退料明细}.日期 <= '" & LastDay & "'"

dttl = jbtl.Build(True) '生成一个DataTable

Dim nums As new List(of String)

Dim Vals As List(of String())

Vals = dt.GetValues("存货编码|存货名称|大单位|规格|大类|小类|品牌产地")

Dim cmd As new sqlcommand

cmd.C

cmd.CommandText = "select * f rom {期初库存}"

Dim dt1 As DataTable = cmd.ExecuteReader(True)

For Each drt As DataRow In dt1.DataRows

Dim qcsl As Double = dtqc.Compute("Sum(期初数量)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & sFirstDay & "' And 日期 <= '" & sLastDay & "'")

Dim qcje As Double = dtqc.Compute("Sum(期初金额)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & sFirstDay & "' And 日期 <= '" & sLastDay & "'")

Dim rksl As Double = dtrk.Compute("Sum(入库数量)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & FirstDay & "' And 日期 <= '" & LastDay & "'")

Dim rkje As Double = dtrk.Compute("Sum(入库金额)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & FirstDay & "' And 日期 <= '" & LastDay & "'")

Dim cksl As Double = dtck.Compute("Sum(出库数量)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & FirstDay & "' And 日期 <= '" & LastDay & "'")

Dim ckje As Double = dtck.Compute("Sum(出库金额)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & FirstDay & "' And 日期 <= '" & LastDay & "'")

Dim thsl As Double = dtth.Compute("Sum(退货数量)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & FirstDay & "' And 日期 <= '" & LastDay & "'")

Dim thje As Double = dtth.Compute("Sum(金额)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & FirstDay & "' And 日期 <= '" & LastDay & "'")

Dim tlsl As Double = dttl.Compute("Sum(退料数量)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & FirstDay & "' And 日期 <= '" & LastDay & "'")

Dim tlje As Double = dttl.Compute("Sum(金额)"," [存货编码] = '" & drt("存货编码") & "' and 日期 >= '" & FirstDay & "' And 日期 <= '" & LastDay & "'")

drt("账面数量") = qcsl + rksl - cksl - thsl + tlsl

drt("账面金额") = qcje + rkje - ckje - thje - tlje

drt("期初数量") = qcsl + rksl - cksl - thsl + tlsl

drt("期初金额") = qcje + rkje - ckje - thje - tlje

If (qcsl + rksl - cksl - thsl + tlsl) > 0 Then

drt("账面单价") = ( qcje + rkje - ckje - thje - tlje ) / (qcsl + rksl - cksl - thsl + tlsl)

drt("期初单价") = ( qcje + rkje - ckje - thje - tlje ) / (qcsl + rksl - cksl - thsl + tlsl)

Else

drt("账面单价") = 0

drt("期初单价") = 0

End If

dt1.save

主题：怎样加快统计运算速度