In our tests the bind API can acquire from the Python side more than 20
values in a single call, at the same time that xColumn acquires 2 values.
Most of the cost is in the callback and not in submitting a row's values
through bind's API .
So with the exception of queries that need only 1 column, IMHO
everything else should go through the xNextRow API.
To keep the complexity to the lowest minimum, my proposal is to use
xNextRow API only for queries that only "scan" over a virtual table (no
filtering).
l.
On 04/03/14 18:23, Hick Gunter wrote:
My guess: Yes.
It would require implementing an new opcode, either only for virtual tables or
also for native tables too, that accepts a list of field numbers (currently
there are only 5 parameters possible for an opcode and some of them have fixed
meanings).
And the logic to generate theses opcodes based on the capabilities of the loaded table
module combined with the requirements of the subject query (fields required for JOIN are
fetched separately from those required for the result set) and the result of the
xBestIndex calls (where it is possible to set the "omit" flag to suppress
generation of a comparison). This also adds to the complexity of register allocation.
Take for example a join that needs 3 fields for the comparison, 2 of which are
also required for the result set of 7 fields total. Do you request all 10
fields at once (wastes up to 9 fields worth of effort if the JOIN is not met)?
Or the 3 fields first and the 5 others only if the join matches (must allocate
consecutive registers to build a result set)? Or 3 first and then 7 (which
approximates the current behavior, as the 2 common fields are fetched twice on
a match)?
And a set of new sqlite3_result routines that specify which of the various
requested fields' value is being set.
-----Ursprüngliche Nachricht-----
Von: J. Merrill [mailto:j.merr...@enlyton.com]
Gesendet: Dienstag, 04. März 2014 16:23
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Virtual table API performance
Eleytherios Stamatogiannakis wrote
Our main test case is TPCH, a standard DB benchmark. The "lineitem"
table of TPCH contains 16 columns, which for 10M rows would require
160M xColumn callbacks, to pass it through the virtual table API.
These callbacks are very expensive, especially when at the other end
sits a VM (CPython or PyPy) handling them.
Would it be very difficult to arrange for an option that would request that
SQLite issue a single more-complex xMultiColumns (a sample name) callback
request, with a way for multiple results to be returned, rather than many
xColumn callbacks? This would reduce the number of calls across the VM boundary.
Applications that don't implement xMultiColumns (and request its use) would see
no change; those that do would get the performance boost.
J. Merrill
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Why-does-type-affinity-declared-on-a-foreign-key-column-affect-join-speed-tp74183p74295.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-----------------------------------------------------------------------
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any
purposes, or disclose its contents to any person as to do so could be a breach
of confidence. Thank you for your cooperation.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
