[odb-users] Recommendations for Mass-Deleting from SQLite Database table

[Boris Kolpackov]

Hi Eric,

Sum, Eric B <eric.b.sum at lmco.com> writes:

> I am trying to delete a group of objects(previously persisted in odb) 
> from a table in a SQLite database that match a certain condition.
> 
> I can think of two ways to do this:
> 
> 1. Use the query API from ODB to get all objects that match that condition
>    and store them in an ODB result.  Then, call the ODB erase function to
>    delete the persistent object from the database.
> 
> 2. Call the execute function in ODB, and execute a native SQL statement such
>    as "DELETE FROM <Tablename> WHERE <condition expression>" such that the
>    condition expression matches the same query in the first option .
> 
> There will be MANY entries in the table, and there are going to be MANY 
> objects to delete based on the condition, so I am going to be performing 
> a massive delete in the database.
> 
> Which of these options would you recommend for speed/efficiency/performance
> since I am going to be performing such a massive delete?  Any other 
> suggestions for performing such an operation?

The second approach will definitely be faster. In fact, for some time
now I have been planning to add the query_erase() function, which would
be similar to query() except that it will delete the objects instead of
returning them. If you can wait, I should have it ready some time 
tomorrow and can build you a pre-release binary.

Boris