vendredi 12 juin 2015

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.

Aucun commentaire:

Enregistrer un commentaire