weixin_40292092 2017-10-19 00:15 采纳率: 100%
浏览 2199
已采纳

VB 写入sqlite 数据库太慢

VB 写入sqlite 数据库太慢,写入50个数据需要4秒,100个数据需要8秒,请哪位大侠帮我看一下程序哪里不对(程序能够正常运行,用excel vba 写入access不到1秒就完成了),非常感谢!
Private Sub CommandButton1_Click()
timer11 = Now()
Dim m As Integer, t As Integer
Dim SQL As String, SQL1 As String, SQL2 As String
Dim sr$, i%, xb%, mmmm1$, mmmm2$, mmmm3$, mmmm4$, rightcount%, righttotal%, kkkkk%, testwrong%, wrongcount%, arr0, arr, arr2, arr4, d0, d1, d2, d3, d4, d5, testright%, d8
sr = ""
Dim sCnn As cConnection, sRs As cRecordset
Set sCnn = New_c.Connection
sCnn.OpenDB App.Path & "\Database\database.db"
Set sRs = sCnn.OpenRecordset("Select * from cltestlist Where 考试序号<>'" & sr & "'")
arr = sRs.GetRows(sRs.RecordCount)
Set sRs = sCnn.OpenRecordset("Select * from cldefine")
arr0 = sRs.GetRows(sRs.RecordCount)
Set d0 = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Set d3 = CreateObject("scripting.dictionary")
Set d4 = CreateObject("scripting.dictionary")
Set d5 = CreateObject("scripting.dictionary")
For i = 0 To UBound(arr, 2)
d0(Val(arr(9, i))) = Val(arr(5, i))
d1(Val(arr(9, i))) = arr(6, i)
d2(Val(arr(9, i))) = (arr(7, i))
d3(Val(arr(9, i))) = (arr(17, i))
d4(Val(arr(9, i))) = (arr(11, i))
d5(Val(arr(9, i))) = (arr(10, i))
Next
If IsNull(arr0(2, 14)) = True Or arr0(2, 14) = "" Then

    For i = 1 To Val(arr0(2, 7))
        xb = Val(d0(i))
        mmmm = Trim(TextBox1(i))
          If SQL = "" Then
                SQL = "update cltestlist set 当前答题='" & mmmm & "' where 累计序号='" & xb & "'"
          Else
               SQL = SQL & ";" & "update cltestlist set 当前答题='" & mmmm & "' where 累计序号='" & xb & "'"
          End If
    Next

Else
For i = 1 To Val(arr0(2, 7))
xb = Val(d0(i + (arr0(2, 6) - 1) * 40))
mmmm = Trim(TextBox1(i))
If SQL = "" Then
SQL = "update cltestlist set 当前答题='" & mmmm & "' where 累计序号='" & xb & "'"
Else
SQL = SQL & ";" & "update cltestlist set 当前答题='" & mmmm & "' where 累计序号='" & xb & "'"
End If
Next
End If
SQL1 = "update clTemp2 set 错误题号='" & sr & "'"
sCnn.Execute (SQL)
sCnn.Execute (SQL1)
Set sRs = sCnn.OpenRecordset("Select * from cltestlist Where 考试序号>0") ''''刷计算
arr = sRs.GetRows(sRs.RecordCount)
SQL = ""
SQL1 = ""
For i = 0 To UBound(arr, 2)

         If arr(17, i) = arr(11, i) And arr(10, i) <> "" Then
                xb = (arr(5, i))
                mmmm1 = rightcount
                mmmm2 = "正确"
                mmmm3 = Format(wrongcount / (rightcount + wrongcount), "0.00%")
                mmmm4 = ""
                If SQL = "" Then
                    SQL = "update cltestlist set 选中='" & mmmm4 & "', 错误率= '" & mmmm3 & "',最后一次对错='" & mmmm2 & "',正确次数='" & mmmm & "' where 累计序号='" & xb & "'"
                Else
                    SQL = SQL & ";" & "update cltestlist set 选中='" & mmmm4 & "', 错误率= '" & mmmm3 & "',最后一次对错='" & mmmm2 & "',正确次数='" & mmmm & "' where 累计序号='" & xb & "'"
                End If
         ElseIf arr(10, i) <> "" Then
                xb = Val(arr(5, i))
                mmmm4 = wrongcount
                mmmm3 = "错误"
                mmmm2 = Format(wrongcount / (rightcount + wrongcount), "0.00%")
                If SQL = "" Then
                      SQL = "update cltestlist set 错误次数='" & mmmm4 & "', 最后一次对错= '" & mmmm3 & "',错误率='" & mmmm2 & "'where 累计序号='" & xb & "'"
                Else
                      SQL = SQL & ";" & "update cltestlist set 错误次数='" & mmmm4 & "', 最后一次对错= '" & mmmm3 & "',错误率='" & mmmm2 & "'where 累计序号='" & xb & "'"
                End If
        End If

     Next

    For i = 1 To UBound(arr4, 1)
            xb = Val(arr4(i, 1))
            mmmm = arr4(i, 2)
            If SQL1 = "" Then
                    SQL1 = "update clTemp2 set 错误题号=" & mmmm & " where ID=" & xb & ""
            Else
                    SQL1 = SQL1 & ";" & "update clTemp2 set 错误题号=" & mmmm & " where ID=" & xb & ""
            End If
    Next

    sCnn.Execute (SQL)
    sCnn.Execute (SQL1)
    Set sCnn = Nothing

End Sub

  • 写回答

4条回答 默认 最新

  • iUnknown@ 2018-02-21 04:16
    关注

    sqlite本身就有点慢,看看其他的数据库能否满足您的要求

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

问题事件

  • 已采纳回答 7月31日

悬赏问题

  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 spring后端vue前端
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题