VB 执行效率问题

Question

我写了一个方法,里面循环嵌套比较复杂,执行效率特别低 ,大概20多秒才能运行出页面,请问如何提高效率?
If mRs1(0) < mRs2(0) And mRs1(0) > mRs2(2) Then 
sql3 = "update tbl_EmpSarllary " & _ 
"Set JN_yiliao = " & mRs1(0) & " * " & mRs3(0) & " , " & _ 
"JN_yanglao = " & mRs1(0) & " * " & mRs3(1) & " , " & _ 
"JN_shiye = " & mRs1(0) & " * " & mRs3(2) & " " & _ 
"where FEmpID = '" & mFempID & "'" 
mMyCn.Execute (sql3) 

Answer 1

Private Sub setJNSalary(ByVal mYear As Long, ByVal mMonth As Long)
    
    Dim sql As String
    Dim mRs As Object
    Dim mRs3 As Object    Dim mNowDate As Date
    Dim mFempID As String   '员工内码
    Dim mDep As String      '部门
    Dim mPos As String      '职务
    Dim mSgins As String    '船岸标志
    Dim mSta As String      '在职状态
    Dim mCengci As String   '职务层次    Dim mBB As String       '币别
    
    Dim mGWSDate As Date    '船上工资开始日期
    Dim mGWEDate As Date    '船上工资结束日期    Dim mKHSDate As Date    '岸上工资开始日期
    Dim mKHEDate As Date    '岸上工资结束日期
    Dim mDP As String
    Dim mKHMoney As Double
    Dim mRMBKHMoney As Double
    Dim mKHSCount As Double
    Dim mJNSalary As Double '当月缴纳工资    Dim mTemp As Integer
    Dim mOrderKH As Double
    Dim mSF As String       '身份
    
    Dim JN_yanglao As Double    '养老保险缴纳金额
    Dim JN_yiliao As Double     '医疗保险缴纳金额
    Dim JN_shiye As Double      '失业保险缴纳金额
    Dim JN_zhufang As Double    '住房公积金缴纳金额
    
    Dim mRs4 As Object
    Dim mRs5 As Object
    Dim mRs6 As Object
    Dim mRs7 As Object
    Dim mRs8 As Object
    Dim mRs9 As Object
   
    
    On Error Resume Next
    
    PicWait.Visible = True
    PicWait.ZOrder    DoEvents
    '船上人员工资计算日期
    If mMonth = 1 Then
        mGWSDate = mYear - 1 & "-12-11"
        mGWEDate = mYear & "-" & mMonth & "-10"
    Else
        mGWSDate = mYear & "-" & mMonth - 1 & "-11"
        mGWEDate = mYear & "-" & mMonth & "-10"
    End If
    '岸上人员工资计算日期
    If mMonth = 1 Then
        mKHSDate = mYear - 1 & "-12-26"
        mKHEDate = mYear & "-1-25"
    Else
        mKHSDate = mYear & "-" & mMonth - 1 & "-26"
        mKHEDate = mYear & "-" & mMonth & "-25"
    End If
    
    DoEvents
    '初始人员信息查询
    
    '获取人员ID
    sql = "SELECT FHrID From dbo.t_PA_Personal "
    mRMBKHCount = 0
    Set mRs = Nothing
    Set mRs = mMyCn.Execute(sql)
    If Not mRs.EOF And Not mRs.BOF Then
        mRs.MoveFirst
        While Not mRs.EOF
            mTemp = 0
            DoEvents
            
            mFempID = mRs(0)
