Re: [sqlite] Preventing a slow, determinstic function from running twice

[Rob Hoelz]

On Fri, 5 Jan 2018 14:42:00 -0600
Rob Hoelz  wrote:

> On Fri, 5 Jan 2018 15:04:16 -0500
> Richard Hipp  wrote:
> 
> > On 1/5/18, r...@hoelz.ro  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
> > > sqlite-users@mailinglists.sqlite.org
> > > 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
> sqlite-users@mailinglists.sqlite.org
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Preventing a slow, determinstic function from running twice

[Rob Hoelz]

On Fri, 5 Jan 2018 12:02:21 -0800
Jens Alfke  wrote:

> > On Jan 5, 2018, at 11:25 AM, r...@hoelz.ro 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
> sqlite-users@mailinglists.sqlite.org
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Preventing a slow, determinstic function from running twice

[Rob Hoelz]

On Fri, 5 Jan 2018 15:04:16 -0500
Richard Hipp  wrote:

> On 1/5/18, r...@hoelz.ro  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
> > sqlite-users@mailinglists.sqlite.org
> > 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Preventing a slow, determinstic function from running twice

[Richard Hipp]

On 1/5/18, r...@hoelz.ro  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Preventing a slow, determinstic function from running twice

[Jens Alfke]

> On Jan 5, 2018, at 11:25 AM, r...@hoelz.ro 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Preventing a slow, determinstic function from running twice

[r...@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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users