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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

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 against the view.

This view works and of course takes some time to build. Can the JSON1 extension be supported by adding indices or whatever, that speeds-up the VIEW construction/updates?

Viele Grüsse.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3


On 19 Jun 2017, at 2:50pm, Robert M. Münch <[hidden email]> 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, ... , json_extract(json_value,'$.col-50') as col50 FROM a
>
>
> All SELECT requests will then run against the view.
>
> This view works and of course takes some time to build.

Please supply a same SELECT command that you would use this VIEW for.  I’m especially interested in clauses such as GROUP BY, ORDER BY and WHERE.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Jens Alfke-2
In reply to this post by Robert M. Münch

> On Jun 19, 2017, at 6:50 AM, Robert M. Münch <[hidden email]> 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 views are simply macros that transform the statements that use them. (Correct me if I’m wrong; I haven’t actually used views.)

> Can the JSON1 extension be supported by adding indices or whatever, that speeds-up the VIEW construction/updates?

You can create indexes to support JSON1 queries by using the same json_xx function calls in a CREATE INDEX statement.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3


On 20 Jun 2017, at 1:34am, Jens Alfke <[hidden email]> 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 haven’t actually used views.)

You’re right.  When you create a VIEW SQLite stores the CREATE VIEW command, and not the data it retrieves.  When the VIEW is used in a later command SQLite integrates the two commands.

> 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.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

wmertens
On Tue, Jun 20, 2017 at 2:43 AM Simon Slavin <[hidden email]> wrote:

> On 20 Jun 2017, at 1:34am, Jens Alfke <[hidden email]> 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.
>
>
This works great, I use it all the time. As long as you use the exact same
calculated statement in the index as in the query, the index will be used
instead of calculating the value.

Wout.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Jens Alfke-2
In reply to this post by Simon Slavin-3

> On Jun 19, 2017, at 5:43 PM, Simon Slavin <[hidden email]> 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 primary use case driving the extension of CREATE INDEX to take an expression as well as a column name.

This makes querying JSON workable in practice, and it can be used for other purposes too. For example, you could register a function that extracts image metadata from EXIF tags, and then be able to efficiently index and query that metadata on JPEG images stored in blob columns.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Robert M. Münch
In reply to this post by Simon Slavin-3
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 this VIEW for. I’m especially interested in clauses such as GROUP BY, ORDER BY and WHERE.

The SELECTs we use don't have a GROUP BY, the ORDER BY will only be on one column and we mostly return all columns, sometimes a subset.

The WHERE parts look like this:

... WHERE col-1 <> NULL AND col-2 <> NULL ... AND col-x <> NULL

... WHERE col-x LIKE ...

and so on. So mostly pretty simple queries. No JOINS etc. We have about 50.000 records and maybe at max 50 columns.

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Robert M. Münch
In reply to this post by Jens Alfke-2
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 haven’t actually used views.)

Hi, yes, that's right. That's why it makes sense to speed up VIEW execution as it's run dynamically when views are used.


> You can create indexes to support JSON1 queries by using the same json_xx function calls in a CREATE INDEX statement.

Ah, cool.

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 column for future expansion. I think that could speed things up. What do you think?

--

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (544 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Simon Slavin-3
In reply to this post by Robert M. Münch


On 20 Jun 2017, at 8:46am, Robert M. Münch <[hidden email]> 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 command and that the JSON itself isn’t a problem.  You might be able to speed up your command by making indexes which allow SQLite to identify the null columns very quickly.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Jens Alfke-2
In reply to this post by Robert M. Münch

> On Jun 20, 2017, at 12:48 AM, Robert M. Münch <[hidden email]> 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 column for future expansion. I think that could speed things up. What do you think?

Creating indexes on the JSON properties is the big optimization. What you’re proposing above will add a little extra performance, but I don’t think it’ll be that much (basically you’d just save the overhead of reading the property out of the JSON at the time the index is updated and when the query runs.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...