Fastest way to INSERT Sqlite?

[cenglish]

I seem to be missing something and can't figure it out. I'm seeing that INSERT should be pretty fast but it's not the case for me. I know I've gotta be overlooking something simple. I have the following code that uses data from a web service that pulls around 30,000 rows of items with only 3 colums of data. I'm then trying to insert that data into an empty table, then update another table from that table.

Code: Select all

on updateQtyOh qtyToUpdate
   if qtyToUpdate = 1 then
      put executeSQL("BEGIN IMMEDIATE TRANSACTION") into resultSet
      put "Updating All Quantities" into field "updateStatus"
      show field "updateStatus"
   end if

   put revXMLNodeContents(xmlQtyTree_ONCE, "string/NewDataSet/Table[" & qtyToUpdate & "]/I") into qty_itemno
   put revXMLNodeContents(xmlQtyTree_ONCE, "string/NewDataSet/Table[" & qtyToUpdate & "]/Q") into qty_qty
   put revXMLNodeContents(xmlQtyTree_ONCE, "string/NewDataSet/Table[" & qtyToUpdate & "]/C") into qty_cprc
   
   put "INSERT INTO Onhand_Cache VALUES (" & qty_itemno & "," & qty_qty & "," & qty_cprc & ")" into qtySQL
   
   put executeSQL(qtySQL) into qtyResult
   put (1+qtyToUpdate) into qtyToUpdate

   if qtyToUpdate <= totalQtys then
      send "updateQtyOh qtyToUpdate" to me in 0 milliseconds
   else
      put executeSQL("COMMIT TRANSACTION") into tResult

      put "UPDATE Items SET QTYOH = (SELECT OnHand_Cache.QTYOH FROM OnHand_Cache WHERE OnHand_Cache.ITEMNO = Items.ITEMNO), CPRC = (SELECT OnHand_Cache.CPRC FROM OnHand_Cache WHERE OnHand_Cache.ITEMNO = Items.ITEMNO)" & \
            "WHERE EXISTS (SELECT * FROM OnHand_Cache WHERE OnHand_Cache.ITEMNO = Items.ITEMNO)" into qtySQL
      put executeSQL(qtySQL) into qtyResult
      put "Update Complete" into field "updateStatus"
      
      revDeleteXMLTree xmlQtyTree_ONCE
   end if
end updateQtyOh

The thing is that I'm trying to run this in the background and still allow the user to browse through the app, so I obviously want it to go as fast as possible. This process will only be run once. Right now it's taking 10+ minutes for it to complete and drags down the iOS version of the app while it's running. The "executeSQL()" is just a function that gets the dbid and executes the statement. From what I keep seeing, 30,000 items should go extremely fast with using begin transaction and commit transaction, but this isn't working for me. Any feedback would be greatly appreciated.

[shaosean]

The XML might be the bottleneck..

[sturgis]

Are you sure that its the inserts that are slow, and not the update at the end?

Just set up a quick test, doing 30k inserts and it takes 420 milliseconds. Assuming your 30k inserts are actually behaving as a transaction block rather than separate transactions, then as shaosean mentioned, it is likely the bottleneck is elsewhere. You might do some timings. Comment out the sql stuff and just run through the xml and see how long things take.

Also, there is some overhead with send in time, for my test I used a repeat loop to do the inserts. If they're done in 420 millisec, thats not too awful long to be blocking. Can always put in a wait 0 with messages if you need breathing space for other events to occur. (though that will put you back closer to the overhead of a send in time)

Heres the code for my 30k inserts, maybe something in it will help.

Code: Select all

put "INSERT INTO  test VALUES ('[[random(1000)]]','[[random(1000)]]'" into tMrg
   if sConnectId is empty then put revOpenDatabase("sqlite","testdb.sqlite",,,) into sConnectId --ugly, but it works. 

-- also noticed that it shaves off about 40ms using "BEGIN TRANSACTION" rather than the immediate form. 
   revexecutesql sConnectId,"BEGIN IMMEDIATE TRANSACTION"  -- not using a separate function for this, just using the native stuff. 

   put the milliseconds into tStart -- track time
   repeat 30000 times -- this could be in the i = 1 to myNumberOfRows instead and be just as fast
      revexecutesql sConnectId, merge(tMrg) -- merge in random data, just numbers to make it easy, and send the insert
   end repeat
   revexecutesql sConnectId, "COMMIT"
   put the milliseconds - tStart -- show the time in milliseconds

[FireWorx]

Thanks that's great info. I think I may be able to speed up some of my repeat loop inserts as well with this BEGIN and COMMIT method. I also want to explore the ROLL BACK feature. I believe that this may be a benefit if an admin level user alters the database accidentally in an unwanted way? Any code to get me started would be appreciated.
Thanks!