B4J Question reduce speed of updating the database

[SMOOTSARA]

Hello friends

I need to update 25,000 records from the database.
But after 5,000 records, the speed of this operation drops sharply !!

What is the cause of the slowdown?

Thank you.

    For i=1 To 25000
        Log(i)
        If File.Exists("C:\Users\User\Desktop\"&verb_type&"\",i&"-"&verb_type&".HTML") Then
            master_html = File.ReadString("C:\Users\User\Desktop\"&verb_type&"\",i&"-"&verb_type&".HTML")
  
            sql1.ExecNonQuery2("UPDATE tbl_master SET html_"&verb_type&" = ?,special_json_"&verb_type&" = ?,master_json_"&verb_type&" = ? WHERE id = ?", Array As Object(master_html,get_json_spechal,get_json,i))
    
    Else
        
        TextArea1.Text=TextArea1.Text&CRLF&i
    
        End If
    Next

 

[jmon]

try with transactions:
https://www.b4x.com/b4j/help/jsql.html#sql_begintransaction

 

[keirS]

You should use AddNonQueryToBatch in your loop instead of ExecNonQuery2. Then after your loop has finished call ExecNonQueryBatch.

 

[Erel]

Both answers are correct. You should never make more than a single insert without an explicit transaction.

Async SQL is very simple: [B4X] SQL with Wait For