access vba程序循环处理10万级别数据慢的问题

gxlubinrobin2 2018-05-25 11:04:54
目前我有个vba的程序，按了按钮后会根据输入数据源（也就是第一张图片里的这些excel），生成我想要的excel文件，在按钮事件里会调用一个AllocateCommMaterial方法，现在的问题是点了按钮，我电脑啥都不干的情况下需要运行1小时50分左右，主要的时间都花在这个AllocateCommMaterial方法，这个方法大约需要1小时30多分钟。
现在我就是想优化这块的速度，请问大神有好的建议吗？

备注：刚开始我不知道是这个AllocateCommMaterial方法慢，以后是使用了access数据库的原因导致的慢，所以想是不是换成其他数据库，用其他语言就会提升速度，但是再看了这个方法里面的逻辑以后，我认为就算改成其他数据库，其他语言，如果按照现在的这种处理流程，应该也是白搭,b不知道我这样理解对不对？（因为这个方法里要循环10万左右的数据，每个循环里面还要更新另外2个表的对应id相同的记录）




AllocateCommMaterial方法


Function AllocateCommMaterial(Threshold As Double)



Dim rstQry As Recordset

Dim Part As String

Dim WO As String

Dim WOShortage As Long

Dim ETA As Long

Dim i As Integer

Dim Wi As Long



PartFlag = 0

WOFlag = 0



Set rstQry = CurrentDb.OpenRecordset("0403_Comm_WO_Parts_Status")



rstQry.MoveLast

rstQry.MoveFirst



WOPre = "0000"



DoCmd.SetWarnings (False)



While Not rstQry.EOF



WO = rstQry![WO No]

WOShortage = rstQry![WO Demand Qty]

Part = rstQry![Part No]



'pg.Run Count, RowCount, "正在运行:" & Part



PartOHNet = IIf(IsNull(rstQry![OH Net]), 0, rstQry![OH Net])

PartOHAllocated = IIf(IsNull(rstQry![OH Allocated Qty]), 0, rstQry![OH Allocated Qty])



PartReceNet = IIf(IsNull(rstQry![Receiving Net]), 0, rstQry![Receiving Net])

PartReceAllocated = IIf(IsNull(rstQry![Receiving Allocated Qty]), 0, rstQry![Receiving Allocated Qty])



PartTransitNet = IIf(IsNull(rstQry![Transit Net]), 0, rstQry![Transit Net])

PartTransitAllocated = IIf(IsNull(rstQry![Transit Allocated Qty]), 0, rstQry![Transit Allocated Qty])



PartWONet = IIf(IsNull(rstQry![WO Net]), 0, rstQry![WO Net])

PartWOAllocated = IIf(IsNull(rstQry![WO Allocated Qty]), 0, rstQry![WO Allocated Qty])



PartOSPNet = IIf(IsNull(rstQry![OSP Net]), 0, rstQry![OSP Net])

