Bulk insert into SQLite

[natbobc]

I know this comment is old, but is it possible to improve performance using transactions? From what I understand from the sqlite performance comparison document (yes very old, but probably vaguely applicable) under normal circumstances the db file is opened and closed each time an insert is applied unless it is encapsulated in a transaction.

Below is some pseudo code of what I mean;

SQL>> BEGIN TRANSACTION;  // execute as QSqlQuery
 
csv = FileParser::open( "somefile.csv", CSV );
 
while( current_record = csv.nextRecord() ) {
  QSqlQuery q( "INSERT INTO table (firstname,lastname,etc) VALUES (:firstname,:lastname,:etc)" );
 
  q.bindValue( ":firstname", current_record[0] );
  q.bindValue( ":lastname", current_record[1] );
 
...
 
  if( !q.exec() ) throw RecordException;  // or however you want to handle an error
} 
 
close( csv );
 
SQL>> COMMIT

SQL>> BEGIN TRANSACTION;  // execute as QSqlQuery

csv = FileParser::open( "somefile.csv", CSV );

while( current_record = csv.nextRecord() ) {
  QSqlQuery q( "INSERT INTO table (firstname,lastname,etc) VALUES (:firstname,:lastname,:etc)" );

  q.bindValue( ":firstname", current_record[0] );
  q.bindValue( ":lastname", current_record[1] );

...

  if( !q.exec() ) throw RecordException;  // or however you want to handle an error
} 

close( csv );

SQL>> COMMIT

To copy to clipboard, switch view to plain text mode 

I think the alternative and possibly preferred method maybe using QVariantList's with a prepared statement and execBatch() for the insert.

Once you've loaded the data into the db, then grab the table using QSqlTableModel. Note it's just an idea, haven't actually tested it for performance. Hence the mashed code.

Regards,

Nate

[jacek]

I know this comment is old, but is it possible to improve performance using transactions?

If SQLite docs say so, then it might work. This really depends on the DBMS and other databases might like different constructs.

For tips on PostgreSQL read this: http://www.depesz.com/index.php/2007...t-as-possible/

while( current_record = csv.nextRecord() ) {
  QSqlQuery q( "INSERT INTO table (firstname,lastname,etc) VALUES (:firstname,:lastname,:etc)" );
  ...
  q.bindValue( ":firstname", current_record[0] );
  ...

while( current_record = csv.nextRecord() ) {
  QSqlQuery q( "INSERT INTO table (firstname,lastname,etc) VALUES (:firstname,:lastname,:etc)" );
  ...
  q.bindValue( ":firstname", current_record[0] );
  ...

To copy to clipboard, switch view to plain text mode 

If you pass the query string to QSqlQuery constructor, it will execute it immediately --- you have to use prepare() if you have placeholders:

QSqlQuery q;
q.prepare( "INSERT INTO table (firstname,lastname,etc) VALUES  (:firstname,:lastname,:etc)" );
while( current_record = csv.nextRecord() ) {
  ...
  q.bindValue( ":firstname", current_record[0] );
  ...

QSqlQuery q;
q.prepare( "INSERT INTO table (firstname,lastname,etc) VALUES  (:firstname,:lastname,:etc)" );
while( current_record = csv.nextRecord() ) {
  ...
  q.bindValue( ":firstname", current_record[0] );
  ...

To copy to clipboard, switch view to plain text mode 

Also by moving call to prepare() out of the loop, you can save a bit of time (esp. in case of nasty queries).

[sabeesh]

Hi,
Please try this also
http://www.qtcentre.org/forum/f-qt-p...ghlight=SQLite