Re: [sqlite] Is there a best practice for breaking up a large update?

There are other writers, but they create new stuff, while this big update wants to consolidate info about existing stuff. There are also pure readers. Some of the docs on WAL logging had caveats that put me off. Not knowing exactly what I was going to be doing with the database, I could not be

Re: [sqlite] IN verses EXISTS Query Speed

Dave Blake wrote: > SELECT * FROM tablea > WHERE EXISTS (SELECT 1 FROM tableb WHERE tablea.id = tableb.id AND ...) > > is quicker than > SELECT * FROM tablea > WHERE tablea.id IN (SELECT tableb.id FROM tableb WHERE ...) > > Is there any reason for this to be always true in SQLite, or is it query

Re: [sqlite] IN verses EXISTS Query Speed

It depends on the data shape. In the first case, using a correlated subquery, the outer table query is processed applying all applicable joins and where conditions, and if and only if the row is still a candidate is the correlated subquery performed. If it succeeds the result row is output.

[sqlite] Why takes the second SELECT three times as much time?

I have defined the following table: CREATE TABLE messages ( dateTEXT NOT NULL DEFAULT CURRENT_DATE, timeTEXT NOT NULL DEFAULT CURRENT_TIME, typeTEXT NOT NULL, messageTEXT NOT NULL, PRIMARY KEY (date, time, type) ); CREATE INDEX

[sqlite] IN verses EXISTS Query Speed

Some simple testing is showing using an EXISTS statement is generally quicker then using an IN e.g. SELECT * FROM tablea WHERE EXISTS (SELECT 1 FROM tableb WHERE tablea.id = tableb.id AND ...) is quicker than SELECT * FROM tablea WHERE tablea.id IN (SELECT tableb.id FROM tableb WHERE ...) Is

Re: [sqlite] freebsd 11 SQLite build: readline/readline.h file not found

On 15 October 2016 at 23:45, jungle Boogie wrote: > Readline.h is found here: > # find / -name readline.h > /usr/local/include/editline/readline.h > /usr/local/include/readline/readline.h > /usr/local/include/guile/2.0/readline.h > /usr/include/edit/readline/readline.h

[sqlite] freebsd 11 SQLite build: readline/readline.h file not found

Hi All, I just re-installed freebsd 11 on a machine of mine and as usual, I build sqlite from source. However, I see this: sqlite3/src/shell.c:66:11: fatal error: 'readline/readline.h' file not found I'm quite sure I didn't have problems on the 10.x branch with sqlite, so I don't know if things

Re: [sqlite] Why takes the second SELECT three times as much time?

On 16-10-16 12:00, Cecil Westerhof wrote: I have defined the following table: CREATE TABLE messages ( dateTEXT NOT NULL DEFAULT CURRENT_DATE, timeTEXT NOT NULL DEFAULT CURRENT_TIME, typeTEXT NOT NULL, messageTEXT NOT NULL,

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

But be careful, as you can't change all records from 3 to 4 and then 4 to 5, as the 4 to 5 will contain the records you've just moved from 3 to 4 Canofworms.jpg. ;) Thanks, Chris On 15 Oct 2016 5:46 p.m., "Richard Damon" wrote: > On 10/15/16 12:15 PM, Simon

[sqlite] Regarding Hebrew data in Sqlite db

Hello team, I am using sqlite3.exe for bulk insertion in C#. I am facing issue when insert Hebrew data. As it contains double qoute(") as character and it does not support in insertion. After surfing, I came to know that I have to do double it and append same as suffix and prefix of word then it

Re: [sqlite] Regarding Hebrew data in Sqlite db

On 15 Oct 2016, at 5:26pm, Vaibhav Shah wrote: > I am using sqlite3.exe for bulk insertion in C#. I am facing issue when > insert Hebrew data. As it contains double qoute(") as character and it does > not support in insertion. Dear Vaibhav, Happy to help if you can

Re: [sqlite] Why takes the second SELECT three times as much time?

> On Oct 16, 2016, at 4:49 AM, Luuk wrote: > > Because your second query has to build the complete view before it can decide > if a result is between the selected dates? I didn’t think a view had a physical manifestation that had to be built; I thought it was just a

Re: [sqlite] Why takes the second SELECT three times as much time?

On 16 Oct 2016, at 7:17pm, Jens Alfke wrote: > I didn’t think a view had a physical manifestation that had to be built; I > thought it was just a shortcut/macro for a nested SELECT statement. > Or is the query optimizer not able to convert the nested SELECT into the same >

Re: [sqlite] Regarding Hebrew data in Sqlite db

> On Oct 15, 2016, at 9:26 AM, Vaibhav Shah wrote: > > I am using sqlite3.exe for bulk insertion in C#. I am facing issue when > insert Hebrew data. As it contains double qoute(") as character and it does > not support in insertion. It’s almost always a bad idea to put

Re: [sqlite] Why takes the second SELECT three times as much time?

See https://www.sqlite.org/optoverview.html under section 10.0 Query Flattening Your query is: SELECT * FROM WHERE which could be treated as SELECT * FROM (view select statement) WHERE condition and then flattened. Note however that the query WILL NOT be flattened because of rule #2,

Re: [sqlite] Why takes the second SELECT three times as much time?

2016-10-16 21:05 GMT+02:00 Keith Medcalf : > > See https://www.sqlite.org/optoverview.html > under section 10.0 Query Flattening > > Your query is: > > SELECT * FROM WHERE > > which could be treated as > > SELECT * > FROM (view select statement) > WHERE condition > > and

[sqlite] A possible double bug?

Hi, I was wondering about the different behavior of inserting a Double as a String vs as a value with a prepare statement in C. Consider an example when the value: 62.027393 is inserted as a String and as value with a prepared statement, for instance: "CREATE TABLE test (foo REAL)" "INSERT

Re: [sqlite] A possible double bug?

On Sunday, 16 October, 2016 12:03, Victor Evertsson wrote: > I was wondering about the different behavior of inserting a Double as a > String vs as a value with a prepare statement in C. > Consider an example when the value: 62.027393 is inserted as a String and >