SQLite attachments get slower even with transactions

Question

I am having issues with sqlite inserts performance even with transactions. My Android app has approximately 23,000 rows from a web service, and I need to paste them into a single table.

The web service is partitioned so that I get about 2,000 rows in each WS request and wrap the 2000 inserts in the transaction. After these insertions are completed, I submit a new request to WS and again use the new transaction for new 2000 lines.

It works great in the beginning. He makes a lot of investments per second. but over time, it becomes slower and finally ends with 4 or 3 inserts per second until it completes 23,000 lines.

Is there a problem with the size of the table? When it gets bigger, do inserts get slower? Is there a way to improve performance for this large amount of data?

Thanks for your help.

Answer 1

There is a great thread covering SQLite performance in the question How to improve SQLite performance? which is not bad. I would at least go to prepared statements if you are not already using them.

However, since you are on Android, I assume that you may encounter some I / O bottlenecks written to flash memory. Try to run the code on several different devices from different manufacturers, also see if there are any extreme options.

Answer 2

data sharing should be used. since you can insert a 100 - 100 insert one by one so that the process is smooth and no data loss will have a problem.

the other is that I assume that you are using an asynchronous (maybe different) thread.

or you can also use the service in the latter case for the simultaneous use of all data.