CODEWITHSUNDEEP
androidsqlite
tellob
tellob

Reputation: 1250

How to read 4 Millions lines from SQLite and write them to a file fast?

In my current android project I am developing an application, that records communication data. Each data packet is stored in a single row inside my SQLite database. One of my test recordings has 3.888.530 lines.

The application shall support an export functionality, to write this data into a text file in a JSON format. Currently I am using the LIMIT function, read packages of 5.000 lines and write them to the file. This is really slow. I think reading 15.000 lines from the database and writing them to the file needs more than 60s.

For writing I am using a PrintStream. A string is created and the conversion to a byte array is given to the stream.

I read my data packets with this query:

SELECT * 
FROM data_packet 
INNER JOIN data_packet_type 
    ON data_packet.data_packet_type_id = data_packet_type.id 
WHERE recording_id = 7 
ORDER BY time_received ASC 
LIMIT 5000 OFFSET 0

The query is executed with calling:

db.rawQuery(query, null);

Afterwards I run through the data with a cursor and store the data.

I hope there is a way to export the data much faster. Thanks.

Upvotes: 2

Views: 632

Answers (1)

Juergen
Juergen

Reputation: 12728

I think, you really should drop the LIMIT function.

By using LIMIT / OFFSET you force the database to read over the same chunks of rows over and over again, specially when you use ORDER BY and/or JOINS.

The other question, I would think about (when I would have the db scheme) is if you really need the JOIN. For me, it looks as it could be avoided, but of course the database could optimize the JOIN away, what I can not say either without having a scheme and maybe an EXPLAIN.

When this does not help, I would recommend to do the reading without the writing, so you can see, if your are database or Write-Bound in this case (as Robert hinted, that PrintStream could be bad for the writing -- when much data is written, it well could be the writing that is slow).

But without db scheme and the information, if the read or the write is the problem, a better answer is really difficult!

Upvotes: 2

Related Questions