VB upated sqlite速度太慢，请纠正哪里不对！

weixin_40292092 2017-10-19 08:47:52

VB upated sqlite速度太慢，50个数据要4秒，100个数据要8秒，请教语法哪里可以改善？（程序能够正常运行）

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

...全文