'            sql = "SELECT V1.DepartmentName, V1.PositionName, H1.HRMS_UserField_3, " & _
'                  " H1.HRMS_UserField_4 , H1.HRMS_UserField_1, H1.HRMS_UserField_2 , H1.HRMS_UserField_11" & _
'                  " FROM v_Report_WorkInfo V1 INNER JOIN " & _
'                  " HM_EmployeesAddInfo H1 ON V1.EM_ID = H1.EM_ID " & _
'                  " WHERE (V1.EM_ID = '" & mFempID & "') "            sql = "SELECT  H1.HRMS_UserField_3,V1.DepartmentName " & _
                  " FROM v_Report_WorkInfo V1 INNER JOIN " & _
                  " HM_EmployeesAddInfo H1 ON V1.EM_ID = H1.EM_ID " & _
                  " WHERE (V1.EM_ID = '" & mFempID & "') "
            Set mRs1 = mMyCn.Execute(sql)
            If Not mRs1.EOF And Not mRs1.BOF Then
                mSgins = mRs1(0)    '原始船岸标志
                mDep = mRs1(1)      'DepartmentName
                'mDep = mRs1(0)      'DepartmentName
                'mPos = mRs1(1)      'PositionName
                'mSgins = mRs1(2)    '原始船岸标志
                'mSta = mRs1(3)      '出勤...
            End If
            
            '判断船币别
            sql = "SELECT FBB From tbl_ZB WHERE (FShippName = '" & mDep & "')"
            Set mRs1 = mMyCn.Execute(sql)
            If Not mRs1.EOF And Not mRs.BOF Then
                If IsNull(mRs1(0)) = False Then
                    mBB = mRs1(0)
                Else
                    mBB = ""
                End If
            Else
                mBB = ""
            End If
            
           If mSgins = "船上" Then
           
                If mBB = "美元" Then
                    '判断是否存在调单
                    sql = "SELECT FEmpID AS 人员ID, FDate AS 调单日期, FNSigns AS 现船岸标志 " & _
                          "FROM tbl_ChangeBill where (fdate between '" & mGWSDate & "' and '" & mGWEDate & "' ) " & _
                          "AND (FEmpID = '" & mFempID & "')"
                        
                    Set mRs2 = mMyCn.Execute(sql)
                        If mRs2.EOF And mRs2.BOF Then
                        '当月不存在调单的情况
                           
                            '根据职务，确定缴纳标准 ***外-无调单***
                            sql = "SELECT tbl_Sta.FJNstardand " & _
                                  "FROM tbl_Sta INNER JOIN " & _
                                  "tbl_EmpSarllary ON tbl_Sta.FInterID = tbl_EmpSarllary.FInterID " & _
                                  "WHERE tbl_EmpSarllary.FEmpID = '" & mFempID & "' "
                            Set mRs3 = mycn.Execute(sql)
                            
                            Dim FJNstardand1 As Long
                            FJNstardand1 = mRs3(0)
                            
                            If Not mRs3.EOF And Not mRs3.BOF Then
        

Answer 2

既然是1对1的关系，那么就一次性将所有信息取出来，这样可以省去循环内的查询。
比如前3个查询可以合并成如下 SQL，而且因为 INNER JOIN 的自动过滤要处理的纪录数也少了。
可以考虑将查询做成一个视图。
SELECT P.FHrID, H1.HRMS_UserField_3, V1.DepartmentName, ZB.FBB
  FROM dbo.t_PA_Personal   P
  JOIN v_Report_WorkInfo   V1 ON V1.EM_ID = P.FHrID
  JOIN HM_EmployeesAddInfo H1 ON V1.EM_ID = H1.EM_ID 
  JOIN tbl_ZB              ZB ON ZB.FShippName = V1.DepartmentName

Answer 3

