batch SQL insertion

I have a text file with 50k+- lines and each line contains data which has to be pulled out of each line as a separate field.

The program runs several times a day.

Since this app is portable I am using SQLIite and reading each of those 50k lines one by one gathering required data and inserting into SQlite DB File as it goes.

I did some tests and found out reading through lines alone only takes like 10% of actual time it tak开发者_如何学Pythones now, all the overhead comes when I am inserting all that data one by one in SQLite db.

Looking for suggestions for improvement.

You could increase performance using transactions, so that multiple INSERTs are requested at once, instead of one for each line in your text file. This would allow you to batch INSERT statements (try something like 100 per batch) - this will result in a significant performance boost.

You can increase speed by:

1. Executing INSERTs in transaction. Depending on the amount of data, it may be a good idea to do it in batches of eg. 1000 statements.
2. Use prepared statements, so that the overhead of parsing query is minimized.
3. If you have indexes in target table, sometimes it's good to create a TEMPORARY table with the same structure as target table, populate it with all data, and then, at the end, issue INSERT INTO target_table SELECT * FROM temporary_table.