PartOSPAllocated = IIf(IsNull(rstQry![OSP Allocated Qty]), 0, rstQry![OSP Allocated Qty])



    If WOShortage <= PartOHNet Then    'No shortage

         

         WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'No Shortage', [OH Allocate] = " & WOShortage & _

                    " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"

                  

         PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Allocated Qty] = " & PartOHAllocated + WOShortage & _

                ",[OH Net] =  " & PartOHNet - WOShortage & " WHERE [Part No] = '" & Part & "'"

                

    ElseIf WOShortage <= PartOHNet + PartWONet Then      'No shortage - WO

        

        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'No shortage - WO', [OH Allocate] = " & PartOHNet & _

                    ",[WO Allocate]= " & WOShortage - PartOHNet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"

                    

        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _

                    ",[WO Net] = " & PartWONet - WOShortage + PartOHNet & _

                    ",[WO Allocated Qty] = " & WOShortage - PartOHNet + PartWOAllocated & _

                    ",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"

                    

    ElseIf WOShortage <= PartOHNet + PartWONet + PartOSPNet Then      'OSP'

        

        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'OSP', [OH Allocate] = " & PartOHNet & _

                    ",[WO Allocate] = " & PartWONet & _

                    ",[OSP Allocate]= " & WOShortage - PartOHNet - PartWONet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"

                    

        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _

                    ",[WO Net] = 0" & _

                    ",[OSP Net] = " & PartOSPNet - WOShortage + PartOHNet + PartWONet & _

                    ",[OSP Allocated Qty] = " & WOShortage - PartWONet - PartOHNet + PartOSPAllocated & _

                    ",[WO Allocated Qty]= " & PartWOAllocated + PartWONet & _

                    ",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"

    

    ElseIf WOShortage <= PartOHNet + PartWONet + PartReceNet Then      'Receiving

        

        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'Receiving', [OH Allocate] = " & PartOHNet & _

                    ",[WO Allocate] = " & PartWONet & _

                    ",[OSP Allocate] = " & PartOSPNet & _

                    ",[Receiving Allocate]= " & WOShortage - PartOHNet - PartWONet - PartOSPNet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"

                    

        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _

                    ",[WO Net] = 0" & _

                    ",[OSP Net] = 0" & _

                    ",[Receiving Net] = " & PartReceNet - WOShortage + PartOHNet + PartWONet + PartOSPNet & _

                    ",[Receiving Allocated Qty] = " & WOShortage - PartOHNet - PartWONet - PartOSPNet + PartReceAllocated & _

                    ",[OSP Allocated Qty]= " & PartWOAllocated + PartOSPNet & _

                    ",[WO Allocated Qty]= " & PartWOAllocated + PartWONet & _

                    ",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"

                    

    ElseIf WOShortage <= PartOHNet + PartWONet + PartReceNet + PartTransitNet Then      'Transit

        

        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [Result] = 'Transit', [OH Allocate] = " & PartOHNet & _

                    ",[WO Allocate] = " & PartWONet & _

                    ",[OSP Allocate] = " & PartOSPNet & _

                    ",[Receiving Allocate] = " & PartReceNet & _

                    ",[Transit Allocate]= " & WOShortage - PartOHNet - PartWONet - PartOSPNet - PartReceNet & " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"

                    

        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0" & _

                    ",[WO Net] = 0" & _

                    ",[OSP Net] = 0" & _

                    ",[Receiving Net] = 0" & _

                    ",[Transit Net] = " & PartTransitNet - WOShortage + PartReceNet + PartOSPNet + PartWONet + PartOHNet & _

                    ",[Transit Allocated Qty] = " & WOShortage - PartReceNet - PartOHNet - PartWONet - PartOSPNet + PartTransitAllocated & _

                    ",[Receiving Allocated Qty]= " & PartReceAllocated + PartReceNet & _

                    ",[OSP Allocated Qty]= " & PartOSPAllocated + PartOSPNet & _

                    ",[WO Allocated Qty]= " & PartWOAllocated + PartWONet & _

                    ",[OH Allocated Qty]= " & PartOHAllocated + PartOHNet & " WHERE [Part No] = '" & Part & "'"

                    



    Else   'if Parts are Shortage =      WOShortage > PartOHNet + PartReceNet , then update W1, W2, W3......

        

        WOSQL2 = "UPDATE [41_1_Comm_WO_Analysis_Temp] SET [OH Allocate] = " & PartOHNet & ", [WO Allocate] = " & PartWONet & ", [Receiving Allocate]= " & PartReceNet & ", [Transit Allocate]= " & PartTransitNet & ", [OSP Allocate]= " & PartOSPNet

        

        PartSQL = "UPDATE [28_1_Avaiable_For_Comm_WO] SET [OH Net] = 0, [WO Net] = 0, [Receiving Net] = 0, [Transit Net] = 0, [OSP Net] = 0, [OSP Allocated Qty]= " & PartOSPAllocated + PartOSPNet & ", [Transit Allocated Qty]= " & PartTransitAllocated + PartTransitNet & ", [Receiving Allocated Qty]= " & PartReceAllocated + PartReceNet & ", [WO Allocated Qty] = " & PartWONet + PartWOAllocated & ", [OH Allocated Qty] = " & PartOHNet + PartOHAllocated

        

        ETA = PartOHNet + PartWONet + PartReceNet + PartTransitNet + PartOSPNet

        

        i = 1

        While i <= 54

            

            Wi = IIf(IsNull(rstQry.Fields("W" & i & " Net")), 0, rstQry.Fields("W" & i & " Net"))

            

            If Wi > 0 Then

                

                ETA = ETA + Wi

                

                If ETA < WOShortage Then    'ETA not enough for WOShortage

                    

                    WOSQL2 = WOSQL2 & ",[W" & i & " Allocate] = " & Wi

                    PartSQL = PartSQL & ",[W" & i & " Net] = 0"

                

                Else   'ETA is enough for WOShortage, end the loop

                    

                    If i < 10 Then

                        

                            WOSQL2 = WOSQL2 & ",[W" & i & " Allocate] = " & Wi + WOShortage - ETA & ",[Result] = 'W0" & i & "'" & _

                            " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"

                    

                        Else

                            WOSQL2 = WOSQL2 & ",[W" & i & " Allocate] = " & Wi + WOShortage - ETA & ",[Result] = 'W" & i & "'" & _

                            " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"

                    

                    End If

                    

                        PartSQL = PartSQL & ",[W" & i & " Net] = " & ETA - WOShortage & _

                        " WHERE [Part No] = '" & Part & "'"

                    

                        GoTo LabelA

                

                End If

            

            End If

            

            i = i + 1

        

        Wend



