Re: [sqlite] sqlite - Delete large table in sqlite

Do you have secure_delete turned on? Do you have triggers on the table you are trying to delete? Is the table you are deleting from the parent table of any referential constraints? > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf

Re: [sqlite] sqlite - Delete large table in sqlite

DELETE FROM tab WHERE rowid in (SELECT rowid from tab ORDER BY … LIMIT 1000); If SQLite compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT http://www.sqlite.org/compile.html#enable_update_delete_limit DELETE FROM tab WHERE … ORDER BY … LIMIT 1000; From: Simon Slavin

Re: [sqlite] sqlite - Delete large table in sqlite

Do you have auto_vacuum turned on? It may be that the cost isn't actually in deleting the table, it may be that the cost is rearranging the rest of the file to fill the gaps left by deleting the table. In that case you could turn off auto_vacuum, or you could use incremental vacuum to smooth out

Re: [sqlite] schema_version and Vacuum or Backup API

Not what I expected, but this can of work for my dual purpose. I'll have to tune it just a little bit, but this will work both as a check for differences between prior and current, and give me a "one-liner transactionable" SQL command to give a FILE>NEW command. Appreciated. On Fri, Sep 9, 2016

Re: [sqlite] sqlite - Delete large table in sqlite

maybe rename and recreate the database with "insert from" the remaining tables would be faster, and give the benefit of a vacuum also? On 09/09/2016 13:14, Венцислав Русев wrote: On 09/09/2016 12:49 PM, Bhavesh Patel wrote: I have a 4GB SQLite database (currently using SQLCipher for encrypt

Re: [sqlite] schema_version and Vacuum or Backup API

On 2016/09/08 4:59 PM, Stephen Chrzanowski wrote: Is there a way that I can get your sub-query (Reordered to have tables, then indexes, then views) to come out as one row? I can then have the application just do an easy string comparison, AND have a method to include the resource string to crea

Re: [sqlite] sqlite - Delete large table in sqlite

On 9 Sep 2016, at 6:14pm, Венцислав Русев wrote: > I don't know if this will work for you, but can't you delete 1000 rows then > another 1000 ... until the table is empty. If you do do that, each 1000 rows should be deleted inside a transaction. So BEGIN DELETE FROM myTable WHERE ... END Bu

Re: [sqlite] sqlite - Delete large table in sqlite

On 09/09/2016 12:49 PM, Bhavesh Patel wrote: I have a 4GB SQLite database (currently using SQLCipher for encryption). I have created a physical table in the SQLite DB which has approx 20 rows x 183 columns. When I am trying to delete the table. Sqlite is taking up a lot of time to delete

Re: [sqlite] sqlite - Delete large table in sqlite

On 9 Sep 2016, at 1:49pm, Bhavesh Patel wrote: > during the process of delete/drop is running, if I try to connect I randomly > get database lock error. Set a timeout of five minutes when you open your SQLite connections using one of the following:

[sqlite] sqlite - Delete large table in sqlite

I have a 4GB SQLite database (currently using SQLCipher for encryption). I have created a physical table in the SQLite DB which has approx 20 rows x 183 columns. When I am trying to delete the table. Sqlite is taking up a lot of time to delete the huge table. I tried drop table and also del

[sqlite] SQLite - how to get number of active connection?

I want to run certain clean-up tasks on the SQLite database only and only when there is only 1 connection(self) to SQLite. I do not want to lock the database during the cleanup process. The main application(s) using the SQLite DB is given 1st preference. the cleanup application is executed at l

Re: [sqlite] Problem with rename table

Yes my first idea was to rename back to old name but you cant because every sql fails with error malformed database schema. Interest thing is that sql Richard suggested works and no error message returned. It looks like triggers are parsed when other sql executed and produce error. I found another