Preventing a slow, determinstic function from running twice

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Preventing a slow, determinstic function from running twice

rob@hoelz.ro
Hi SQLite users and devs,

I have an application using SQLite which stores XZ-compressed blobs of JSON
from the Twitter API to minimize disk usage.  My unxz function is a little
slow, and I've noticed that if I specify the function several times in a
query, it gets run multiple times, even though it's deterministic.  For
example:



In the above query, unxz is run three times, even though content doesn't
change within the same row.  Is there a way to tell SQLite to only run a
function once per row of results?  I looked into
https://sqlite.org/c3ref/get_auxdata.html, but it appears that metadata is
only stored for compile-time constants.

Thanks,
Rob




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Preventing a slow, determinstic function from running twice

Jens Alfke-2


> On Jan 5, 2018, at 11:25 AM, [hidden email] wrote:
>
> … My unxz function is a little slow, and I've noticed that if I specify the function several times in a query, it gets run multiple times, even though it's deterministic. …
> Is there a way to tell SQLite to only run a function once per row of results?

Not currently. There was a long-ish email thread about that here last September, with subject "Common subexpression optimization of deterministic functions”. I don’t think there was any resolution, just that it’s an optimization that hasn’t been implemented yet.

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

Re: Preventing a slow, determinstic function from running twice

Richard Hipp-3
In reply to this post by rob@hoelz.ro
On 1/5/18, [hidden email] <[hidden email]> wrote:
> Hi SQLite users and devs,
>
> I have an application using SQLite which stores XZ-compressed blobs of JSON
> from the Twitter API to minimize disk usage.  My unxz function is a little
> slow, and I've noticed that if I specify the function several times in a
> query, it gets run multiple times, even though it's deterministic.  For
> example:
>

Your example did not go through.  Please resend.  Use plain-text email
for best results.

>
>
> In the above query, unxz is run three times, even though content doesn't
> change within the same row.  Is there a way to tell SQLite to only run a
> function once per row of results?  I looked into
> https://sqlite.org/c3ref/get_auxdata.html, but it appears that metadata is
> only stored for compile-time constants.
>
> Thanks,
> Rob
>
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Preventing a slow, determinstic function from running twice

rob@hoelz.ro
On Fri, 5 Jan 2018 15:04:16 -0500
Richard Hipp <[hidden email]> wrote:

> On 1/5/18, [hidden email] <[hidden email]> wrote:
> > Hi SQLite users and devs,
> >
> > I have an application using SQLite which stores XZ-compressed blobs
> > of JSON from the Twitter API to minimize disk usage.  My unxz
> > function is a little slow, and I've noticed that if I specify the
> > function several times in a query, it gets run multiple times, even
> > though it's deterministic.  For example:
> >
>
> Your example did not go through.  Please resend.  Use plain-text email
> for best results.
>
> >
> >
> > In the above query, unxz is run three times, even though content
> > doesn't change within the same row.  Is there a way to tell SQLite
> > to only run a function once per row of results?  I looked into
> > https://sqlite.org/c3ref/get_auxdata.html, but it appears that
> > metadata is only stored for compile-time constants.
> >
> > Thanks,
> > Rob
> >
> >
> >
> >
> > --
> > Sent from: http://sqlite.1065341.n5.nabble.com/
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>


Here's the example:

> select json_ref(unxz(content), '$.user.id'), json_ref(unxz(content),
> '$.full_text) from archive where json_ref(unxz(content),
> '$.full_text') like '%dogs%';
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Preventing a slow, determinstic function from running twice

rob@hoelz.ro
In reply to this post by Jens Alfke-2
On Fri, 5 Jan 2018 12:02:21 -0800
Jens Alfke <[hidden email]> wrote:

> > On Jan 5, 2018, at 11:25 AM, [hidden email] wrote:
> >
> > … My unxz function is a little slow, and I've noticed that if I
> > specify the function several times in a query, it gets run multiple
> > times, even though it's deterministic. … Is there a way to tell
> > SQLite to only run a function once per row of results?
>
> Not currently. There was a long-ish email thread about that here last
> September, with subject "Common subexpression optimization of
> deterministic functions”. I don’t think there was any resolution,
> just that it’s an optimization that hasn’t been implemented yet.
>
> —Jens
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Thanks for letting me know about the thread, Jens - I did a cursory search of the archives but I must've been using the wrong keywords!  I'll read up on that.

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

Re: Preventing a slow, determinstic function from running twice

rob@hoelz.ro
In reply to this post by rob@hoelz.ro
On Fri, 5 Jan 2018 14:42:00 -0600
Rob Hoelz <[hidden email]> wrote:

> On Fri, 5 Jan 2018 15:04:16 -0500
> Richard Hipp <[hidden email]> wrote:
>
> > On 1/5/18, [hidden email] <[hidden email]> wrote:
> > > Hi SQLite users and devs,
> > >
> > > I have an application using SQLite which stores XZ-compressed
> > > blobs of JSON from the Twitter API to minimize disk usage.  My
> > > unxz function is a little slow, and I've noticed that if I
> > > specify the function several times in a query, it gets run
> > > multiple times, even though it's deterministic.  For example:
> > >
> >
> > Your example did not go through.  Please resend.  Use plain-text
> > email for best results.
> >
> > >
> > >
> > > In the above query, unxz is run three times, even though content
> > > doesn't change within the same row.  Is there a way to tell SQLite
> > > to only run a function once per row of results?  I looked into
> > > https://sqlite.org/c3ref/get_auxdata.html, but it appears that
> > > metadata is only stored for compile-time constants.
> > >
> > > Thanks,
> > > Rob
> > >
> > >
> > >
> > >
> > > --
> > > Sent from: http://sqlite.1065341.n5.nabble.com/
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
>
>
> Here's the example:
>
> > select json_ref(unxz(content), '$.user.id'), json_ref(unxz(content),
> > '$.full_text) from archive where json_ref(unxz(content),
> > '$.full_text') like '%dogs%';
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Sorry - I just noticed that I wrote "json_ref" where I meant to write "json_extract".
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users