NB: SELECT COUNT(<field>) FROM <table> requires a traversal of the whole table (or index, if one exists for the field) and returns the number of non-NULL entries, whereas SELECT COUNT() FROM <table> invokes a special opcode to retrieve the total number of rows without actually acessing any of them and so is very much faster.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Joshua Watt Gesendet: Montag, 10. September 2018 16:28 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Database occasionally very slow for trivial query Hello, I have seen a strange behavior when using sqlite 3.20.1, and I was hoping someone could help explain it. I have a database with a very simple schema: $ sqlite3 build/cache/bb_persist_data.sqlite3 SQLite version 3.20.1 2017-08-24 16:21:36 Enter ".help" for usage hints. sqlite> .schema CREATE TABLE BB_URI_HEADREVS(key TEXT PRIMARY KEY NOT NULL, value TEXT); When our application starts up, it determines if it need to clear our the table. In the event that it does (which is most of the time) it uses the following query: BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT; Normally, this query takes no more than 1-3 seconds to complete, however, on rare occasion this will take an order of magnitude more (20-30 seconds). The real kicker here, is that I am never adding any rows to the database (e.g. it is always completely empty), so an order of magnitude increase seems unnecessary to erase an already empty table. If it makes any difference, the actual delay occurs when the COMMIT statement is executed, the DELETE FROM goes pretty fast. For reference, the following pragmas are used: pragma synchronous = normal; pragma journal_mode = WAL; pragma wal_autocheckpoint = 100; I use the small wal_autocheckpoint because the database is read- mostly, and we would rather have fast readers at the expense of occasional slow writes. WAL mode is used because we access the database from multiple processes, and we need the occasional write to not block readers. To be completely honest, this problem manifests under heavy I/O load, so I'm not suggesting it is necessarily sure that it is sqlite at fault, but the order of magnitude difference seems a bit extreme. 1) I used to use the rollback journal and didn't really see this problem, is there something about WAL mode that is more sensitive to I/O delay than the rollback journal? 2) Is there something that sqlite is doing "in the background" that might be making this query slow? 3) Are the some settings I could change that might make a difference? 4) Is there some sort of profiling I could enable to help pinpoint (or confirm) that this is indeed due to I/O delay and not something internal to sqlite? For reference, here is the complete log of SQL that the application executes on startup. Only the delay takes a significant amount of time. BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS BB_URI_HEADREVS(key TEXT PRIMARY KEY NOT NULL, value TEXT); COMMIT; BEGIN TRANSACTION; SELECT COUNT(key) FROM BB_URI_HEADREVS; COMMIT; BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT; Thanks all for your time, -- Joshua Watt <jpewhac...@gmail.com> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
