请教两个执行速度相差超过十倍以上的代码进行优化的问题。

Question

如下两个方法，为何执行速度相差超过十倍以上？
请教高质量的优化代码，提高函数的速度。谢谢。
Private Function RecordCost() As Integer
    Dim i As Long
    Dim m As Long
    Dim rs As Recordset
    Dim strSql As String
    Dim conn As New ADODB.Connection    RecordCost = -1    '读取业务单据的内容写入临时表
    strSql = "SELECT..INTO TblCostTemp..."
    conn.Execute strSql    strSql = "SELECT ...FROM TblCostTemp..."
    rs.Open strSql, conn, 1, 3
    For i = 0 To rs.RecordCount - 1
        m = m + 1
        Debug.Print m
        'RecordCount=40000
        '读取货品代码
        If InStr(rs.Fields("货品代码").Value, strGoodsID) < 1 Then
            '...
            '根据一定的规则计算单据并更新
            strSql = "..."
            conn.Execute strSql
        End If
        rs.MoveNext
    Next
    If rs.State = 1 Then rs.Close
    strSql = "DROP TABLE TblCostTemp"
    conn.Execute strSql
    '整个循环执行4万次，耗时半小时
    RecordCost = 0
End Function
Private Function RecordCostNew() As Integer
    Dim i As Long
    Dim j As Long
    Dim m As Long
    Dim strSql As String
    
    Dim rs As Recordset
    Dim rc As Recordset
    Dim conn As New ADODB.Connection    RecordCost = -1    strSql = "SELECT ...FROM TblCostTemp..."
    rs.Open strSql, conn, 1, 3
    For i = 0 To rs.RecordCount - 1
        strGoodsID = rs.Fields("GoodsID").Value
        strSql = "SELECT ... FROM ... WHERE GoodsID='" & strGoodsID & "'"
        rc.Open strSql, conn, 1, 3
        If Not rc.EOF Then
            '...
        End If
        '多次执行rc.Open strSql, conn, 1, 3，以取得单据的某些内容
        If rc.State = 1 Then rc.Close        strSql = "SELECT ... FROM ... WHERE GoodsID='" & strGoodsID & "'"
        rg.Open strSql, conn, 1, 3
        For j = 0 To rg.RecordCount - 1
            m = m + 1
            Debug.Print m
            '...
            '根据一定的规则计算单据并更新
            rg.Update
            rg.MoveNext
        Next
    Next
    '整个循环执行4万次，耗时3分钟
    RecordCost = 0
End Function

Answer 1

看lz的代码，或者搞反了，或者隐藏了重要的，代码和速度不对lz可以调适运行，跟踪看时间浪费在哪里，如果循环部分代码能用sql或存储过程会提高速度

Answer 2

那他下面的strSql = "..."是什么样的？可能是这地方耽误时间了，还有，你在上面的代码的For i = 0 To rs.RecordCount - 1的下面加上DoEvents试试看是不是变快了。

Answer 3

不能取你要的GoodsID的记录来循环吗

Answer 4

我个人感觉conn.Execute strSql的速度要快于update,何况update程序断中又用了多的记录源，怎么会返到快呢？仔细观查你的代码中的问题，你提供的程序中看不出什么。

Answer 5

