Having imlemented a memory-based virtual table complete with indices, full table scan and direct access via rowid (which happens to be the memory address of the row) I can do a batch delete of 100.000 rows (in a table with 1 composite index) in about 2 seconds (3.7 seconds with the condition) while running linux (RH 5.6 x86_64 VM) on a virtual machine. Deleting all rows of a native SQLite table (while checking for the value of a non-indexed field to avoid SQLite just dropping an re-creating the table) takes about 1 second.
Note that both operations require a full table scan to fill a „rowset“ (= SQLite internal temporary table) and that the virtual table function VUpdate expects the virtual table code to handle index deletetion which is explicitly coded in the native table case. asql> explain delete from <virtual>; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Goto 0 18 0 00 NULL 2 Integer 0 1 0 00 NULL 3 Null 0 2 0 00 NULL 4 VOpen 0 0 0 vtab:187BE588:2ACC1FDC4990 00 NULL 5 Integer 1 4 0 00 NULL 6 Integer 0 5 0 00 NULL 7 VFilter 0 12 4 00 NULL 8 Rowid 0 3 0 00 NULL 9 RowSetAdd 2 3 0 00 NULL 10 AddImm 1 1 0 00 NULL 11 VNext 0 8 0 00 NULL 12 Close 0 0 0 00 NULL 13 RowSetRead 2 16 3 00 NULL 14 VUpdate 0 1 3 vtab:187BE588:2ACC1FDC4990 02 NULL 15 Goto 0 13 0 00 NULL 16 ResultRow 1 1 0 00 NULL 17 Halt 0 0 0 00 NULL 18 VBegin 0 0 0 vtab:187BE588:2ACC1FDC4990 00 NULL 19 Goto 0 2 0 00 NULL asql> explain delete from <native> where <fx>=4; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Goto 0 31 0 00 NULL 2 Integer 0 1 0 00 NULL 3 Null 0 2 0 00 NULL 4 OpenRead 0 215 0 7 00 <native> 5 Rewind 0 13 0 00 NULL 6 Column 0 6 4 00 <native>.<fx> 7 Integer 4 5 0 00 NULL 8 Ne 5 12 4 collseq(BINARY) 6c NULL 9 Rowid 0 3 0 00 NULL 10 RowSetAdd 2 3 0 00 NULL 11 AddImm 1 1 0 00 NULL 12 Next 0 6 0 01 NULL 13 Close 0 0 0 00 NULL 14 OpenWrite 0 215 0 8 00 <native> 15 OpenWrite 1 1362 0 Keyinfo(5,BINARY,BINARY) 00 <index> 16 RowSetRead 2 27 3 00 NULL 17 NotExists 0 26 3 00 NULL 18 Rowid 0 11 0 00 NULL 19 Column 0 1 6 00 <native>.<kf1> 20 Column 0 2 7 00 <native>.<kf2> 21 Column 0 3 8 00 <native>.<kf3> 22 Column 0 4 9 00 <native>.<kf4> 23 Column 0 5 10 00 <native>.<kf5> 24 IdxDelete 1 6 6 00 NULL 25 Delete 0 1 0 <native> 00 NULL 26 Goto 0 16 0 00 NULL 27 Close 1 1362 0 00 NULL 28 Close 0 0 0 00 NULL 29 ResultRow 1 1 0 00 NULL 30 Halt 0 0 0 00 NULL 31 Transaction 0 1 0 00 NULL 32 VerifyCookie 0 1191 0 00 NULL 33 TableLock 0 215 1 <native> 00 NULL 34 Goto 0 2 0 00 NULL -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Bob Friesenhahn Gesendet: Dienstag, 07. Februar 2017 22:06 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Virtual table vs real table query performance We are trying to improve the query performance of our virtual table implementation (which is implemented in C). Due to requirements of external code, a specified column of a specified row (by rowid) is queried at a time (the least efficient means of access). Our virtual table is accessing entries in a memory-based array. I have implemented a benchmark script written in Python using the APSW wrapper. The benchmark script reveals that access to a native database table is 5 times faster than access to our virtual table. Intuitively, I would think that access to a memory-based virtual table could be faster than native tables. Our developer has implemented xBestIndex and xFilter support which is intended to result in direct access to the requested row rather than scanning the whole table. What is the expected performance of a properly implemented virtual table (assuming little additional overhead) vs a native table? Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us<mailto:bfrie...@simple.dallas.tx.us>, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer, http://www.GraphicsMagick.org/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH<http://www.scigames.at> | 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
