Uploading text data to server from SQLite is faster or text files?

Question

My application receives a lot of data every milliseconds(name,id,value,.. of a signal from a simulator device). and I should upload this data to MySQL server. First I need to save them in a database and upload them to the server via GSM network. The reason that I save data into the mobile device is that the connection is so slow and the app receives a quite large number of data at a time and cannot upload them at once, also it may encounter disconnection problem. In fact, I just need a temporary storage and I don't want to do any data manipulation in the SQLite. Currently I am writing the data to the simple text file(CSV) and then upload every 20 lines to the server using name/value pairs in Android HttpPost, but what about SQLite? Is it faster to insert data to SQLite and then upload it? Which approach is preferable?

Here is the sample data that I receive from the simulator device:

time,id,name,value
143125738713,id1,name1,8.000077
143125738714,id1,name1,8.000004
143125738714,id1,name1,8.000008
143125738715,id2,name2,0.0
143125738716,id2,name2,0.0

Answer 1

For a definitive answer, one would have to measure, and I am sure it will be possible to create a badly implemented CSV writer which is slower than a well written SQLite writer.

However, when it comes to writing sequential data records to a file, there is not much out there which can beat using to a simple file stream in speed. Each additional layer of abstraction upon that will probably slow your program down. Thus I would not expect writing to a text file to be slower than writing to any kind of database, even it is as lightweight as SQLlite, as long as there is not any "performance bug" in the program.

If speed is your main concern, and the network connection is your bottleneck, you may consider to apply some kind of data compression before sending the files over the line, and decompress it on the server before inserting it.

Answer 2

SQLite is awesome but it is "not optimised" for insert speed (but note that you can run it in significantly faster 'async' mode which will not protect your data if something crashes - this may be acceptable for your use-case if you don't mind losing some of those data points)

You'll be better off appending to a filestream.

• Robert Harvey: To be clear, SQLite implicitly wraps each Insert in its own transaction, so if you're willing to write the transactions yourself, you can chunk several Inserts together in a single transaction, get speeds as fast as any other database, and still have transaction protection.