Private Function RecordCostNew() As Integer
        '省略了所有的变量声明，出错处理等代码，只保留循环主体
        strSql = "SELECT [代码] FROM [货品物料] WHERE isfather=0 AND [仓库代码]='" & strStoreID & "'"
        rs.Open strSql, cn, 1, 3
        '进度条控制
        lngRecordCount = rs.RecordCount
        If lngRecordCount > 0 Then objBar.Max = lngRecordCount
        objBar.Value = 0
        DoEvents
        For i = 0 To lngRecordCount - 1
            DoEvents
            objBar.Value = objBar.Value + 1
            '根据货品代码核算成本
            strGoodsID = rs.Fields(0) & " "
            If Len(Trim$(strGoodsID)) > 1 Then
                strGoodsID = Trim$(strGoodsID)                '获取结存存货实际成本、数量
                strSql = "SELECT SUM(b.[收入数量]) AS 收入数量,SUM(b.[发出数量]) AS 发出数量,SUM(b.[收入成本]) AS 收入成本货款,SUM(b.[发出成本]) AS [发出成本货款] FROM [业务单据] b" _
                         & "  WHERE b.[货品代码]='" & strGoodsID & "' AND b.[日期]<" & MyApp.FormatDateWithBrackets(strStartDate) & strStoreWhere
                rg.Open strSql, cn, 1, 3
                If Not rg.EOF Then
                    dblStockMoney = Val(rg.Fields(2) & "") - Val(rg.Fields(3) & "")                  '结存金额
                    dblStockAmount = Val(rg.Fields(0) & "") - Val(rg.Fields(1) & "")                 '结存数量
                    If dblStockAmount <> 0 Then dblStockPrice = dblStockMoney / dblStockAmount       '结存单价
                    dblOutMoney = 0                                                                  '发出成本
                End If
                If rg.State = 1 Then rg.Close                '处理单据
                strSql = " SELECT b.前缀,b.日期,b.单号,b.退货对应单号,b.收入数量,b.虚拟收入数量,b.发出数量,b.虚拟发出数量,b.单位换算率,b.单价,b.库存单价,b.收入成本,b.发出成本,b.费用,b.可抵扣费用,b.可抵扣费用税金 ," _
                         & " a.[代码] AS 企业代码,a.[名称] as [企业名称],c.[核算数量],c.[核算成本],c.[核算往来],c.[往来类别] " _
                         & " FROM (  [企业公司] a INNER JOIN  [业务单据] b ON a.[代码]=b.[企业代码]) LEFT JOIN [单据类型] c ON b.[前缀]=c.[前缀]  " _
                         & "  WHERE b.[货品代码]='" & strGoodsID & "' AND b.[日期]>=" & MyApp.FormatDateWithBrackets(strStartDate) & " AND b.[日期]<" & MyApp.FormatDateWithBrackets(DateAdd("d", 1, strEndDate)) & " " _
                         & " " & strStoreWhere & " AND (c.[核算数量]=1 OR c.[核算成本]=1) ORDER BY b.[日期],b.[序号]"
                rg.Open strSql, cn, 1, 3
                For j = 0 To rg.RecordCount - 1
                    '判断是否核算费用（0或者1），计算该笔货品进出的费用
                    If Not IsNumeric(rg.Fields("费用") & "") Then rg.Fields("费用") = 0
                    If Not IsNumeric(rg.Fields("可抵扣费用") & "") Then rg.Fields("可抵扣费用税金") = 0
                    If Not IsNumeric(rg.Fields("可抵扣费用税金") & "") Then rg.Fields("可抵扣费用") = 0
                    intIsExpense = 0
                    dblExpense = 0                    '开始重新核算成本，（写入收入或发出成本）
                    '收入货品=采购进货+退货+调拨
                    If rg.Fields("收入数量") <> 0 Or rg.Fields("虚拟收入数量") <> 0 Then
                        '.....
                    ElseIf rg.Fields("发出数量") <> 0 Or rg.Fields("虚拟发出数量") <> 0 Then '发出货品=销售+采购退货+调拨
                         '.....
                    End If                    '成本取8位小数计算
                    If Abs(rg.Fields("收入成本")) < 1 * 10 ^ -8 Then
                        rg.Fields("收入成本") = 0
                    Else
                        rg.Fields("收入成本") = MyApp.round45(rg.Fields("收入成本"), 2)
                    End If
                    If Abs(rg.Fields("发出成本")) < 1 * 10 ^ -8 Then
                        rg.Fields("发出成本") = 0
                    Else
                        rg.Fields("发出成本") = MyApp.round45(rg.Fields("发出成本"), 2)
                    End If                    dblStockMoney = dblStockMoney + Val(rg.Fields("收入成本") & "") - Val(rg.Fields("发出成本") & "")                 '结存金额
                    dblStockAmount = dblStockAmount + Val(rg.Fields("收入数量") & "") - Val(rg.Fields("发出数量") & "")               '结存数量
                    dblStockAmount = 0
                    If dblStockAmount <> 0 Then dblStockPrice = dblStockMoney / dblStockAmount                                        '结存单价
                    rg.Update
                    rg.MoveNext
                Next
                If rg.State = 1 Then rg.Close            End If
            rs.MoveNext
        Next
        If rs.State = 1 Then rs.Close