LabelA:

        

        If i = 55 Then

            

            WOSQL2 = WOSQL2 & ",[Result] = 'W55...'" & _

                     " WHERE [WO No] = '" & WO & "' AND [Part No] = '" & Part & "'"

            

            PartSQL = PartSQL & " WHERE [Part No] = '" & Part & "'"

        

        End If



    End If

    

    

    DoCmd.RunSQL WOSQL2

    DoCmd.RunSQL PartSQL

    

WOPre = WO

rstQry.MoveNext





Wend       'Allocate material





End Function


AllocateCommMaterial方法的说明：











...全文
2111 3 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
ACMAIN_CHM 2018-05-25
  • 打赏
  • 举报
 回复
代码太长了，没耐心看了。建议楼主可以先加一些时间输出，看一下具体是哪一句哪，然后再进行分析讨论。
gxlubinrobin2 2018-05-25
  • 打赏
  • 举报
 回复
我现在能想到的是把这个方法用c#去实现，因为access vba里不支持多线程，在C#里面用多线程去循环处理，然后vba调用c#的dll，不知道是不是可行
gxlubinrobin2 2018-05-25
  • 打赏
  • 举报
 回复
不需要仔细看代码，逻辑也不复杂，我写清楚了，这个方法就是要循环处理10万条数据，然后每个循环里都要更新2个表对应的记录，所以慢，是加起来时间慢，不是里面某几句慢
2018年春ACCESS期末测试卷A
2018年春ACCESS期末测试卷A 2018年春ACCESS期末测试卷A
计算机二级题库二.pdf
计算机二级题库二.pdf
计算机二级题库.pdf
计算机二级题库.pdf
vba遍历数组_VBA 在行中循环遍历的例子
在单元格区域对象中循环：For Each c In Range("$A$2:$A$" & Cells(Rows.count, "A").End(xlUp).row‘do somethingNext c基于上述代码运行后，你会发现，少量数据处理上还行，但大量数据之后，电脑卡慢，用时较长等缺点就出来了。那要如何操作才能既快速，又能按行的要求进行循环遍历呢？答案是：使用数组替代对象，然后在数组中...
ACCESS大批量数据写入之性能提升方案
ACCESS数据库中批量写入大数据量时，最让人头疼的就是效率问题了。我一直也被这个问题困扰着。最近突然想起一个方案。利用ACCESS自身的导入功能，实现大数据的迅速导入功能。整体方案是这样的：数据—>文本文件—>MDB 适用范围：大批量数据转换 下面是实现方案，请注意后面的注释。第一、将 数据写入到文本文件中。每一行一条记录，第一行是标题行。这里注意文本文件的格式：列之间使用逗号分
Access

7,732

社区成员

28,799

社区内容

发帖
与我相关
我的任务
社区描述
Microsoft Office Access是由微软发布的关系数据库管理系统。它结合了 MicrosoftJet Database Engine 和 图形用户界面两项特点。
社区管理员
  • Access
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章