Deleting items from database is very slow...

[MortenMacFly]

I don't know if this question belongs here, so please be patient with me.
I am having a rather simple use-case for wxsqlite3: A DB with 3 tables, where 2 tables are basically parameter logs that grow with time. There is a clean-up function that allows the user to remove any old logs that are no longer useful. The database file is around 10-15 MB in size.

For the clean-up I use this routine (mStateDB is a wxSQLite3Database*):

    sql.Format("select timestamp from log_db order by timestamp;");
    wxSQLite3ResultSet rs = mStateDB->ExecuteQuery(sql);

    mStateDB->Begin(); // Defer execution
    while ( rs.NextRow() )
    {
      wxDateTime ts_dt = rs.GetDate(wxT("timestamp"));
      wxString ts_str = rs.GetString(wxT("timestamp"));
      if ( ts_dt.IsValid() && date_time.IsLaterThan(ts_dt) )
      {
        sql.Format("delete from log_db where timestamp = %Q;", (const char*)ts_str.mb_str(wxConvUTF8));
        mStateDB->ExecuteUpdate(sql);
      }
    }
    mStateDB->Commit(); // Now execute commands

It happens that unexpectedly for a database, for just a few thousand deletions this operation is rather slow and takes many seconds, up to half a minute. So I wonder where this slow-down comes from and suspect my code... ;-)

Maybe I didn't understand the API, but ->Begin() and ->End() does first collect and than executes DB commands at once, right? Do you have any hints for me?

[utelle]

I don't know if this question belongs here, so please be patient with me.

Questions, if they are not related directly to the wxSQLite3 wrapper, but to SQLite itself (like about query optimization or performance of certain SQL commands), are usually better asked on the SQLite user forum.

I am having a rather simple use-case for wxsqlite3: A DB with 3 tables, where 2 tables are basically parameter logs that grow with time. There is a clean-up function that allows the user to remove any old logs that are no longer useful. The database file is around 10-15 MB in size.

SQLite can handle databases which have many GBs in size, so 10-15 MB should not impose a problem.

For the clean-up I use this routine (mStateDB is a wxSQLite3Database*):

    sql.Format("select timestamp from log_db order by timestamp;");
    wxSQLite3ResultSet rs = mStateDB->ExecuteQuery(sql);

    mStateDB->Begin(); // Defer execution
    while ( rs.NextRow() )
    {
      wxDateTime ts_dt = rs.GetDate(wxT("timestamp"));
      wxString ts_str = rs.GetString(wxT("timestamp"));
      if ( ts_dt.IsValid() && date_time.IsLaterThan(ts_dt) )
      {
        sql.Format("delete from log_db where timestamp = %Q;", (const char*)ts_str.mb_str(wxConvUTF8));
        mStateDB->ExecuteUpdate(sql);
      }
    }
    mStateDB->Commit(); // Now execute commands

It happens that unexpectedly for a database, for just a few thousand deletions this operation is rather slow and takes many seconds, up to half a minute. So I wonder where this slow-down comes from and suspect my code... ;-)

Theoretically your code could be to blame, but I doubt it. Are you sure that your application causes the slow-down? For example, SQLite will usually create a rollback journal file for a large transaction. If this triggers your antivirus software, this may explain the problem.

Without knowing the database schema it is very difficult to analyze performance issues. There are many reasons that could cause slow-downs like update of index tables, disk I/O, etc.

If the code you present is really the SQL code that gets executed, it looks rather inefficient. For example, you retrieve with the outer select all records with timestamps in ascending order. Then you delete each row individually. Actually, this will delete all rows with a valid timestamp. If possible, you should delete all rows in a single delete command. This would work, if invalid timestamps can be identified in an SQL where condition..

If you must delete records individually for some reason, I would strongly recommend to use a prepared statement, because this would eliminate the overhead of reparsing the delete command over and over again. Prepared statements can be reused with new values for the bound parameters.

Maybe I didn't understand the API, but ->Begin() and ->End() does first collect and than executes DB commands at once, right?

