emptying tables

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

emptying tables

rene
Hi

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 .."

3.       Choose 1. or 2. Depending on the amount of values inside the table

4.       Some other way unknown to me


Thank you for any hints.
Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

rene
> 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.

I started with the single table solution. But it was to slow on select and the database was also bigger because of the id for each row. Now I can use the timestamp as rowed.

Btw. How big is the overhead of calling multiple commands instead of one?
I do sqlite3_prepare() for all commands on the beginning and in the running only sqlite3_reset(), sqlite3_bind*() and sqlite3_step().


Regards
Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

Simon Slavin-3
In reply to this post by rene

On 8 May 2015, at 12:54pm, Zaumseil René <[hidden email]> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

Igor Tandetnik-2
In reply to this post by rene
On 5/8/2015 7:54 AM, Zaumseil René 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?

Close the connection, physically delete the database file, then re-open
(which will create a new file) and re-create the schema.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

Simon Slavin-3
In reply to this post by rene

On 8 May 2015, at 1:27pm, Zaumseil René <[hidden email]> wrote:

> I started with the single table solution. But it was to slow on select and the database was also bigger because of the id for each row. Now I can use the timestamp as rowed.

If it was slow on SELECT I suspect you didn't have an appropriate INDEX.  But my suggestion about this was just a minor part of my answer and if your setup works for you it is not a bad setup.

> Btw. How big is the overhead of calling multiple commands instead of one?
> I do sqlite3_prepare() for all commands on the beginning and in the running only sqlite3_reset(), sqlite3_bind*() and sqlite3_step().

My understanding is that you cannot bind a TABLE name to a parameter, so with your current setup you wouldn't be able to use the same statement with multiple tables.  If you collapse your data into one TABLE then you could just rebind one parameter as you move from one set of data to another.

But I cannot even guess how speed would change between the two models.  They depend too much on the sizes of the tables and how fast various components of your computer are.  The main convenience of having one table instead of many is that it usually means you do less programming.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

rene
In reply to this post by rene
Sorry for the delay.

>> I started with the single table solution. But it was to slow on select and the database was also bigger because of the id for each row. Now I can use the timestamp as rowed.
>
>If it was slow on SELECT I suspect you didn't have an appropriate INDEX.  But my suggestion about this was just a minor part of my answer and if your setup works for you it is not a bad setup.
>

