[sqlite] Weird (slow) TEMP B-TREE ORDER BY

Forgot to add DDL for the outer table: CREATE TABLE materializedInstances ( provider TEXT(20), instanceId TEXT(20), packageId INTEGER, date TEXT(20), duration INTEGET, transportType INTEGER, CONSTRAINT MATERIALIZEDINSTANCES_PK PRIMARY KEY

[sqlite] Weird (slow) TEMP B-TREE ORDER BY

Hi Donald, Thanks for the answer, I'll try to address these issues: 1,4) I was lazy and got the DDL from the GUI without looking at it. Here is the proper form from the master table (it shouldnt affect replicability though) CREATE TABLE [materializedInstances] ([provider] TEXT, [instanceId]

[sqlite] Weird (slow) TEMP B-TREE ORDER BY

Hi guys, Sorry to be a pain, but does anyone have an idea about this issue? I have to recommend switching DBs this week based on this, and I am reluctant to do so, but we haven't been able to figure a fix either. Thanks, Dinu

[sqlite] UPDATE Problem

Hi Peter, From https://www.sqlite.org/datatype3.html: "When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible"; basically any fraction you insert into a NUMERIC

[sqlite] Weird (slow) TEMP B-TREE ORDER BY

Hi Richard, Like said, we've already tried: CREATE TEMP TABLE x AS (...query without outer sort...); CREATE xind ON x (price); SELECT * FROM x ORDER BY 2 but it also takes 30 seconds; for some really esoteric reason, CREATE TABLE x AS ... takes all of those 30s, even though the execution plan

[sqlite] Releasing a read (SHARED) lock

You should be also aware of a more common pitfall: unclosed result sets. Any lock is held until you read PAST the last row or you call stmt_finalize (or the equivalent abstraction in your DBAL). Always close select statements. On Mar 12, 2015 11:40 AM, "R.Smith" wrote: > > > On 2015-03-12 04:38

[sqlite] Releasing a read (SHARED) lock

There is also a more obscure side-effect exhibited by sqlite only: if you issue consecutive unfinalized select statements, it never releases the write lock (although they should be atomic), but it does trip the deadlock mechanism; any writer in the wait will receive SQLITE_BUSY at the very

[sqlite] Releasing a read (SHARED) lock

when you have a leaked lock. On 13.03.2015 02:17, Barry wrote: > On 13 March 2015 at 01:21, Dinu Marina wrote: > >> You should be also aware of a more common pitfall: unclosed result sets. >> Any lock is held until you read PAST the last row or you call stmt_finalize >

[sqlite] Releasing a read (SHARED) lock

7am, Barry wrote: > >> On 13 March 2015 at 01:21, Dinu Marina wrote: >> >>> You should be also aware of a more common pitfall: unclosed result sets. >>> Any lock is held until you read PAST the last row or you call stmt_finalize >>> (or the equivalent a

[sqlite] Weird (slow) TEMP B-TREE ORDER BY

e-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of Dinu Marina >> Sent: donderdag 12 maart 2015 11:34 >> To: sqlite-users at mailinglists.sqlite.org >> Subject: Re: [sqlite] Weird (slow) TEMP B-TREE ORDER BY &

Re: [sqlite] Atomic DELETE index optimisation?

Update: we are also using WAL; I have noticed during a huge delete, the WAL also grows huge. Could this also be a reason for slowness, that SQLite duplicates every block that hosts a delete? Is there any way to work around this? On 17.12.2017 22:53, Dinu Marina wrote: Hi all, It became

[sqlite] Atomic DELETE index optimisation?

Hi all, It became apparent to me from performance measurements that the DELETE operation is very slow, when operating on large recordsets with indexes involved. My deduction is that SQLite updates the indexes for every deleted row, which in painstakingly slow... Since the DELETE should be