Re: [sqlite] Virtual table API performance

2014-03-05 Thread Alek Paunov
On 05.03.2014 11:02, RSmith wrote: On 2014/03/05 10:41, Dominique Devienne wrote: On Tue, Mar 4, 2014 at 9:41 PM, Elefterios Stamatogiannakis One thing that IMHO long term might improve the situation would be if SQLite's own "native" tables would use the same Virtual Table API,//... ...//Of

Re: [sqlite] Virtual table API performance

2014-03-05 Thread RSmith
On 2014/03/05 10:41, Dominique Devienne wrote: On Tue, Mar 4, 2014 at 9:41 PM, Elefterios Stamatogiannakis One thing that IMHO long term might improve the situation would be if SQLite's own "native" tables would use the same Virtual Table API,//... ...//Of course, the above is a "naive"

Re: [sqlite] Virtual table API performance

2014-03-05 Thread Dominique Devienne
On Tue, Mar 4, 2014 at 9:41 PM, Elefterios Stamatogiannakis wrote: > [...] Nevertheless, having people do crazy/clever hacks like that to avoid the > inefficiencies of an API, gives a hint that something might be problematic. > > In a previous email of mine, i had said half

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Elefterios Stamatogiannakis
Also, i want to note the pretty clever "hack" from Hick Gunter that works by passing a "proxy" for the row (instead of the whole row's values) to SQLite. This proxy gets expanded using the xColumn API by a virtual table outside the whole query. In this way a query using multiple VTs, only

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Elefterios Stamatogiannakis
On 4/3/2014 11:33 μμ, Alek Paunov wrote: On 04.03.2014 20:25, Eleytherios Stamatogiannakis wrote: On 04/03/14 20:11, Alek Paunov wrote: On 04.03.2014 15:05, Eleytherios Stamatogiannakis wrote: On 03/03/14 03:01, Alek Paunov wrote: It seems that the easiest optimization for this (very often)

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Alek Paunov
On 04.03.2014 20:25, Eleytherios Stamatogiannakis wrote: On 04/03/14 20:11, Alek Paunov wrote: On 04.03.2014 15:05, Eleytherios Stamatogiannakis wrote: On 03/03/14 03:01, Alek Paunov wrote: It seems that the easiest optimization for this (very often) VT use case (bulk streaming) is SQLite

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Eleytherios Stamatogiannakis
On 04/03/14 20:11, Alek Paunov wrote: On 04.03.2014 15:05, Eleytherios Stamatogiannakis wrote: On 03/03/14 03:01, Alek Paunov wrote: It seems that the easiest optimization for this (very often) VT use case (bulk streaming) is SQLite add-on in _C_ to be written, implementing vtable interface

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Alek Paunov
On 04.03.2014 15:05, Eleytherios Stamatogiannakis wrote: On 03/03/14 03:01, Alek Paunov wrote: It seems that the easiest optimization for this (very often) VT use case (bulk streaming) is SQLite add-on in _C_ to be written, implementing vtable interface specialization containing xNextPage

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Eleytherios Stamatogiannakis
-Ursprüngliche Nachricht- Von: Eleytherios Stamatogiannakis [mailto:est...@gmail.com] Gesendet: Dienstag, 04. März 2014 14:15 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Virtual table API performance Could you explain some more your solution? Does it work in this following case: sele

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Eleytherios Stamatogiannakis
s 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

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Hick Gunter
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 E

Re: [sqlite] Virtual table API performance

2014-03-04 Thread J. Merrill
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

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Hick Gunter
t __rec from VT1" in your xNext implementation to have sqlite3_result_int64() called half as often. HTH -Ursprüngliche Nachricht- Von: Eleytherios Stamatogiannakis [mailto:est...@gmail.com] Gesendet: Dienstag, 04. März 2014 14:15 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Virt

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Eleytherios Stamatogiannakis
ios Stamatogiannakis [mailto:est...@gmail.com] Gesendet: Sonntag, 02. März 2014 20:39 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Virtual table API performance We have both input and output virtual tables that avoid hitting the hard disk and are also able to compress the incoming and out

Re: [sqlite] Virtual table API performance

2014-03-04 Thread Eleytherios Stamatogiannakis
On 03/03/14 03:01, Alek Paunov wrote: On 02.03.2014 21:38, Elefterios Stamatogiannakis wrote: Under this view, the efficiency of the virtual table api is very important. Above query only uses 2 VTs in it, but we have other queries that use a lot more VTs than that. Max tests in C shows 2x CPU

Re: [sqlite] Virtual table API performance

2014-03-03 Thread Hick Gunter
esendet: Sonntag, 02. März 2014 20:39 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Virtual table API performance We have both input and output virtual tables that avoid hitting the hard disk and are also able to compress the incoming and outgoing data. We have a virtual table that takes as inp

Re: [sqlite] Virtual table API performance

2014-03-02 Thread Alek Paunov
On 02.03.2014 21:38, Elefterios Stamatogiannakis wrote: Under this view, the efficiency of the virtual table api is very important. Above query only uses 2 VTs in it, but we have other queries that use a lot more VTs than that. Max tests in C shows 2x CPU work, but he explains that the test is

Re: [sqlite] Virtual table API performance

2014-03-02 Thread Elefterios Stamatogiannakis
We have both input and output virtual tables that avoid hitting the hard disk and are also able to compress the incoming and outgoing data. We have a virtual table that takes as input a query and sends the data to a port on another machine. This virtual table is called "OUTPUT". And another

Re: [sqlite] Virtual table API performance

2014-03-02 Thread Max Vlasov
On Sun, Mar 2, 2014 at 5:21 PM, Elefterios 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.

Re: [sqlite] Virtual table API performance

2014-03-02 Thread Elefterios Stamatogiannakis
In our performance tests we try to work with data and queries that are representative of what we would find in a typical DB. This means a lot of "small" values (ints, floats, small strings), and 5-20 columns. Our main test case is TPCH, a standard DB benchmark. The "lineitem" table of TPCH

Re: [sqlite] Virtual table API performance

2014-03-01 Thread Max Vlasov
Hi, thanks for explaining your syntax in another post. Now about virtual tables if you don't mind. On Fri, Feb 28, 2014 at 8:24 PM, Eleytherios Stamatogiannakis wrote: > > If we load into SQLite, > > create table newtable as select * from READCOMPRESSEDFILE('ctable.rc'); >

[sqlite] Virtual table API performance

2014-02-28 Thread Eleytherios Stamatogiannakis
Hello, First of all, i would like to say that, IMHO, SQLite's overall design and code quality is top notch. Having said that, SQLite's virtual table API, while beautifully designed, it is not very efficient. We have been hitting our heads for the last few years, on the virtual table API, to