lundi 20 juillet 2015

Inputting data in SQLite database from txt takes unusual long time

I have some hundred text files of ; separated values, each file being 200-300 mb large. I suspect that the problem is that the values that is inputted in two columns, with each column having an index, have six digit decimals.

This is the SQLite table, created in python:

    cur.execute("CREATE TABLE MessageType1(port int, userid int, longitude INT, latitude INT, cog INT, heading INT, unixtime int)")
    cur.execute("CREATE INDEX latitude_index ON MessageType1 (latitude)")
    cur.execute("CREATE INDEX longitude_index ON MessageType1 (longitude)")

The values is inputted by using this script:

for f in *.txt
do
    sqlite3 -separator ';' database/database.db ".import $f MessageType1"
    echo ${f%.*}.database
done

Typical input is:

32;25743255;70.384123;59.693545;261.60;242.00;134433400

After some testing, I've found that inputting one file into the database could take upwards of three hours. Should I try to decrease the number of decimals?

Aucun commentaire:

Enregistrer un commentaire