Android Question SQL Update Queries faster

[GeoffT660]

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

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]

Try to move Starter.sqlBN.BeginTransaction and Starter.sqlBN.EndTransaction outsides the For / Next loop.

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

 

[GeoffT660]

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.

 

[DonManfred]

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

 

[XbNnX_507]

Why don't you try this

           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.

 

[Erel]

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

 

[mc73]

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):

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"))

 

[GeoffT660]

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!

 

[GeoffT660]

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.

 

[Erel]

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.

 

[Mahares]

This is how I would tackle it using: Smart Literals, Parameterized query, Batch and Wait for.

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

 

[GeoffT660]

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!

 

[Mahares]

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