[Qt-interest] Driver not loaded...

[Scott Aron Bloom]

When Im doing bulk inserts into SQL Lite, I do three things...

1) All the pragmas listed
2) Wrap the inserts into a transaction
3) Where possible drop the indexes.. 

With those three, I can get upwards of 50-60k rows per second inserted

Scott


-----Original Message-----
From: qt-interest-bounces+scott.bloom=onshorecs.com at qt.nokia.com
[mailto:qt-interest-bounces+scott.bloom=onshorecs.com at qt.nokia.com] On
Behalf Of Jason H
Sent: Tuesday, April 05, 2011 9:35 AM
To: Alex Strickland; qt-interest at qt.nokia.com
Subject: Re: [Qt-interest] Driver not loaded...

How is that any different from putting all 2500 into a single
transaction? 
(Speed-wise, not integrity wise)




----- Original Message ----
From: Alex Strickland <sscc at mweb.co.za>
To: qt-interest at qt.nokia.com
Sent: Tue, April 5, 2011 11:59:08 AM
Subject: Re: [Qt-interest] Driver not loaded...

On 05/04/2011 17:19, Eric Clark wrote:

> Yes it is for good reason. I have a little over 25000 rows of data 
>being inserted into the database and it takes a really long time to do
so.

No I mean that sqlite goes to *extreme* lengths to make sure your data
is on disk and it takes a long time.

>> supports it, any or all of these may make sense for you:
>>
>>       "PRAGMA synchronous = off"
>>       "PRAGMA journal_mode = off"
>>       "PRAGMA locking_mode = exclusive"
>
> I am not familiar with any of these or even how to set them. Is there 
>any chance that you could point me in the right direction to look into 
>what these options do and how to set them?

They are documented on sqlite's web site :
    http://www.sqlite.org/pragma.html

This may help you drop something in:

EBLog::logType SavvDictionary::fastDBWriteMode( QSqlDatabase db ) {
     QString query;

     EBLog::log( EBLog::info, tr( "Set fast write mode." ) );
     query = "PRAGMA synchronous";
     if ( queryDBWriteMode( db ) != EBLog::success )
         return EBLog::error;
     query = "PRAGMA synchronous = off";
     if ( pragmaSet( query, db ) != EBLog::success )
         return EBLog::error;
     query = "PRAGMA journal_mode = off";
     if ( pragmaSet( query, db ) != EBLog::success )
         return EBLog::error;
     query = "PRAGMA locking_mode = exclusive";
     if ( pragmaSet( query, db ) != EBLog::success )
         return EBLog::error;
     if ( queryDBWriteMode( db ) != EBLog::success )
         return EBLog::error;
     return EBLog::success;
}

EBLog::logType SavvDictionary::normalDBWriteMode( QSqlDatabase db ) {
     QString query;

     EBLog::log( EBLog::info, tr( "Restore normal write mode." ) );
     if ( queryDBWriteMode( db ) != EBLog::success )
         return EBLog::error;
     query = "PRAGMA synchronous = full";
     if ( pragmaSet( query, db ) != EBLog::success )
         return EBLog::error;
     query = "PRAGMA journal_mode = delete";
     if ( pragmaSet( query, db ) != EBLog::success )
         return EBLog::error;
     query = "PRAGMA locking_mode = normal";
     if ( pragmaSet( query, db ) != EBLog::success )
         return EBLog::error;
     if ( queryDBWriteMode( db ) != EBLog::success )
         return EBLog::error;
     return EBLog::success;
}

EBLog::logType SavvDictionary::queryDBWriteMode( QSqlDatabase db ) {
     QString query;

     query = "PRAGMA synchronous";
     if ( pragmaGet( query, db ) != EBLog::success )
         return EBLog::error;
     query = "PRAGMA journal_mode";
     if ( pragmaGet( query, db ) != EBLog::success )
         return EBLog::error;
     query = "PRAGMA locking_mode";
     if ( pragmaGet( query, db ) != EBLog::success )
         return EBLog::error;
     return EBLog::success;
}

EBLog::logType SavvDictionary::pragmaSet( const QString & query, const
QSqlDatabase & db ) {
     QSqlQuery pragmaQuery( db );

     if ( !pragmaQuery.exec( query ) ) {
         close();
         return EBLog::log( EBLog::error, pragmaQuery.lastError().text()

+ ", " + query );
     }
     EBLog::log( EBLog::info, query, EBLog::noshow );
     return EBLog::success;
}

EBLog::logType SavvDictionary::pragmaGet( const QString & query, const
QSqlDatabase & db ) {
     QSqlQuery pragmaQuery( db );

     if ( !pragmaQuery.exec( query ) ) {
         close();
         return EBLog::log( EBLog::error, pragmaQuery.lastError().text()

+ ", " + query );
     }
     if ( !pragmaQuery.next() ) {
         return EBLog::log( EBLog::error, pragmaQuery.lastError().text()

+ ", " + query );
         close();
     }
     EBLog::log( EBLog::info, query + ", value = " + pragmaQuery.value(
0 ).toString(), EBLog::noshow );
     return EBLog::success;
}

You'll have to hack out the EBLog stuff unless you'd like me to send it.

Regards
--
Alex
_______________________________________________
Qt-interest mailing list
Qt-interest at qt.nokia.com
http://lists.qt.nokia.com/mailman/listinfo/qt-interest



      
_______________________________________________
Qt-interest mailing list
Qt-interest at qt.nokia.com
http://lists.qt.nokia.com/mailman/listinfo/qt-interest