End Function

Answer 6

Private Function RecordCost() As Integer
        '省略了所有的变量声明，出错处理等代码，只保留循环主体
        strGoodsID = ""
        'strSql = "SELECT [代码] FROM [货品物料] WHERE isfather=0 AND [仓库代码]='" & strStoreID & "'"
        '处理单据
        strSql = " SELECT b.货品代码,e.收入数量 AS 累计收入数量,e.发出数量 AS 累计发出数量,e.收入成本货款 AS 累计收入成本,e.发出成本货款 AS 累计发出成本, " _
                 & " b.前缀,b.日期,b.单号,b.退货对应单号,b.收入数量,b.虚拟收入数量,b.发出数量,b.虚拟发出数量,b.单位换算率,b.单价,b.库存单价,b.收入成本," _
                 & " b.发出成本,b.费用,b.可抵扣费用,b.可抵扣费用税金 ,a.[代码] AS 企业代码,a.[名称] AS [企业名称],c.[核算数量],c.[核算成本],c.[核算往来],c.[往来类别] INTO TblCostTemp" _
                 & " FROM ((([企业公司] a INNER JOIN  [业务单据] b ON a.[代码]=b.[企业代码]) " _
                 & "LEFT JOIN [单据类型] c ON b.[前缀]=c.[前缀]) " _
                 & " LEFT JOIN 货品物料 f ON f.代码=b.[货品代码] )  " _
                 & "LEFT JOIN (SELECT h.货品代码,SUM(h.[收入数量]) AS 收入数量,SUM(h.[发出数量]) AS 发出数量,SUM(h.[收入成本]) AS 收入成本货款,SUM(h.[发出成本]) AS [发出成本货款]  FROM [业务单据] h " _
                 & "  WHERE  h.[日期]<" & MyApp.FormatDateWithBrackets(strStartDate) & " " & strStoreWhere & " GROUP BY h.货品代码  ) e ON f.代码=e.[货品代码] " _
                 & "   WHERE f.isfather=0 AND b.[日期]>=" & MyApp.FormatDateWithBrackets(strStartDate) & " AND b.[日期]<" & MyApp.FormatDateWithBrackets(DateAdd("d", 1, strEndDate)) & " " _
                 & " " & strStoreWhere & " AND (c.[核算数量]=1 OR c.[核算成本]=1) ORDER BY b.[日期],b.[序号]"
        '写入临时表
        cn.Execute strSql        strSql = "SELECT 货品代码,累计收入数量,累计发出数量,累计收入成本,累计发出成本, " _
                 & " 前缀,日期,单号,退货对应单号,收入数量,虚拟收入数量,发出数量,虚拟发出数量,单位换算率,单价,库存单价,收入成本," _
                 & " 发出成本,费用,可抵扣费用,可抵扣费用税金 ,企业代码,[企业名称],[核算数量],[核算成本],[核算往来],[往来类别] FROM TblCostTemp"
        rs.Open strSql, cn, 1, 3        '进度条控制
        lngRecordCount = rs.RecordCount
        If lngRecordCount > 0 Then objBar.Max = lngRecordCount
        objBar.Value = 0
        DoEvents
        For i = 0 To lngRecordCount - 1
            DoEvents
            objBar.Value = objBar.Value + 1
            '根据货品代码核算成本
            If InStr(rs.Fields("货品代码").Value, strGoodsID) < 1 Then
                strGoodsID = rs.Fields("货品代码").Value
                dblStockMoney = Val(rs.Fields("累计收入成本").Value & "") - Val(rs.Fields("累计发出成本").Value & "")                  '结存金额
                dblStockAmount = Val(rs.Fields("累计收入数量").Value & "") - Val(rs.Fields("累计发出数量").Value & "")                '结存数量
                If dblStockAmount <> 0 Then dblStockPrice = dblStockMoney / dblStockAmount       '结存单价
                dblOutMoney = 0                                                                  '发出成本
            End If            dblOrderInCost = 0    '每单据的收入成本
            dblOrderOutCost = 0   '每单据的发出成本
            dblOrderPrice = 0     '每单据的库存单价            '判断是否核算费用（0或者1），计算该笔货品进出的费用
            If Not IsNumeric(rs.Fields("费用") & "") Then rs.Fields("费用") = 0
            If Not IsNumeric(rs.Fields("可抵扣费用") & "") Then rs.Fields("可抵扣费用税金") = 0
            If Not IsNumeric(rs.Fields("可抵扣费用税金") & "") Then rs.Fields("可抵扣费用") = 0
            intIsExpense = 0
            dblExpense = 0            '开始重新核算成本，（写入收入或发出成本）
            '收入货品=采购进货+退货+调拨
            If rs.Fields("收入数量") <> 0 Or rs.Fields("虚拟收入数量") <> 0 Then
                '....
            ElseIf rs.Fields("发出数量") <> 0 Or rs.Fields("虚拟发出数量") <> 0 Then '发出货品=销售+采购退货+调拨
                '....
            End If            '成本取8位小数计算
            If Abs(dblOrderInCost) < 1 * 10 ^ -8 Then
                dblOrderInCost = 0
            Else
                dblOrderInCost = MyApp.round45(dblOrderInCost, 2)
            End If
            If Abs(dblOrderOutCost) < 1 * 10 ^ -8 Then
                dblOrderOutCost = 0
            Else
                dblOrderOutCost = MyApp.round45(dblOrderOutCost, 2)
            End If            dblStockMoney = dblStockMoney + dblOrderInCost - dblOrderOutCost                                                  '结存金额
            dblStockAmount = dblStockAmount + Val(rs.Fields("收入数量") & "") - Val(rs.Fields("发出数量") & "")               '结存数量
            dblStockAmount = 0
            If dblStockAmount <> 0 Then dblStockPrice = dblStockMoney / dblStockAmount                                        '结存单价            '将重新核算后的成本写入数据库
            strSql = "UPDATE 业务单据 SET  收入成本=" & dblOrderInCost & ",发出成本=" & dblOrderOutCost & ",库存单价=" & dblOrderPrice & " WHERE 单号='" & rs.Fields("单号").Value & "' AND 货品代码='" & strGoodsID & "'"
            cn.Execute strSql            rs.MoveNext
        Next
        If rs.State = 1 Then rs.Close
        '删除临时表
        strSql = "DROP TABLE TblCostTemp"
        cn.Execute strSql
End Function
我也觉得应该是反了，RecordCost应该要比RecordCostNew执行得快，但是实际上，RecordCostNew只要三分钟，RecordCost超过了30分钟。

Answer 7

这个是进销存里面的成本核算的，我也是第一次做这个东西，然后数据库要支持access和sql。
我的sql语句还可以优化吗？

Answer 8

关键是 RecordCost() 中，rs.Open 的查询中没有过滤条件，你等于要将整个表的记录处理一遍。
而 RecordCostNew() 只选取了相关的记录进行处理。
数据量不一样，速度当然不同了。