Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

2017-09-11 Thread J. King
Doh. I meant the linked document on the error log. Silly me. On September 11, 2017 9:41:39 PM EDT, "J. King" wrote: >There's an extra word in the first paragraph of Section 4 of that >document, by the way: > >" The error logger callback has

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

2017-09-11 Thread J. King
There's an extra word in the first paragraph of Section 4 of that document, by the way: " The error logger callback has also proven useful in catching errors occasional errors that the application misses..." On September 11, 2017 11:22:50 AM EDT, Dan Kennedy wrote: >On

Re: [sqlite] Table-valued function crashing in SQLite 3.20

2017-09-11 Thread Richard Hipp
On 9/11/17, Jens Alfke wrote: > We have a virtual table serving as a table-valued function, which has worked > fine up through SQLite 3.19, but now in 3.20.1 is crashing whenever it’s > used. Does it work on trunk? -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Table-valued function crashing in SQLite 3.20

2017-09-11 Thread Jens Alfke
For what it’s worth, the instructions generated in 3.19.2 (which don't crash) are slightly different: the OpenRead and Rewind instructions are at the top, not down at location 15 — 0 Init0 24 0 00 Start at 24 1 OpenRead0

[sqlite] Table-valued function crashing in SQLite 3.20

2017-09-11 Thread Jens Alfke
We have a virtual table serving as a table-valued function, which has worked fine up through SQLite 3.19, but now in 3.20.1 is crashing whenever it’s used. I know there were changes in 3.20 relating to native functions taking pointers, but this doesn’t seem to be related to that. Were there any

Re: [sqlite] PRAGMA journal_size_limit prevents -wal growth or only size outside a transaction?

2017-09-11 Thread Howard Kapustein
Looking through the source a bit I see sqlite3_busy_timeout is a no-fail operation (aside from misuse) so sqlite3_busy_timeout(0); SQLITE_CHECKPOINT_TRUNCATEsqlite3_busy_timeout(n); does what I need. Thanks all. - Howard P.S. sqlite3_close intentionally doesn't respect

Re: [sqlite] Proof that a line has been modified

2017-09-11 Thread Paxdo
I just got all the answers! I must have an email problem. I'm going to look at all of this, thank you very much everyone! Dominique Devienne 8 septembre 2017 à 11:41 On Fri, Sep 8, 2017 at 12:29 AM, Nico Williams See also

Re: [sqlite] compiling for android

2017-09-11 Thread Christian Czech
Hi, it guess it must be some special settings. We used sqlite quite some time now and the last build problem we got with ndk is quite long time ago. Regards, Christian --- derago e. K., Gartenstr. 4, D-79807 Lottstetten, Amtsgericht Freiburg/Br. HRA621012, weitere

Re: [sqlite] compiling for android

2017-09-11 Thread Dan Kennedy
On 09/11/2017 05:20 AM, Mark Sibly wrote: Hi, I just encountered a problem trying to build the sqlite amalgamation package for android with the latest NDK (v15.2.4203891) on windows 10. The compiler couldn't find mmap, which was causing a ton of errors. I solved it by sticking this at the

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

2017-09-11 Thread Dan Kennedy
On 09/10/2017 08:30 PM, R Smith wrote: Well yes but the documentation suggests that one could expect a slight degradation. The words "works best with" does not seem to imbue an idea of "give WITHOUT ROWID tables a wide berth when your tables are more than few columns wide", and I don't think

Re: [sqlite] Intel 17

2017-09-11 Thread Dominique Devienne
On Mon, Sep 11, 2017 at 4:49 PM, Richard Hipp wrote: > On 9/11/17, Dominique Devienne wrote: > > FYI, here are the modifications we did to a 3.19.3 amalgamation to build > > with the Intel 17 compiler. Note that we have a few modifs of our own, so > > line

Re: [sqlite] Intel 17

2017-09-11 Thread Richard Hipp
On 9/11/17, Dominique Devienne wrote: > FYI, here are the modifications we did to a 3.19.3 amalgamation to build > with the Intel 17 compiler. Note that we have a few modifs of our own, so > line numbers might be off. --DD > > PS: I hope the formatting goes through. Was

Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

2017-09-11 Thread Hick Gunter
If you have 4 set clauses, the those 4 fields will be updated with the same values for all the rows matching the where clause with just a single call to sqlite3_step(). If you actually want to update only 1 of the fields in 1 record, then you must bind the current values (which are unknown and

Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

2017-09-11 Thread ghalwasi
hi Darko, >If you're preparing that statement more than once then you are wasting time, there's no reason whatsoever to do it. You're also wasting time if you make a bind call to set any column that hasn't changed since the last time you executed the statement. >The entire row is rewritten

[sqlite] compiling for android

2017-09-11 Thread Mark Sibly
Hi, I just encountered a problem trying to build the sqlite amalgamation package for android with the latest NDK (v15.2.4203891) on windows 10. The compiler couldn't find mmap, which was causing a ton of errors. I solved it by sticking this at the top-ish of sqlite3.c #if __ANDROID__ #include

Re: [sqlite] Need help with SQL query

2017-09-11 Thread Kees Nuyt
On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt wrote: > CREATE TABLE ATTRIBUTES ( > ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE, > KEY TEXT, > VALUE TEXT, > PRIMARY KEY (ITEM_ID,KEY) > ) WITHOUT ROWID; > CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);

Re: [sqlite] Seeking advice on improving batch sql update performance on zipvfs

2017-09-11 Thread Eric Grange
> I've also attached results of "EXPLAIN UPDATE ITEM SET FIELD4 = 10 WHERE DS = 15;". Not zipvfs specific, but that kind of update can be quite inefficient if the record is large (in terms of bytes, or records per database page) as your table declaration hints to. This will be especially

Re: [sqlite] Need help with SQL query

2017-09-11 Thread Kees Nuyt
On Sun, 10 Sep 2017 20:35:16 -0700, Vikas Aditya wrote: > Hi All, > > I need some help in figuring our right query syntax for querying > items from two tables. We have two tables. One of the table has > list of items. And Second table has additional attributes. Adding

Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread Frank Millman
On Mon Sep 11, 2017 at 11:15:22AM +0200, no...@null.net wrote: > SELECT > acc_no, > acc_name, > SUM(i_90.invoice_bal) AS 90_days, > SUM(i_current.invoice_bal) AS current > FROM >debtors_table > LEFT JOIN > invoices i_90 > ON >

Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread nomad
On Mon Sep 11, 2017 at 11:15:22AM +0200, no...@null.net wrote: > SELECT > acc_no, > acc_name, > SUM(i_90.invoice_bal) AS 90_days, > SUM(i_current.invoice_bal) AS current > FROM >debtors_table > LEFT JOIN > invoices i_90 > ON >

Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread nomad
On Mon Sep 11, 2017 at 08:40:41AM +0200, Frank Millman wrote: > I could structure it like this (pseudo code) - > > SELECT acc_no, acc_name, > (SELECT SUM(invoice_bal) FROM invoices > WHERE invoice_date <= date_5) AS 120_days, > (SELECT SUM(invoice_bal) FROM invoices

[sqlite] Intel 17

2017-09-11 Thread Dominique Devienne
FYI, here are the modifications we did to a 3.19.3 amalgamation to build with the Intel 17 compiler. Note that we have a few modifs of our own, so line numbers might be off. --DD PS: I hope the formatting goes through. Was garbled when I converted to plain text, so trying in rich-text, sorry.

Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread Frank Millman
Clemens Ladisch wrote: > Frank Millman wrote: > > I changed it to use a WITH clause to make one scan of the invoice table and > > store the results. > The WITH clause itself does not store anything; it's syntactic sugar [...] To > force the ageing results to be stored, you'd have to create a

Re: [sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread Clemens Ladisch
Frank Millman wrote: > SELECT acc_no, acc_name, > (SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date <= date_5) > AS 120_days, > (SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_5 > AND invoice_date <= date_4) AS 90_days, > (SELECT

[sqlite] Selecting data from WITH clause is very slow

2017-09-11 Thread Frank Millman
Hi all I am writing an accounting system, and want to select ‘aged balances’ from the debtors table. To do this, I need to assign each outstanding invoice to an ageing ‘bucket’, where each bucket contains the sum of invoices where date > start_date and date <= end_date. There will be five