On 8 May 2015, at 12:54pm, Zaumseil Ren? <RZaumseil at kkg.ch> wrote:
> I have a range of tables with rowid,value inside. Some have only few and some > other can have up to a million values. > Now I will empty all the tables and vacuum the database. Which is the fastest > way? > > > 1. A loop over all tables with "delete * from tablename" (this is what > I'm currently doing) > > 2. A loop over all tables with "drop table tablename" and "create table > .." The actual truth depends on finicky details of your own setup. But see the last part of this page: <https://www.sqlite.org/lang_delete.html> "When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This "truncate" optimization makes the delete run much faster." It may technically still be faster to do DROP/CREATE but it's more correct to do DELETE and with this optimisation it will be very fast. I just wanted to add that if you have multiple tables with the same schema (same columns of the same types) then you may get better results by merging all the tables together, just adding one column with what used to be the table name. This means you would no longer have to do operations on many different tables, you could just use the table name as a WHERE parameter. And you could empty all your tables with one command. Simon.
