I see, so in the native implementation you already have the whole table in memory and only use the clustered b-tree index to search for tuples. So I would not expect a large improvement from the virtual table implementation, but the virtual table being 5 times slower is strange. Maybe not the correct data structure used?
By the way, I had tried adding a virtual table in the sqlite amalgamation and I did not see observable difference. On the other hand, I have seen improvement in the virtual table utilization using the latest version of sqlite (in comparison to a release about a year ago). ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Bob Friesenhahn <bfrie...@simple.dallas.tx.us> Sent: Wednesday, February 8, 2017 4:09 PM To: SQLite mailing list Subject: Re: [sqlite] Virtual table vs real table query performance On Wed, 8 Feb 2017, Dimitris Bil wrote: > Do you perform the benchmark on the native database table using cold > cache or warm cache? Also, can you briefly describe what the > benchmark does and give the schema for the native database table? My benchmark repeatedly reads all of the columns one by one given row id and column name. The table is read many (e.g. 100) times so this is a warm cache test. The schema is not terribly important but the table we are trying to optimize (with 1800 or less rows) contains a 64-bit rowid, five integer values, and two short text string values. int64, uint32, uint32, text[16], uint8, text[16], text[18], uint8, uint32 What I am looking for is expected average virtual table performance vs native table performance for repeated column reads. Due to being a generic implementation (supporting many virtual tables), our virtual implementation uses programmed/dynamic marshalling rather that compiled marshalling. The schema definition is also dynamically generated. There are implementation overheads and it is useful to know what performance is possible (e.g. compared to native table performance) in order to know when the implementation is about as good as it can be. Bob -- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