我已经把SQL合并了,虽然效率有提升,但是不明显啊    
    DoEvents
    '初始人员信息查询，判断船员初始ID，船岸标志，部门，币别
    sql = "SELECT t_PA_Personal.FHrID AS Expr3," & _
          "HM_EmployeesAddInfo.HRMS_UserField_3 AS Expr1, " & _
          "v_Report_WorkInfo.DepartmentName AS Expr2, tbl_ZB.FBB " & _
          "FROM v_Report_WorkInfo INNER JOIN " & _
          "HM_EmployeesAddInfo ON " & _
          "v_Report_WorkInfo.EM_ID = HM_EmployeesAddInfo.EM_ID INNER JOIN " & _
          "t_PA_Personal ON HM_EmployeesAddInfo.EM_ID = t_PA_Personal.FHrID INNER JOIN " & _
          "tbl_ZB ON v_Report_WorkInfo.DepartmentName = tbl_ZB.FShippName "
         ' Debug.Print sql
         'Set mRs = Nothing
   Set mRs = mMyCn.Execute(sql)
   
        While Not mRs.EOF And Not mRs.BOF
                    mFempID = mRs(0) 'ID
            mSgins = mRs(1)  '船岸标志
            mDep = mRs(2)    '部门
            mBB = mRs(3)     '币别
            
        '根据职务，确定缴纳标准
        sql = "SELECT tbl_Sta.FJNstardand " & _
              "FROM tbl_Sta INNER JOIN " & _
              "tbl_EmpSarllary ON tbl_Sta.FInterID = tbl_EmpSarllary.FInterID " & _
              "WHERE tbl_EmpSarllary.FEmpID = '" & mFempID & "' "
        'Debug.Print sql
        Set mRs4 = mMyCn.Execute(sql)
        If Not mRs4.EOF And Not mRs4.BOF Then
        
            FJNstardand0 = mRs4(0)
        Else
            FJNstardand0 = 0
        End If      '当月应发
        sql = "SELECT f_yingfa From tbl_EmpSarllary " & _
              "WHERE fyear = '" & mYear & "' and fmonth = '" & mMonth & "' " & _
              "AND FEmpID = '" & mFempID & "' "
        Set mRs6 = mMyCn.Execute(sql)
        If Not mRs6.EOF And Not mRs6.BOF Then
        
            yingfa11 = mRs6(0)
        Else
            yingfa11 = 0
        End If
  
           If mSgins = "船上" Then
               
               If mBB = "美元" Then                   sql = "SELECT tbl_ChangeBill.FEmpID, tbl_ChangeBill.FDate, tbl_ZB.FBB, " & _
                         "tbl_ChangeBill.FNDepID , tbl_ChangeBill.FNSigns " & _
                         "FROM tbl_ChangeBill INNER JOIN " & _
                         "tbl_ZB ON tbl_ChangeBill.FNDepID = tbl_ZB.FShippName " & _
                         " where (FEmpID = '" & mFempID & "') and (fdate between '" & mGWSDate & "' and '" & mGWEDate & "') "'                    Debug.Print sql
                   Set mRs3 = mMyCn.Execute(sql)
                   If Not mRs3.EOF And Not mRs3.BOF Then
                       If mRs3(4) = "船上" Then
                           If mRs3(2) = "美元" Then                               sql = "update tbl_EmpSarllary set FYFSalary = '" & FJNstardand0 & "' " & _
                                     "where fempid = '" & mFempID & "' "
                               mMyCn.Execute sql                           ElseIf mRs3(2) = "人民币" Then                                day = DateDiff("d", CDate(Format(mGWSDate, "YYYY-MM-DD")), CDate(Format(mRs3(1), "YYYY-MM-DD"))) - 1
                                '当月应发工资
                                sql = "SELECT tbl_EmpSarllary.F_Yingfa,tbl_Sta.FJNstardand " & _
                                      "FROM tbl_Sta INNER JOIN " & _
                                      "tbl_EmpSarllary ON tbl_Sta.FInterID = tbl_EmpSarllary.FInterID " & _
                                      "WHERE tbl_empsarllary.fempid = '" & mFempID & "' " & _
                                      "and fyear = '" & mYear & "' and fmonth = '" & mMonth & "' "
                                Set mRs4 = mMyCn.Execute(sql)
                                yingfa1 = mRs4(0)
                                biaozhun1 = mRs4(1)
                                   
                                If Not mRs4.EOF And Not mRs4.BOF Then
                                    sql = "update tbl_EmpSarllary set FYFSalary = '" & yingfa1 & "' + '" & biaozhun1 & "' * '" & day & "'/ 30 " & _
                                          "where fempid = '" & mFempID & "' "
                                    mMyCn.Execute sql
                                Else
                                   sql = "update tbl_EmpSarllary set FYFSalary = 0 " & _
                                         "where fempid = '" & mFempID & "' "
                                End If
                                
                           End If
                       ElseIf mRs3(4) = "岸上" Then
                            day1 = DateDiff("d", CDate(Format(mGWSDate, "YYYY-MM-DD")), CDate(Format(mRs3(1), "YYYY-MM-DD"))) - 1                             sql = "SELECT tbl_EmpSarllary.F_Yingfa,tbl_Sta.FJNstardand " & _
                                   "FROM tbl_Sta INNER JOIN " & _
                                   "tbl_EmpSarllary ON tbl_Sta.FInterID = tbl_EmpSarllary.FInterID " & _
                                   "WHERE tbl_empsarllary.fempid = '" & mFempID & "' " & _
                                   "and fyear = '" & mYear & "' and fmonth = '" & mMonth & "' "
                              
                             Set mRs4 = mMyCn.Execute(sql)
                             yingfa2 = mRs4(0)
                             biaozhun2 = mRs4(1)
                              
                             If Not mRs4.EOF And Not mRs4.BOF Then
                                 sql = "update tbl_EmpSarllary set FYFSalary = '" & yingfa2 & "' + '" & biaozhun2 & "' * '" & day1 & "'/ 30 " & _
                                       "where fempid = '" & mFempID & "' "
                                 mMyCn.Execute sql
                             Else
                                  sql = "update tbl_EmpSarllary set FYFSalary = 0 " & _
                                        "where fempid = '" & mFempID & "' "
                             End If
                       End If
                   Else
                           sql = "update tbl_EmpSarllary set FYFSalary = '" & FJNstardand0 & "' " & _
                                 "where FEmpID = '" & mFempID & "' "
                           mMyCn.Execute sql
                   End If
                    
               ElseIf mBB = "人民币" Then                         If Not mRs3.EOF And Not mRs3.BOF Then
                           If mRs3(4) = "船上" Then
                               If mRs3(2) = "美元" Then
                               
                                       sql = "update tbl_EmpSarllary set FYFSalary = '" & FJNstardand0 & "' " & _
                                             "where fempid = '" & mFempID & "' "
                                       mMyCn.Execute sql                               ElseIf mRs3(2) = "人民币" Then                                    day9 = DateDiff("d", CDate(Format(mGWSDate, "YYYY-MM-DD")), CDate(Format(mRs3(1), "YYYY-MM-DD"))) - 1                    
                                    
                               End If
                           ElseIf mRs3(4) = "岸上" Then
                                  
               End If
               
           ElseIf mSgins = "岸上" Then
                   If Not mRs3.EOF And Not mRs3.BOF Then
                       If mRs3(4) = "船上" Then
                           If mRs3(2) = "美元" Then
                               
                           ElseIf mRs3(2) = "人民币" Then                       ElseIf mRs3(4) = "岸上" Then                  
                   Else            
            End If
            mRs.MoveNext
        Wend