Not exactly. Using a transaction persists all database changes only, when the final commit is executed. That is, you usually have considerably less disk I/O.

A minor detail: the begin command should be executed, before doing the select command - that is, the select is part of the transaction. However, this doesn't explain the slow-downs.

[MortenMacFly]

Thank you for the quick answer. In fact, I was trying hard to implement what I have in mind in a single statement but did not find a (working) way to filter the time-stamps. Maybe I should try harder on that one. For the rest: The DB scheme is super-simple: The log_db is just a small table with:
create table log_db (guid TEXT, timestamp TEXT, result INTEGER, checksum INTEGER, size INTEGER);
I can say that AV software or other interfering stuff is not an issue.

Also, you have spotted an error: IsLaterThan(ts_dt) should actually compare to the requested date! Strange that it was working as expected or did it actually...?! :-)

So I think you are right: Its not a performance issue with sqlite but an implementation issue. You can close this ticket therefore.

[MortenMacFly]

Minor correction and note to self: The line:
if ( ts_dt.IsValid() && date_time.IsLaterThan(ts_dt) )
...is actually correct as date_time is the requested date (as wxDateTime) and not the one from the DB.

[utelle]

In fact, I was trying hard to implement what I have in mind in a single statement but did not find a (working) way to filter the time-stamps. Maybe I should try harder on that one.

In which format do you store the timestamp in the database? Without knowing the format of the timestamp strings I can't assist in formulating a working where-condition. In principle, it shouldn't be too difficult, if the timestamp string is for example in standard ISO format.

Additionally, your code implies that there are invalid timestamps in the database table? Why? Can they be identified by their value or format?

For the rest: The DB scheme is super-simple: The log_db is just a small table with: create table log_db (guid TEXT, timestamp TEXT, result INTEGER, checksum INTEGER, size INTEGER); I can say that AV software or other interfering stuff is not an issue.

Good. Maybe SQLite cache size could matter (although I doubt it). When the number of database changes grows, SQLite will write modified database pages to the rollback journal. And disk I/O can slow down the transaction.

Also, you have spotted an error: IsLaterThan(ts_dt) should actually compare to the requested date! Strange that it was working as expected or did it actually...?! :-)

Sorry, my fault. I didn't look close enough. Your code is correct.

Minor correction and note to self: The line: if ( ts_dt.IsValid() && date_time.IsLaterThan(ts_dt) ) ...is actually correct as date_time is the requested date (as wxDateTime) and not the one from the DB.

Yes, as said I misinterpreted the code.

[MortenMacFly]

Finally, for others that also stumble across this one: I improved the situation a lot by doing:

    mStateDB->Begin(); // Defer execution

    sql.Format("select timestamp from log_db order by timestamp;");
    wxSQLite3ResultSet rs = mStateDB->ExecuteQuery(sql);
    wxSQLite3Statement stmt = mStateDB->PrepareStatement("delete from log_db where timestamp = ?;");
    while ( rs.NextRow() )
    {
      wxDateTime ts_dt = rs.GetDate  (wxT("timestamp"));
      wxCharBuffer ts_c = rs.GetString(wxT("timestamp")).ToUTF8();
      if ( ts_dt.IsValid() && date_time.IsLaterThan(ts_dt) )
      {
        stmt.Bind(1, ts_c.data());
        stmt.ExecuteUpdate();
        stmt.Reset();
      }
    }

    mStateDB->Commit(); // Now execute commands

Its maybe not perfect (still struggling with date and select ... where) but way, way faster.

Thanks again for you help! :-)

[utelle]

Finally, for others that also stumble across this one: I improved the situation a lot by doing:
[...]
Its maybe not perfect (still struggling with date and select ... where) but way, way faster.

Using prepared statements and bind variables is almost always faster than constructing an SQL string with parameter values.

Regarding the where-condition, something like

DELETE FROM log_db WHERE timestamp < :date_time;

should work (where you bind the user-selected timestamp to the parameter :date_time). However, it depends on the actual format of timestamps used and how invalid timestamps are represented in the database.