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
uj5u.com熱心網友回復：
字太多.看不完.
uj5u.com熱心網友回復：
不知道sqlite是否支持類似SQL Server的bcp命令列工具。uj5u.com熱心網友回復：
不知道，在網上沒有找到。有其他更快的方法嗎？uj5u.com熱心網友回復：
代碼太多.我個人看不完.也沒有你的資料庫檔案.無法幫你測驗但是肯定不至于這么慢的.還是需要檢查代碼或者資料庫及表的設計.
看你用的應該是vbrichclient,我也在用.沒見得這么慢.
SQLite也有ODBC。你也可以試一下ADO-ODBC-SQLite看看.
uj5u.com熱心網友回復：
cltestlist 表的記錄數如果比較大， “累計序號” 欄位建立索引。此外，如果該欄位是數值型的，那么在where條件里面別加引號同樣的
clTemp2 表的ID確認是否有索引
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/52808.html
下一篇：獲取指定的CAD