Android Question SQL Update Queries faster

G

GeoffT660

Active Member
Licensed User
Longtime 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
 
Sort by date Sort by votes

klaus

Expert
Licensed User
Longtime 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
G

GeoffT660

Active Member
Licensed User
Longtime 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

XbNnX_507

Active Member
Licensed User
Longtime 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
Longtime User
DonManfred said:
Why not combining both the query and the update with ONE SQL-Update. No need for a loop.
Or did is miss something?
Click to expand...
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
G

GeoffT660

Active Member
Licensed User
Longtime User
DonManfred said:
Why not combining both the query and the update with ONE SQL-Update. No need for a loop.
Or did is miss something?
Click to expand...
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
G

GeoffT660

Active Member
Licensed User
Longtime User
Erel said:
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
Click to expand...
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

B4X founder
Staff member
Licensed User
Longtime User
GeoffT660 said:
not sure if this applies to Updates also?
Click to expand...
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
M

Mahares

Expert
Licensed User
Longtime 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
G

GeoffT660

Active Member
Licensed User
Longtime 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
M

Mahares

Expert
Licensed User
Longtime User
GeoffT660 said:
simple try/catch construct was about 25% faster then post #11 with the Batch and Wait For, just to let you know my findings
Click to expand...
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
You must log in or register to reply here.

Similar Threads

S
  • Locked
Java Question DBRequestManager update multiple records
Replies
2
Views
964
Erel
S
  • Question
Android Question DBUtils.InsertMaps
Replies
1
Views
1K
Erel
  • Question
Android Question How to update sqlite database file without getting error ?
Replies
11
Views
3K
Guenter Becker
  • Question
Android Question Problem saving data quickly in SqLite
Replies
5
Views
2K
Erel
K
  • Question
Android Question CustomListView running too slow
Replies
12
Views
3K
Erel
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…