Re: [sqlite] Using WHERE clauses with REAL data

On 4 Mar 2014, at 4:14am, Donald Shepherd wrote: > It appears that using equals on floating point (REAL) data in WHERE clauses > doesn't necessarily work, presumably because of rounding errors - see below > for an example. Is this the case? Do I need to use BETWEEN

Re: [sqlite] Using WHERE clauses with REAL data

It's a nice idea but that's just some sample values generated by an emulator. I've compromised and am using round() to limit it to a few digits after the decimal when doing the comparison. On 4 March 2014 21:27, Simon Slavin wrote: > > On 4 Mar 2014, at 4:14am, Donald

Re: [sqlite] "INSERT" and "END TRANSACTION" time

On 4 Mar 2014, at 5:06am, romtek wrote: > If that were true, then I wouldn't be getting a very large speed-up when > enveloping write ops in an explicit transaction, would I? Sorry, I can't tell. It depends on how the virtual storage mechanism works. But if your

Re: [sqlite] Using WHERE clauses with REAL data

On 4 Mar 2014, at 10:33am, Donald Shepherd wrote: > It's a nice idea but that's just some sample values generated by an > emulator. I've compromised and am using round() to limit it to a few > digits after the decimal when doing the comparison. If you're using

Re: [sqlite] "INSERT" and "END TRANSACTION" time

On Tue, Mar 4, 2014 at 3:44 AM, Simon Slavin wrote: > > On 4 Mar 2014, at 1:15am, romtek wrote: > > > I have a question based on my observation. According to your numbers for > a > > 5400 RPM disk, one write op should take about 11 ms. However, it often >

Re: [sqlite] Virtual table API performance

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

Could you explain some more your solution? Does it work in this following case: select * from VT2(select * from VT1); by directly passing the rows from VT1 to VT2 (short-circuiting SQLite)? What would happen in the following case?: select * from VT2(select processrow(c1,c2,c3) from VT1);

Re: [sqlite] Virtual table API performance

Your VT1 table already has an xColumn implementation, possibly doing something like switch( p_column ) { case 1: sqlite3_result_xxx( p_ctx, v_rec->f1, ...); break; ... case n: sqlite3_result_xxx( p_ctx, v_rec->fn, ...); break;

[sqlite] Weird out of memory problem a prepare

Hi, I have this code that fails always with the error output next: = zSql= "SELECT r.name, s.content FROM resource AS r, static AS s WHERE (r.ids = s.ids AND r.name = ?);"; stmt = NULL; rc = sqlite3_prepare_v2(db, zSql, strlen(zSql), , NULL); if ( rc != SQLITE_OK ){ dprintf(

[sqlite] Handling SQLITE_PROTOCOL with WAL databases

Hi, I encounter the same issue as described here: https://www.mail-archive.com/sqlite-users%40sqlite.org/msg57092.html usually once or twice a day, when running a performance test-suite for my application (using SQLite 3.8.1). What is the correct way to handle the SQLITE_PROTOCOL errors that

Re: [sqlite] Weird out of memory problem a prepare

On 4 Mar 2014, at 3:09pm, Eduardo Morras wrote: > zSql= "SELECT r.name, s.content FROM resource AS r, static AS s WHERE (r.ids > = s.ids AND r.name = ?);"; > > [snip] > > Error on query: out of memory I think this might require comparing every row in resource with every

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

Re: [sqlite] Weird out of memory problem a prepare

On 4 Mar 2014, at 3:15pm, Simon Slavin wrote: > On 4 Mar 2014, at 3:09pm, Eduardo Morras wrote: > >> zSql= "SELECT r.name, s.content FROM resource AS r, static AS s WHERE (r.ids >> = s.ids AND r.name = ?);"; >> >> [snip] >> >> Error on query: out of

Re: [sqlite] Virtual table API performance

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

Re: [sqlite] Virtual table API performance

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

Re: [sqlite] Virtual table API performance

Thank you for the explanation. Answers inline. On 04/03/14 16:16, Hick Gunter wrote: Your VT1 table already has an xColumn implementation, possibly doing something like switch( p_column ) { case 1: sqlite3_result_xxx( p_ctx, v_rec->f1, ...); break;

Re: [sqlite] Virtual table API performance

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

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

[sqlite] Compilation problems after upgrading from v3.7.6.3 to v3.8.4

I am having compilation problems after upgrading from v3.7.6.3 to v3.8.4 on the static const int iLn = __LINE__+4; < lines containing these statements Tried with VS2012 and VS2013, can someone help me fix this ? error C2099: initializer is not a constant ..\sqlite3\sqlite3.c 73531 error

Re: [sqlite] Weird out of memory problem a prepare

On Tue, 4 Mar 2014 15:19:24 + Simon Slavin wrote: > > On 4 Mar 2014, at 3:15pm, Simon Slavin wrote: > > > On 4 Mar 2014, at 3:09pm, Eduardo Morras wrote: > > > >> zSql= "SELECT r.name, s.content FROM resource AS r, static AS

Re: [sqlite] Weird out of memory problem a prepare

Eduardo Morras wrote: > Trying simple "SELECT r.name FROM resource AS r WHERE r.name = ?" gets "Out > of memory" too calling preparev2. This has nothing to do with the query itself. If you aren't doing something funny with the memory allocator, it's likely that SQLite's database object got

Re: [sqlite] Compilation problems after upgrading from v3.7.6.3 to v3.8.4

On Tue, Mar 4, 2014 at 2:59 PM, ioannis wrote: > I am having compilation problems after upgrading from v3.7.6.3 to v3.8.4 on > the > static const int iLn = __LINE__+4; < lines containing these statements > We have had any trouble here. Are you redefining __LINE__

Re: [sqlite] Weird out of memory problem a prepare

Hello, what's your SharedSection value? [heap limitation] Best Regards. On Tue, Mar 4, 2014 at 12:16 PM, Clemens Ladisch wrote: > Eduardo Morras wrote: > > Trying simple "SELECT r.name FROM resource AS r WHERE r.name = ?" gets > "Out of memory" too calling preparev2. > >

Re: [sqlite] Compilation problems after upgrading from v3.7.6.3 to v3.8.4

On Tue, Mar 4, 2014 at 8:59 PM, ioannis wrote: > error C2099: initializer is not a constant ..\sqlite3\sqlite3.c 73531 > According to the MS docs, __LINE__ is a "decimal integer constant" in VS 2013: http://msdn.microsoft.com/en-us/library/b0084kay.aspx but what you're

Re: [sqlite] Virtual table API performance

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

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

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] Compilation problems after upgrading from v3.7.6.3 to v3.8.4

It seems that the compilation errors i reported earlier error C2099: initializer is not a constant ..\sqlite3\sqlite3.c 73531, etc only occured in debug mode, and, were caused by flag: Program Database for Edit And Continue (/ZI) after changing the flag to: Program Database (/Zi) everything

Re: [sqlite] Weird out of memory problem a prepare

On 4 Mar 2014, at 8:05pm, Eduardo Morras wrote: > The tables have 4 rows each one, Hahahaha. Hah. That changes things. You have something wrong. Either the database is corrupt (check it with a PRAGMA or make another one) or your code is messed up somehow. As a test,