Android Question SQL Update Queries faster

GeoffT660

Active Member
Licensed User
I've been trying a bunch of different ideas including batch updates but the SQL update process still takes about 1.25 seconds per record on a Galaxy Tab E. and 1/2 that on the S9. I also tried adding indexes on relevant fields with no noticeable improvement. Are there any other techniques for increasing the speed of this Update Command
B4X:
Cursor1 = Starter.SQLBN.ExecQuery("SELECT ASSETNO, MCNO, QTYORD, ASQTY FROM wcUdfAsTbl WHERE JOBNO = '" & mJobNo  & "' AND MCNO = '" & mMCNo & "'")' ORDER BY DSPORD")
For i = 0 To Cursor1.RowCount - 1
  Cursor1.Position = i
  mAssetNo = Cursor1.GetString("ASSETNO")
  Query = "UPDATE wcUDFAsTbl Set OBNO = ?, ASTRTYPE = ?, ASSTATUS = ?, QTYOB = ?, ExcCode = ?, ExcQty = ?, ASCOND = ?, Loaded = ?, DriverNo = ?, Driver = ?, Route = ?, UDF2 = ?, OBTX = ?, MDate = ?, MTime=?, OBDate = ?, OBTime = ?, MSTAT = ?, RECSTAT = ?, Posted = ?  WHERE AssetNo = " &  Cursor1.GetString("ASSETNO")
  Starter.sqlBN.BeginTransaction
  Try
    Starter.sqlBN.ExecNonQuery2(Query, Array As String(Starter.gOBNo,"OB","P1", Cursor1.GetString("ASQTY"), mExc, edExcQty.text, mCond, "True", Starter.gDrvrNo,Starter.gDrvr,Starter.gRte, mCurLoc, "OB", DateTime.Time(DateTime.Now), TNow, DateTime.Time(DateTime.Now),TNow, "O", mRS, "P"))
    Starter.sqlBN.TransactionSuccessful
  Catch
    Log(LastException.Message)
  End Try
  Starter.sqlBN.EndTransaction
Next
Cursor1.Close
 

klaus

Expert
Licensed User
Try to move Starter.sqlBN.BeginTransaction and Starter.sqlBN.EndTransaction outsides the For / Next loop.
B4X:
Cursor1 = Starter.SQLBN.ExecQuery("SELECT ASSETNO, MCNO, QTYORD, ASQTY FROM wcUdfAsTbl WHERE JOBNO = '" & mJobNo  & "' AND MCNO = '" & mMCNo & "'")' ORDER BY DSPORD")
Starter.sqlBN.BeginTransaction
Try
For i = 0 To Cursor1.RowCount - 1
 Cursor1.Position = i
 mAssetNo = Cursor1.GetString("ASSETNO")
 Query = "UPDATE wcUDFAsTbl Set OBNO = ?, ASTRTYPE = ?, ASSTATUS = ?, QTYOB = ?, ExcCode = ?, ExcQty = ?, ASCOND = ?, Loaded = ?, DriverNo = ?, Driver = ?, Route = ?, UDF2 = ?, OBTX = ?, MDate = ?, MTime=?, OBDate = ?, OBTime = ?, MSTAT = ?, RECSTAT = ?, Posted = ?  WHERE AssetNo = " &  Cursor1.GetString("ASSETNO")
 Starter.sqlBN.ExecNonQuery2(Query, Array As String(Starter.gOBNo,"OB","P1", Cursor1.GetString("ASQTY"), mExc, edExcQty.text, mCond, "True", Starter.gDrvrNo,Starter.gDrvr,Starter.gRte, mCurLoc, "OB", DateTime.Time(DateTime.Now), TNow, DateTime.Time(DateTime.Now),TNow, "O", mRS, "P"))
Next
Starter.sqlBN.TransactionSuccessful
Catch
  Log(LastException.Message)
End Try
Starter.sqlBN.EndTransaction
Cursor1.Close

