VB 写入sqlite 数据库太慢 30C

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

3个回答

sqlite就是慢,紧凑的数据库推荐你用sql server localdb

sql server 是必须在客户电脑上安装sql server 软件吧?这样很麻烦。

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