With a single table and appropriate indices I have nearly the double size of the database :(
Currently I use one column with "integer primary key asc" as rowed and another for the real value.

>> Btw. How big is the overhead of calling multiple commands instead of one?
>> I do sqlite3_prepare() for all commands on the beginning and in the running only sqlite3_reset(), sqlite3_bind*() and >sqlite3_step().
>
>My understanding is that you cannot bind a TABLE name to a parameter, so with your current setup you wouldn't be able to >use the same statement with multiple tables.  If you collapse your data into one TABLE then you could just rebind one >parameter as you move from one set of data to another.
>

I have one prepared statement for each table to delete the whole table "delete * from tablename" and one to delete only values after a given time. The last one will use the time as bind parameter.

>But I cannot even guess how speed would change between the two models.  They depend too much on the sizes of the >tables and how fast various components of your computer are.  The main convenience of having one table instead of many >is that it usually means you do less programming.
>

Sure. My main problem is still the time of a delete statement. It takes some minutes to delete all data from all tables. May be it is also a hardware problem. I found recently a thread about disabling indexing on windows directories. I will try this and play with the sqlite settings.

I also tried to recreate the database. It failed sometimes because after "db close" I could not always delete the file. Windows gave a permission denied. And then you also run in problems when the database is read from other processes.
It would be nice to have a command inside sqlite to clear an entire database.

>Simon.

Thank you for your input.
Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

Simon Slavin-3

On 12 May 2015, at 9:33am, Zaumseil René <[hidden email]> wrote:

> My main problem is still the time of a delete statement. It takes some minutes to delete all data from all tables.

How many tables do you have ?  You are using just the command

DELETE FROM myTable

with WHERE clause ?

> May be it is also a hardware problem. I found recently a thread about disabling indexing on windows directories. I will try this and play with the sqlite settings.
>
> I also tried to recreate the database. It failed sometimes because after "db close" I could not always delete the file. Windows gave a permission denied. And then you also run in problems when the database is read from other processes.

This is usually the result of antivirus software.  When you close the database the antivirus program thinks a change has been made to the database file so it immediately starts scanning it.  It is still scanning the file when you try to delete it, so Windows refuses to delete the file because a program is still reading it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

rene
In reply to this post by rene
>> My main problem is still the time of a delete statement. It takes some minutes to delete all data from all tables.
>
>How many tables do you have ?  You are using just the command
>

I have up to 3000 Tables. Most of them have only some entries. But some can have have up to a million.

>DELETE FROM myTable
>
>with WHERE clause ?

I use the simple "delete from tablename"

>
>> May be it is also a hardware problem. I found recently a thread about disabling indexing on windows directories. I will try >this and play with the sqlite settings.
>>
>> I also tried to recreate the database. It failed sometimes because after "db close" I could not always delete the file. >Windows gave a permission denied. And then you also run in problems when the database is read from other processes.
>
>This is usually the result of antivirus software.  When you close the database the antivirus program thinks a change has been >made to the database file so it immediately starts scanning it.  It is still scanning the file when you try to delete it, so >Windows refuses to delete the file because a program is still reading it.
>

yeah, I suspect something like that.

>Simon.


Thank you
Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

rene
In reply to this post by rene
Here some last test data

PRAGMA synchronous = 1;
PRAGMA auto_vacuum = FULL;
PRAGMA journal_mode = WAL;
PRAGMA wal_autocheckpoint = 10000;
PRAGMA cache_size = 10000;

A database ca. 4GB with 2200 tables takes 3 minutes for a "delete * from table" over all tables.


HTH
Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

Richard Hipp-3
On 5/12/15, Zaumseil René <[hidden email]> wrote:

> Here some last test data
>
> PRAGMA synchronous = 1;
> PRAGMA auto_vacuum = FULL;
> PRAGMA journal_mode = WAL;
> PRAGMA wal_autocheckpoint = 10000;
> PRAGMA cache_size = 10000;
>
> A database ca. 4GB with 2200 tables takes 3 minutes for a "delete * from
> table" over all tables.

I think that operation will go MUCH faster if you (1) turn off
auto_vacuum, (2) use journal_mode=DELETE, and (3) put all of the
DELETE operations inside a single transaction.


>
>
> HTH
> Rene
>
> Kernkraftwerk Goesgen-Daeniken AG
> CH-4658 Daeniken, Switzerland
>
> Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche
> oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben
> ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie
> diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten,
> diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender
> umgehend zu benachrichtigen. Besten Dank.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

rene
In reply to this post by rene
>I think that operation will go MUCH faster if you (1) turn off
>auto_vacuum, (2) use journal_mode=DELETE, and (3) put all of the
>DELETE operations inside a single transaction.
>
>D. Richard Hipp

I have used auto_commit as a replacement for vacuum. Because I only add new data and only delete from the end I thought it would be faster (no extra scan to rebuild ).

I use WAL mode to not block the reader processes. Do you suggest I should switch to delete mode before I really delete data and switch to wal back after I'm done?

I have a prepared statement "delete from tablename" for each table. I start a transaction and for each table I do sqlite3_reset() and sqlite3_step(). After this I end the transaction.
Should I place all the delete statements in a single statement?


Thank you
Rene

BTW. Will you be at the eurotcl conference?


Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

Richard Hipp-3
On 5/12/15, Zaumseil René <[hidden email]> wrote:
>>I think that operation will go MUCH faster if you (1) turn off
>>auto_vacuum, (2) use journal_mode=DELETE, and (3) put all of the
>>DELETE operations inside a single transaction.
>>
>>D. Richard Hipp
>
> I have used auto_commit as a replacement for vacuum. Because I only add new
> data and only delete from the end I thought it would be faster (no extra
> scan to rebuild ).

Humor me.  Try your experiment without auto_vacuum.  Leave everything
else the same.  See what performance difference you get.

>
> I use WAL mode to not block the reader processes. Do you suggest I should
> switch to delete mode before I really delete data and switch to wal back
> after I'm done?

Maybe WAL mode will be OK.  Try the experiment.

>
> I have a prepared statement "delete from tablename" for each table. I start
> a transaction and for each table I do sqlite3_reset() and sqlite3_step().
> After this I end the transaction.
> Should I place all the delete statements in a single statement?
>

You should be running sqlite3_step() and sqlite3_reset(), not the
other way around.  But otherwise, what you are doing is fine.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

rene
In reply to this post by rene
>> I have used auto_commit as a replacement for vacuum. Because I only add new
>> data and only delete from the end I thought it would be faster (no extra
>> scan to rebuild ).
>
>Humor me.  Try your experiment without auto_vacuum.  Leave everything
>else the same.  See what performance difference you get.
>
>>
>> I use WAL mode to not block the reader processes. Do you suggest I should
>> switch to delete mode before I really delete data and switch to wal back
>> after I'm done?
>
>Maybe WAL mode will be OK.  Try the experiment.
>

It was no great difference. And without auto_vacuum I can not shrink the file.

>>
>> I have a prepared statement "delete from tablename" for each table. I start
>> a transaction and for each table I do sqlite3_reset() and sqlite3_step().
>> After this I end the transaction.
>> Should I place all the delete statements in a single statement?
>>
>
>You should be running sqlite3_step() and sqlite3_reset(), not the
>other way around.  But otherwise, what you are doing is fine.

It was easier this way and not explicitely recommended otherwise.

I have done some experiments offline using a large db (ca.15 GB).
First select on large tables (ca. 1 million values take ca 1min.)
Following selects are fast (ca. 300ms)
The same goes for delete's.
Vacuum on such a beast takes forever (I canceled after 20 min.)
Could it be the data are too fragmented?

If I delete only the last part of the data the code works ok (1 till 2 minutes).


Thank you
Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

Simon Slavin-3

On 12 May 2015, at 6:25pm, Zaumseil René <[hidden email]> wrote:

> It was no great difference. And without auto_vacuum I can not shrink the file.

You turn off auto_vacuum but do a manual VACUUM once you've deleted all the data from /all/ the tables.  It's faster before VACUUM takes time roughly proportional to the amount of data left in the database.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

Keith Medcalf
In reply to this post by rene

Are you running all your delete statements within a single transaction, or a separate transaction for each?

> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of Zaumseil René
> Sent: Tuesday, 12 May, 2015 08:41
> To: [hidden email]
> Subject: Re: [sqlite] emptying tables
>
> Here some last test data
>
> PRAGMA synchronous = 1;
> PRAGMA auto_vacuum = FULL;
> PRAGMA journal_mode = WAL;
> PRAGMA wal_autocheckpoint = 10000;
> PRAGMA cache_size = 10000;
>
> A database ca. 4GB with 2200 tables takes 3 minutes for a "delete * from
> table" over all tables.
>
>
> HTH
> Rene
>
> Kernkraftwerk Goesgen-Daeniken AG
> CH-4658 Daeniken, Switzerland
>
> Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche
> oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang
> derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt.
> Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie
> hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten
> und den Absender umgehend zu benachrichtigen. Besten Dank.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

rene
In reply to this post by rene
>Are you running all your delete statements within a single transaction, or a separate transaction for each?

A single transaction as written before in this thread.


Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: emptying tables

rene
In reply to this post by rene
>You turn off auto_vacuum but do a manual VACUUM once you've deleted all the data from /all/ the tables.  It's faster >before VACUUM takes time roughly proportional to the amount of data left in the database.
>
>Simon.


Thank you for the hint.
Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users