Android Question Insert perform jdbc + sqlite

[benji]

Hi all, i have this code with some trouble, i have 85000 records and has a time of 10 minutes aprox. Sometimes take 45 seconds(only a few times), i dont know why, same conditions, i dont understand...
any one has an idea? need i change the code?

Sub btnLote_Click

    ProgressDialogShow("Cargando Lotes...")
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
    Try
        Dim sf2 As Object = mysql.ExecQueryAsync("mysql", "Select * from lote;",Null)
        Wait For (sf2) mysql_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
        If Success Then
            Main.DBLocalWMS.BeginTransaction
            Try
                Main.DBLocalWMS.ExecNonQuery("Delete from lote")
                Do While Crsr.NextRow
                    i = i + 1
                    AuxCodigo = Crsr.GetInt("codigo")
                    AuxLote = Crsr.GetString("lote")
                    AuxUMB = Crsr.GetString("umb")
                    AuxVenc = Crsr.GetString("vencimiento")
                    Main.DBLocalWMS.AddNonQueryToBatch("Insert into Lote values(?,?,?,?)",Array As Object(AuxCodigo,AuxLote,AuxUMB,AuxVenc))
                Loop
                Main.DBLocalWMS.ExecNonQueryBatch("mysql")
                Main.DBLocalWMS.TransactionSuccessful
                Catch
                    Log(LastException.Message)
                End Try
                Main.DBLocalWMS.EndTransaction
                Crsr.Close
            End If
        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
        ProgressDialogHide   
    End If

    

End Sub

 

[Mahares]

any one has an idea? need i change the code?

I am going to tell you two things about SQLite, but before you try them if you are going to try them is make sure you make a copy of the local database you are going to delete its records:
1. SQL.ExecNonQueryBatch uses a begin and end a transaction internally. So, you do not need to add the Main.DBLocalWMS.BeginTransaction, transaction successful and EndTransaction.
2. Since you are deleting 85000 records from the local SQLite db, it is most of the time a lot faster to DROP TABLE and CREATE it and its indexes especially if not complicated, rather than going through the DELETE FROM which goes through all records one by one.
3. You may want to log start and end times to each step to see which step bogs you down and slows the process. You may want to try this first.
Again, be careful when you use DROP TABLE

 

[OliverA]

4. Read this post https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content to see how to properly use Wait For with your batch code

5. As to the time difference, make sure you are not comparing release mode vs debug mode. Timing debug mode is pointless.

 

[benji]

I am going to tell you two things about SQLite, but before you try them if you are going to try them is make sure you make a copy of the local database you are going to delete its records:
1. SQL.ExecNonQueryBatch uses a begin and end a transaction internally. So, you do not need to add the Main.DBLocalWMS.BeginTransaction, transaction successful and EndTransaction.
2. Since you are deleting 85000 records from the local SQLite db, it is most of the time a lot faster to DROP TABLE and CREATE it and its indexes especially if not complicated, rather than going through the DELETE FROM which goes through all records one by one.
3. You may want to log start and end times to each step to see which step bogs you down and slows the process. You may want to try this first.
Again, be careful when you use DROP TABLE

thanks, the sqlite is empty at first run, but i will consider DROP TABLE.
thanks again.

 

[benji]

4. Read this post https://www.b4x.com/android/forum/threads/b4x-sql-with-wait-for.79532/#content to see how to properly use Wait For with your batch code

5. As to the time difference, make sure you are not comparing release mode vs debug mode. Timing debug mode is pointless.

thanks, i tried in release mode, and work fine... its very fast, i need to fix some details.

Thanks again.

 

[MiguelL]

Hi,
Just out of noob-curiosity: Would a "Truncate table .." not be the fastest solution here?
regards

 

[Albert Kallal]

thanks, i tried in release mode, and work fine... its very fast, i need to fix some details.

Thanks again.

You note that "some times" it was/is slow. This usually occurs when you edit code, and re-start (we assume debug mode). This can in-place update the code,, but when that occurs, then the code runs as un-optimized. I can't say I have "when" this occurs 100% of the time. However, You can trick/force this in debug mode if you do a tools->clean project. That will make it run fast - even in debug mode. So this explains the sometimes fast, and sometimes slow. However, to always be sure - then you can (should) try running in release mode - since it going to get you full speed ahead.

But if you are debugging, and you have HUGE slow downs, and you still need to debug? then do the clean project. The optimizers show such HUGE speeds ups, that I have some code that is impossible to debug unless I do that tools->clean project before running in debug mode. Sometimes when I forget? I just hit the stop button - since we often talking about 8 minutes worth of time vs say 10 seconds! So just be aware, you edit code, and don't clean, then the resulting code is kind of messy and you not see much if any of the optimizes kick in.

as a result what seems impossible slow is not really slow at all.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Peter Simpson]

On the very rare occasions that I've had to delete all the data in a table but keep the structure in place, I've used truncate. Many years ago whilst learning SQLite I read/learned that deleting all the data from a table (especially if it's a lot of data) is executed quicker and more efficiently with a truncate.

This line will truncate your table_name

     DELETE FROM table_name

SQLite only uses truncate optimiser if the table has no triggers (which I doubt your table uses), thus will be quicker than dropping and recreating the table. But saying that, dropping and recreating the table will also be lighting fast too...

Enjoy...

 

[MiguelL]

Ah Okay, there is no "TRUNCATE TABLE" command in SQLite..
Thanks.