B4X SQLite Database Booklet chapter 2.1.21 Insert many rows SQL.BeginTransaction / SQL.EndTransaction
 
Last edited:
Upvote 0

GeoffT660

Active Member
Licensed User
Thanks for your suggestion. I did try that as this was mentioned in several places but didn't see any noticeable improvement. I'm setting a timer for the loop so the results are fairly accurate. Not sure if it makes any difference but I'm updating records not inserting.
 
Upvote 0

DonManfred

Expert
Licensed User
Why not combining both the query and the update with ONE SQL-Update. No need for a loop.
Or did is miss something?
 
Upvote 0

XbNnX_507

Active Member
Licensed User
Why don't you try this
B4X:
           Dim Cursor1 As Cursor
    Cursor1 = Starter.SQLBN.ExecQuery("SELECT ASSETNO, MCNO, QTYORD, ASQTY FROM wcUdfAsTbl WHERE JOBNO = '" & mJobNo  & "' AND MCNO = '" & mMCNo & "'")' ORDER BY DSPORD")
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i
        mAssetNo = Cursor1.GetString("ASSETNO")
        Query = "UPDATE wcUDFAsTbl Set OBNO = ?, ASTRTYPE = ?, ASSTATUS = ?, QTYOB = ?, ExcCode = ?, ExcQty = ?, ASCOND = ?, Loaded = ?, DriverNo = ?, Driver = ?, Route = ?, UDF2 = ?, OBTX = ?, MDate = ?, MTime=?, OBDate = ?, OBTime = ?, MSTAT = ?, RECSTAT = ?, Posted = ?  WHERE AssetNo = " &     mAssetNo
        Starter.SQLBN.AddNonQueryToBatch(Query, Array As String(Starter.gOBNo,"OB","P1", Cursor1.GetString("ASQTY"), mExc, edExcQty.text, mCond, "True", Starter.gDrvrNo,Starter.gDrvr,Starter.gRte, mCurLoc, "OB", DateTime.Time(DateTime.Now), TNow, DateTime.Time(DateTime.Now),TNow, "O", mRS, "P") )
    Next
    Cursor1.Close
    Dim senderFilter As Object = sql.ExecNonQueryBatch("SQL")
    Wait For ( senderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log ( Success )

No need to try/catch which is probably what is slowing down the queries.
 
Upvote 0

mc73

Well-Known Member
Licensed User
Why not combining both the query and the update with ONE SQL-Update. No need for a loop.
Or did is miss something?
I agree with Don. Something like this (not tested for syntax errors):
B4X:
Starter.sqlBN.ExecNonQuery2("UPDATE wcUDFAsTbl Set OBNO = ?, ASTRTYPE = ?, ASSTATUS = ?, ExcCode = ?, ExcQty = ?, ASCOND = ?, Loaded = ?, DriverNo = ?, Driver = ?, Route = ?, UDF2 = ?, OBTX = ?, MDate = ?, MTime=?, OBDate = ?, OBTime = ?, MSTAT = ?, RECSTAT = ?, Posted = ?  WHERE AssetNo in (select assetno from wcUdfAsTbl WHERE JOBNO = '" & mJobNo  & "' AND MCNO = '" & mMCNo & "')",array as object(Starter.gOBNo,"OB","P1", mExc, edExcQty.text, mCond, "True", Starter.gDrvrNo,Starter.gDrvr,Starter.gRte, mCurLoc, "OB", DateTime.Time(DateTime.Now), TNow, DateTime.Time(DateTime.Now),TNow, "O", mRS, "P"))
 
Upvote 0

GeoffT660

Active Member
Licensed User
Why not combining both the query and the update with ONE SQL-Update. No need for a loop.
Or did is miss something?
Thanks Don, not sure why I didn't think of this as I use it in all other databases but forget how powerful sqllite is. This method cut my updating time in half which I think will be acceptable. I just "roughed" it in with ExecNonQuery but like the format noted by MC73 in post 7. Is there any benefit to using ExecNonQuery2? Thank you all for these comments!
 
Upvote 0

GeoffT660

Active Member
Licensed User
Try / Catch will not slow it down.

Please post the updated code after you fixed the transaction issue.

BTW, best way to make many inserts is explained here: [B4X] SQL with Wait For
Thanks Erel, not sure if this applies to Updates also? I have tried this method several times but feel it just delays the processing but still takes the same amount of time to update and when scanning successive barcode with some updating multiple records this did not prove better in my initial tests. Please correct me if I missed something.
 
Upvote 0

Erel

Administrator
Staff member
Licensed User
not sure if this applies to Updates also?
Yes.

The slowest way to add items is like you did in the first post where you create a new transaction for each command.
The asynchronous method will be as fast as a correct implementation of the synchronous version, without causing the UI to freeze.
 
Upvote 0

Mahares

Expert
Licensed User
This is how I would tackle it using: Smart Literals, Parameterized query, Batch and Wait for.
B4X:
Dim MyData() As Object =Array As Object(Starter.gOBNo,"OB","P1", mExc, edExcQty.text, mCond, "True", _
    Starter.gDrvrNo,Starter.gDrvr,Starter.gRte, mCurLoc, "OB", DateTime.Time(DateTime.Now), _
    TNow, DateTime.Time(DateTime.Now),TNow, "O", mRS, "P", mJobNo, mMCNo)
   
    Dim query As String = $"
    UPDATE wcUDFAsTbl SET OBNO = ?, ASTRTYPE = ?, ASSTATUS = ?, ExcCode = ?,
    ExcQty = ?, ASCOND = ?, Loaded = ?, DriverNo = ?, Driver = ?, Route = ?,
    UDF2 = ?, OBTX = ?, MDate = ?, MTime=?, OBDate = ?, OBTime = ?, MSTAT = ?,
    RECSTAT = ?, Posted = ? 
    WHERE ASSETNO IN (SELECT ASSETNO FROM wcUdfAsTbl WHERE JOBNO = ? AND MCNO = ?)
    "$

    Starter.SQLBN.AddNonQueryToBatch(query, MyData)
    Dim senderFilter As Object = Starter.SQLBN.ExecNonQueryBatch("SQL")
    Wait For (senderFilter) SQL_NonQueryComplete (Success As Boolean)
    If Success Then
        Log("Update was successful")
    Else
        Log("Sorry, update did not succeed")
    End If
 
Upvote 0

GeoffT660

Active Member
Licensed User
After running these scenarios many time while testing the speed, Don's post on #4 in a simple try/catch construct was about 25% faster then post #11 with the Batch and Wait For, just to let you know my findings. Thank you all for the input and help on this!
 
Upvote 0

Mahares

Expert
Licensed User
simple try/catch construct was about 25% faster then post #11 with the Batch and Wait For, just to let you know my findings
I would not rush and take that to the bank every time and every case. It depends on many factors:
I conducted the following benchmark tests for a SQLite database UPDATE of col1 for every record. Database table has 10000 records and 2 columns.

WITHOUT BATCH
Test on Galaxy Tab A 7 inch tablet with OS 5.1: 72 sec
Test on Galaxy S6 phone OS 7.0: 82 sec

WITH BATCH
test on Galaxy Tab A 7 inch tablet with OS 5.1: 4.5 sec
Test on Galaxy S6 phone OS 7.0: 1.5 sec

As you can see the batch method is dramatically faster, in fact several times faster in my cases.
Here is a sample of the data in the table:
COLUMN1 COLUMN2
2019-03-19 43A1A688-42DD-B006-1A84-568C5F084AA4
2019-03-21 BD4BBE0C-A8EF-0D77-04CA-6D647232F5A0
2019-04-01 006F5BED-E930-5D96-4A17-D27E971FDA05
2019-04-01 2BCDB484-2CAF-DB95-CE81-9A7A6D1231E0
2019-04-22 E58AE198-42C2-A6FF-F7C6-FF9FAB871128
 
Last edited:
Upvote 0
Top