Sqlite Memory Use

[drave]

I have a simple app that uses a database as a log and just repeatedly INSERT's.
Originally with Postgresql everything was OK, fine.

Changed to SQLITE by changing the QSqlDatabase::addDatabase
Now the app just chews memory, approx 200K per INSERT. It is currently using 1.5G and increasing

IS this a SQLITE bug or a QT database bug or Normal behaviour or what ??

thanks

[numbat]

For what it's worth, I just ran the following test and it only chews up a couple of megabytes of memory. Could you give more details on your project?

void doSqLiteTest()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("CatchyName");
    db.open();
 
    QString sql = QString("CREATE TABLE T1(F1, F2, F3)");
    QSqlQuery query(db);
    query.exec(sql);
 
    query.exec("BEGIN TRANSACTION;");
 
    for (int i = 0; i < 500000; i++)
    {
        query.prepare("INSERT INTO T1 (F1, F2, F3) "
                       "VALUES (:id, :forename, :surname)");
        query.bindValue(":id", 1001);
        query.bindValue(":forename", "Bart");
        query.bindValue(":surname", "Simpson");
        query.exec();
    }
 
    query.exec("END TRANSACTION;");
}

void doSqLiteTest()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("CatchyName");
    db.open();

    QString sql = QString("CREATE TABLE T1(F1, F2, F3)");
    QSqlQuery query(db);
    query.exec(sql);

    query.exec("BEGIN TRANSACTION;");

    for (int i = 0; i < 500000; i++)
    {
        query.prepare("INSERT INTO T1 (F1, F2, F3) "
                       "VALUES (:id, :forename, :surname)");
        query.bindValue(":id", 1001);
        query.bindValue(":forename", "Bart");
        query.bindValue(":surname", "Simpson");
        query.exec();
    }

    query.exec("END TRANSACTION;");
}

To copy to clipboard, switch view to plain text mode 

[tbscope]

If you want to keep in the spirit of Qt, you can use db.transaction() and db.commit() instead of query.exec("BEGIN TRANSACTION;"); and end transaction

[drave]

Hmm my code looks more like (see below).
Thats artificial by the way, in fact the inserts happen randomely from a few per second to a few per minute
and the basic logic is
Open
INSERT
Close

Could it be the TRANSACTION that frees the memory ??
Do INSERTS have to be wrapped by TRANSACTIONS ?



QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(DBNAME);

for (int i = 0; i < 40000; i++)
{
bOk = db.open();
if ( !bOk )
{ qDebug() << "db.Open() FAILED"; break; }

QSqlQuery query(db);
QString qStr = QString ("INSERT INTO socketlog ([ip],[outlet],[watts],[volts],[amps],[temp],[freq],[powerfactor]) "
"VALUES(%1,0,10,240,2,15,50,1);").arg(19216811 + (i % 100));
bOk = query.exec(qStr);
db.close();
if ( !bOk )
qDebug() << "DB insert_sockdata FAILED :: " << query.lastError();
else
qDebug() << "DB insert_sockdata OK " << i;
}

[numbat]

Do INSERTS have to be wrapped by TRANSACTIONS ?

No, but doing so gives a 1000 fold improvement in speed. See the SQLite FAQ.

I reallly don't think you want to be calling db.open 40000 times. Probably best to do it once for the whole application.

[drave]

thats for speed . any ideas on memory chewing ?

[drave]

bizarre behaviour.
to further test that it wasnt all the opening and closing that was leaking memory i wrapped the query like so

db.open
if ( RUNQUERY == true)
{
QSqlQuery query(db);
QString qStr = QString ("INSERT INTO socketlog ([ip],[outlet],[watts],[volts],[amps],[temp],[freq],[powerfactor]) "
"VALUES(%1,0,10,240,2,15,50,1);").arg(19216811 + (i % 100));
bOk = query.exec(qStr);
}
else bOk = true;
db.close

when RUNQUERY is true this arrangement does not leak memory. duh!! how is that possible?
so is it the query or querystring that is not being destroyed properly or ... confusion rains

[JD2000]

When is the memory leak being measured?

In the first case you have potentially 40000 sets of objects being created in your 'for' loop which may not be deleted until the loop unwinds/exits.
Opening and closing the DB connection does not have any effect on the life of the SQL query objects.

In the second everything is created and destroyed with in the scope of the 'if' statement.

By the way please use [CODE] tags, it makes the source easier to read.

[drave]

i'm measuring by watching the working set in Task Manager

when the loop finish's the working set does not go back down, it remains at the high level and if you run the loop again the memory will begin to climb again

dont those objects go in and out of scope, and get created/destroyed with each loop? at least i thought they did

plus as i said this behaviour is with SQLITE it didnt do that with Postgresql
this is just test code that i wrote after noticing the climbing memory in the real app, that has an OPEN,INSERT,CLOSE logic but not in a loop. i just put it in a loop for test purposes