Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-20 Thread Jens Alfke
> On Jun 20, 2017, at 12:48 AM, Robert M. Münch > wrote: > > Since at one point I know all the columns and later on a couple columns could > be added, I'm thinking about creating the table with the known columns in a > classical way and have one additional JSON

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-20 Thread Simon Slavin
On 20 Jun 2017, at 8:46am, Robert M. Münch wrote: > The WHERE parts look like this: > > ... WHERE col-1 <> NULL AND col-2 <> NULL ... AND col-x <> NULL > > ... WHERE col-x LIKE ... My bet is that it’s these clauses which are slowing down execution of your

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-20 Thread Robert M. Münch
On 20 Jun 2017, at 2:34, Jens Alfke wrote: > My understanding from reading the docs is that SQLite view’s aren’t “built” > at all: their contents have no physical existence in the database, the views > are simply macros that transform the statements that use them. (Correct me if > I’m wrong; I

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-20 Thread Robert M. Münch
On 19 Jun 2017, at 17:09, Simon Slavin wrote: >> CREATE VIEW json AS SELECT rec_id, json_extract(json_value,'$.col-1') as >> col1, json_extract(json_value,'$.col-2') as col2, ... , >> json_extract(json_value,'$.col-50') as col50 FROM a > Please supply a same SELECT command that you would use

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-19 Thread Jens Alfke
> On Jun 19, 2017, at 5:43 PM, Simon Slavin wrote: > >> You can create indexes to support JSON1 queries by using the same json_xx >> function calls in a CREATE INDEX statement. > > That’s a great idea. I don’t know if it works, though. It does, and I believe it was the

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-19 Thread Wout Mertens
On Tue, Jun 20, 2017 at 2:43 AM Simon Slavin wrote: > On 20 Jun 2017, at 1:34am, Jens Alfke wrote: > > > You can create indexes to support JSON1 queries by using the same > json_xx function calls in a CREATE INDEX statement. > > That’s a great idea. I

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-19 Thread Simon Slavin
On 20 Jun 2017, at 1:34am, Jens Alfke wrote: > My understanding from reading the docs is that SQLite view’s aren’t “built” > at all: their contents have no physical existence in the database, the views > are simply macros that transform the statements that use them.

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-19 Thread Jens Alfke
> On Jun 19, 2017, at 6:50 AM, Robert M. Münch > wrote: > > This view works and of course takes some time to build. My understanding from reading the docs is that SQLite view’s aren’t “built” at all: their contents have no physical existence in the database, the

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-19 Thread Simon Slavin
On 19 Jun 2017, at 2:50pm, Robert M. Münch wrote: > Hi, I have a table A(rec_id, JSON-of-record) and I create a view like this: > > CREATE VIEW json AS SELECT rec_id, json_extract(json_value,'$.col-1') as > col1, json_extract(json_value,'$.col-2') as col2, ... ,

[sqlite] JSON1 extension & speed-up by using indices etc.?

2017-06-19 Thread Robert M. Münch
Hi, I have a table A(rec_id, JSON-of-record) and I create a view like this: CREATE VIEW json AS SELECT rec_id, json_extract(json_value,'$.col-1') as col1, json_extract(json_value,'$.col-2') as col2, ... , json_extract(json_value,'$.col-50') as col50 FROM a All SELECT requests will then run