2016-04-18 12:47 GMT+02:00 Olivier Mascia <om at integral.be>: > PRAGMA compile_options; (has SQLITE_SECURE_DELETE been set for compiling > the library?) >
?sqlite3 checkUUID.sqlite SQLite version 3.8.7.1 2014-10-29 13:59:56 Enter ".help" for usage hints. sqlite> PRAGMA compile_options; ENABLE_COLUMN_METADATA ENABLE_FTS3 ENABLE_RTREE ENABLE_UNLOCK_NOTIFY ENABLE_UPDATE_DELETE_LIMIT MAX_SCHEMA_RETRY=25 OMIT_LOOKASIDE SECURE_DELETE SOUNDEX SYSTEM_MALLOC TEMP_STORE=1 THREADSAFE=1 ? > P > ?? > RAGMA auto_vacuum; (is it set to 0 or else?) > ?sqlite> PRAGMA auto_vacuum; 0 ? ?Some other information. I did some testing on an old Aspire One. This is with a table with 1E7 records. This gives a filesize of 512 MB. sqlite3 checkUUID.sqlite <<<"DROP TABLE testUniqueUUID;" real 24m20.05s user 0m13.14s sys 0m59.42s I thougth what if I delete the records before I drop the table? sqlite3 checkUUID.sqlite <<<"DELETE FROM testUniqueUUID; DROP TABLE testUniqueUUID;" real 13m59.44s user 0m11.52s sys 0m48.68s This takes 40% less time and 15% less resources. That is quit a difference. Then I thought: I am probably doing this wrong, the DELETE should be in a transaction. So I did: sqlite3 checkUUID.sqlite <<<"BEGIN TRANSACTION; DELETE FROM testUniqueUUID; COMMIT TRANSACTION; DROP TABLE testUniqueUUID;" real 17m21.39s user 0m11.26s sys 0m52.26s But that is about the same performance. ?Is it not strange that first DELETE and then DROP is so more efficient as just a DROP? I find it counter intuitive. But I am certainly not an expert in databases. -- Cecil Westerhof
