VB upated sqlite speed too slow, 50 data to 4 seconds, 100 data to 8 seconds, consult can improve grammar? (program can run normally)

Private Sub CommandButton1_Click ()
Timer11=Now ()
Dim m As an Integer, t As Integer
Dim As SQL String, SQL1 As String, SQL2 As String
Dim the sr $, I %, xb %, mmmm1 $, mmmm2 $, mmmm3 $, mmmm4 $, rightcount %, righttotal %, KKKKK %, testwrong %, wrongcount %, arr0, arr, arr2, arr4, d0, d1, d2, d3, d4, d5, testright %, d8
The sr=""
Dim sCnn As cConnection, sRs As cRecordset
The Set of sCnn=New_c. Connection
SCnn) OpenDB App) Path & amp; ", the Database, the Database. The db "
Set the sRs=sCnn. OpenRecordset (" Select * from cltestlist Where test serial number & lt;> '" & amp; The sr & amp; "'")
Arr=sRs. GetRows (sRs. RecordCount)
Set the sRs=sCnn. OpenRecordset (" Select * from cldefine ")
Arr0=sRs. GetRows (sRs. RecordCount)
The Set d0=CreateObject (" scripting. The dictionary ")
The Set d1=CreateObject (" scripting. The dictionary ")
The Set d2=CreateObject (" scripting. The dictionary ")
The Set d3=CreateObject (" scripting. The dictionary ")
The Set d4=CreateObject (" scripting. The dictionary ")
Set the d5=CreateObject (" scripting. The 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 the SQL="" Then
SQL="update cltestlist set current answer='" & amp; MMMM & amp; "' where cumulative number=" & amp; Xb & amp; "'
"The Else
SQL=SQL & amp; ";" & "Update cltestlist set current answer='" & amp; MMMM & amp; "' where cumulative number=" & amp; Xb & amp; "'
"End the If
Next
The Else
For I=1 To Val (arr0 (2, 7))
Xb=Val (d0 (I + (arr0 (2, 6) - 1) * 40))
MMMM=Trim (TextBox1 (I))
If the SQL="" Then
SQL="update cltestlist set current answer='" & amp; MMMM & amp; "' where cumulative number=" & amp; Xb & amp; "'
"The Else
SQL=SQL & amp; ";" & "Update cltestlist set current answer='" & amp; MMMM & amp; "' where cumulative number=" & amp; Xb & amp; "'
"End the If
Next
End the If
SQL1="update clTemp2 set error qid='" & amp; The sr & amp; "'
"SCnn. Execute (SQL)
SCnn. Execute (SQL1)
Set the sRs=sCnn. OpenRecordset (" Select * from cltestlist Where test serial number & gt; 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) & lt;> "" Then
Xb=(arr (5 (I))
Mmmm1=rightcount
Mmmm2="right"
Mmmm3=Format (wrongcount/(rightcount + wrongcount), "0.00%")
Mmmm4=""
If the SQL="" Then
SQL="update cltestlist set selected='" & amp; Mmmm4 & amp; "', the error rate=" & amp; Mmmm3 & amp; "The last time, right and wrong='" & amp; Mmmm2 & amp; "', right=" & amp; MMMM & amp; "' where cumulative number=" & amp; Xb & amp; "'
"The Else
SQL=SQL & amp; ";" & "The update cltestlist set selected='" & amp; Mmmm4 & amp; "', the error rate=" & amp; Mmmm3 & amp; "The last time, right and wrong='" & amp; Mmmm2 & amp; "', right=" & amp; MMMM & amp; "' where cumulative number=" & amp; Xb & amp; "'
"End the If
ElseIf arr (10, I) & lt;> "" Then
Xb=Val (arr (5 (I))
Mmmm4=wrongcount
Mmmm3="error"
Mmmm2=Format (wrongcount/(rightcount + wrongcount), "0.00%")
If the SQL="" Then
SQL="update cltestlist set wrong number='" & amp; Mmmm4 & amp; "The last time, right and wrong='" & amp; Mmmm3 & amp; "', the error rate=" & amp; Mmmm2 & amp; "' where cumulative number=" & amp; Xb & amp; "'
"The Else
SQL=SQL & amp; ";" & "Update cltestlist set wrong number='" & amp; Mmmm4 & amp; "The last time, right and wrong='" & amp; Mmmm3 & amp; "', the error rate=" & amp; Mmmm2 & amp; "' where cumulative number=" & amp; Xb & amp; "'
"End the If
End the 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 error qid=" & amp; MMMM & amp; "Where ID=" & amp; Xb & amp; "
"The Else
SQL1=SQL1 & amp; ";" & "Update clTemp2 set error qid=" & amp; MMMM & amp; "Where ID=" & amp; Xb & amp; "
"End the If
Next

SCnn. Execute (SQL)
SCnn. Execute (SQL1)
The Set of sCnn=Nothing
End Sub

CodePudding user response:

Finish words too much. Can't see.

CodePudding user response:

I do not know if sqlite support similar to SQL Server BCP command line tools,

CodePudding user response:

I don't know, is not found on the Internet, there are other faster way?

CodePudding user response:

Too much code. Personally, I couldn't finish. Didn't your database file. Can't help you test
But must not be so slow. Still need to check the code or the design of the database and table.

See you use should be vbrichclient, I also in use. Not necessarily so slowly.
SQLite also have ODBC, you can also try the ADO - ODBC - SQLite and have a look.

CodePudding user response:

Number of cltestlist table if the larger, "cumulative serial number" field index, in addition, if the field is numeric, then don't quotes in the where condition
The same
ClTemp2 table ID to confirm whether there is an index