Values are for retrieving 100.000 rows with a where clause not satisfiable from the index but true for alle rows
asql> select count() from <virtual>; CPU Time: user 0.092986 sys 0.000000 asql> select count() from <virtual> where <fx>=4; CPU Time: user 0.189971 sys 0.000000 CPU Time: user 0.199969 sys 0.000000 CPU Time: user 0.199970 sys 0.000000 asql> select count() from <native> where <fx>=4; CPU Time: user 0.086987 sys 0.010998 CPU Time: user 0.085987 sys 0.009999 CPU Time: user 0.076988 sys 0.002000 -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Bob Friesenhahn Gesendet: Mittwoch, 08. Februar 2017 15:39 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Virtual table vs real table query performance On Wed, 8 Feb 2017, Hick Gunter wrote: > 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 The case I am interested is pure read performance of a single column element at a time given properly implemented xBestIndex and xFilter support. Rows are not being added/removed using sqlite. It is possible that native tables can be faster since the implementation is not limited to the rigid set of callback functions provided for virtual tables to use and of course the amalgamation is optimized by the compiler as one source module. By tracing the callbacks, we do see that our implementation is not invoking the callbacks more times than necessary (which was not the case before xBestIndex and xFilter support was added). Due to the requirements of the implementation, POSIX reader/writer locks are used so there is some low-contention locking overhead. Bob -- Bob Friesenhahn 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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
