Access转换为SQL server后台之代码优化

时　间:2017-11-21 08:18:58
作　者:杨雪   ID：42182  城市：南京
摘　要:在access 作为后台数据库时，有些代码运行并不慢，但是转换后台为SQL server 时，就很慢了。什么原因,怎样进行优化呢？下面有一段示例，看一下吧！

正　文:

'原来是这样的，在access作为后台时并不慢
    Dim rs1 As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    Dim i, j As Long
    rs1.Open "tblsales_temp", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    rs2.Open "tbl_ccprk", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    For i = 1 To rs2.RecordCount
        For j = 1 To rs1.RecordCount
           If rs2("rkjg") > 0 Then
              Exit For
           Else
              If rs2("cpbh") = rs1("cpbh") Then
                 If rs2("rkrq") >= rs1("jgsjks") And rs2("rkrq") <= rs1("jgsj") Then
                    rs2!rkjg = rs1!salesjg
                    rs2!rkbz = rs1!bizhong
                    Exit For
                 End If
              End If
              rs1.MoveNext
           End If
        Next
        rs1.MoveFirst
        rs2.MoveNext
    Next

    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing

    MsgBox "已更新产成品入库价格", vbInformation, "提示:"

但是现在更改数据库后台为SQL server 后，上面这段执行很慢。所以代码需要进行优化，改成更新查询。

优化代码：

    '方案一
    DoCmd.RunSQL "update tblsales_temp inner join tbl_ccprk on tblsales_temp.cpbh=tbl_ccprk.cpbh " _
               & " set tbl_ccprk.rkjg=tblsales_temp.salesjg,tbl_ccprk.rkbz=tblsales_temp.bizhong " _
               & " where bl_ccprk.rkrq>=tblsales_temp.jgsjks and  bl_ccprk.rkrq<=tblsales_temp.jgsj"


    '方案二
    DoCmd.RunSQL "update tblsales_temp inner join tbl_ccprk on tblsales_temp.cpbh=tbl_ccprk.cpbh " _
               & " set tbl_ccprk.rkjg=tblsales_temp.salesjg,tbl_ccprk.rkbz=tblsales_temp.bizhong " _
               & " where bl_ccprk.rkrq  between tblsales_temp.jgsjks and  tblsales_temp.jgsj"


  '方案一，简化版
    DoCmd.RunSQL "update tblsales_temp AS X inner join tbl_ccprk AS Y on X.cpbh=Y.cpbh " _
               & " set Y.rkjg = X.salesjg" _
               & "   , Y.rkbz = X.bizhong " _
               & " where bl_ccprk.rkrq>=X.jgsjks and  bl_ccprk.rkrq<=X.jgsj"


  '方案二，简化版
    DoCmd.RunSQL "update tblsales_temp inner join tbl_ccprk on X.cpbh=Y.cpbh " _
               & " set Y.rkjg = X.salesjg" _
               & "   , Y.rkbz = X.bizhong " _
               & " where bl_ccprk.rkrq  between X.jgsjks and  X.jgsj"