Access转换为SQL server后台之代码优化-杨雪
Access软件网QQ交流学习群(群号码198465573),欢迎您的加入!
首页 >技术文章> Access数据库-模块/函数/VBA


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

发表时间:2017/11/21 8:18:58 评论(1) 浏览(5194)  评论 | 加入收藏 | 复制
   
摘 要:在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"




Access软件网交流QQ群(群号:198465573)
 
 相关文章
存储过程编写经验和优化措施  【周芳(转)  2013/9/17】
通过视图解决当后台为SQL,客户端使用左右联接导致查询速度很慢。  【易勋  2015/7/2】
【Access】布局视图,设计视图,窗体视图特点  【杨雪  2016/7/19】
优化SQL查询:如何写出高性能SQL语句  【宏鹏  2017/10/7】
在Access快速开发平台中调用SqlServer存储过程优化性能...  【红尘如烟  2017/10/20】
常见问答
技术分类
相关资源
文章搜索
关于作者

杨雪

文章分类

文章存档

友情链接