'    End If    PicWait.Visible = False
End Sub

Answer 4

如果你用的是Access或Sql server可以用"Update tablename set col1=a.col1+b.col1 from tablename1 as a,tablename2 as b where 条件 "这样的语句去替换你的循环遍历更新如果是Oracle的话语句应该调整为："Update tablename,tablename1 as a,tablename2 as b set col1=a.col1+b.col1 where 条件"的这种形式

Answer 5

代码太混乱。
将查询所需的表继续用 INNERT JOIN 或 LEFT OUTER JOIN 拼到总查询中，用 ISNULL() 之类的 SQL 函数将空值作为 0 处理。
你自己理一下表的关系，估计用 UPDATE 语句无循环可以实现的，必要时创建一些视图，可以降低 SQL 的复杂度。

Answer 6

我已经把前面四个SQL合并成一个了,但是WHILE循环结果集还是很慢,应当怎么处理呢 ?

Answer 7

只看到你的代码循环中只是按字段关系进行 SELECT 或 UPDATE，那么你应该可以不用循环只用 1 个或多个 UPDATE 进行批量更新就可以了。
因为你的更新目标只有一个，也没有复杂的运行关系，完全可以用 SQL 语句解决的，最多按不同的条件多建几个不同的 VIEW 好了。
update tbl_EmpSarllary
  set FYFSalary = VIEW1.Value
    from tbl_EmpSarllary, VIEW1
      where tbl_EmpSarllary.fempid = VIEW1.fempid