This is with a generic, user definable table and index structure capable virtual table implementation; it is not a "one module per table" statically typed heavily optimized implementation.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Mittwoch, 08. Februar 2017 16:42 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Virtual table vs real table query performance On Wed, Feb 8, 2017 at 4:30 PM, Hick Gunter <h...@scigames.at> wrote: > 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 > Frankly I'm surprised it's slower than "native" SQLite. In bulk-insert, random lookup, and table-delete timings we did in 2009 between native in-memory SQLite, and pure-C++ virtual tables accessing pure C++ data structures (i.e. vm/reflection/introspection/dynamic lookup as in Python for example, but direct addressing of statically typed data), the virtual tables was always faster, and not by a small margin. Admittedly it was a long time ago, and SQLite is getting faster all the time for sure, but you can't beat static typing of memory addressable structures, vs scanning pages of table data and dynamically/serially decoding variable sizes rows within those pages. So something like "non-native" code or something "dynamic" is hiding in the virtual table impl, no? --DD _______________________________________________ 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
