How to remove thousands of records from sqlite android

Question

I have over 5000-6000 records in a SQLite table. When I delete all the records, it takes a very long time and pauses the screen and starts releasing resources.

I tried it using AsyncTask, but still the same question. So can someone tell me how I should delete thousands of records without blocking the application.

Answer 1

I am not an expert for Sqlite, but overall there are 3 ways to do this.

• As each authority commented on Truncate, if you intend to delete the entire entry.
• If you switch to most of the records, you can store the files without deleting in tempTable , and then trim your actual table, at the end insert all records from temp to the actual table

• This is what I use most of the time. Use the Top XXX delete operator in your case, you can delete 200 entries in every 2 minutes. (I assume that you do not enter more than 200 entries in 2 minutes). AsyncTask is the way to go. In T-SQL, I use the following sql, it is up to you.

  Remove from tUser where UserId in (Select the top 200 UserId From tUser where LastLoggin <GetDate () - 120)

Answer 2

If you want to delete all records in the table, you can try to abandon the table:

http://www.sqlite.org/lang_droptable.html

and then create an empty table again:

http://www.sqlite.org/lang_createtable.html