SQLite order of operations in a query

Question

I have a query of the form:

SELECT akey, avalue, expensiveop(akey) FROM atable WHERE avalue < SOME_CONSTANT;

It appears that expensiveop()--which is a user-defined function that takes a fair amount of time to execute--gets called for all rows in the table. For performance reasons I only want it to execute once for each row in the result set.

I tried the following approach, which did not seem to make a difference:

SELECT akey, avalue, expensiveop(akey) FROM (SELECT * FROM atable WHERE avalue < SOME_CONSTANT)

Any ideas?

See *9.0 Subquery flattening* at http://www.sqlite.org/optoverview.html (and search for CROSS JOIN at the same page for ideas) — biziclop, Jan 08 '13 at 18:25

score 0 · Answer 1 · answered Jan 08 '13 at 18:23

0

An idea for a workaround:

SELECT akey, avalue, CASE avalue < SOME_CONSTANT WHEN 0 THEN NULL ELSE expensiveop(akey) END FROM avalue WHERE avalue < SOME_CONSTANT

answered Jan 08 '13 at 18:23

Anton Kovalenko

20,999
2
37
69

score 0 · Answer 2 · answered Jan 08 '13 at 18:38

Another idea is:

SELECT akey, avalue, expensiveop(akey)
FROM atable
WHERE avalue < SOME_CONSTANT
group by akey, avalue

SQL should execute the where and group by before doing the expensive operation, because it is using the aggregated "akey". If the query had something like min(expensiveop(akey)) then it would be performing it for every row.

biziclop · Accepted Answer · 2013-01-08T19:11:55.390

0

You can read about SQLite's optimizer at this page, especially the Subquery flattening part: http://www.sqlite.org/optoverview.html#flattening

It contains a list of 19 conditions all of which must be met to allow subquery flattening to happen. Choose one, and do the exact oppposite it says, for example some random examples (untested):

 SELECT akey, avalue, expensiveop(akey)
 FROM (SELECT * FROM atable WHERE avalue < SOME_CONSTANT LIMIT 99999999)
 LIMIT 99999999

 SELECT akey, avalue, expensiveop(akey) FROM
(SELECT * FROM atable WHERE avalue < SOME_CONSTANT
 UNION
 SELECT * FROM atable WHERE 0 GROUP BY avalue)

 SELECT akey, avalue, expensiveop(akey) FROM
(SELECT * FROM atable WHERE avalue < SOME_CONSTANT LIMIT -1 OFFSET 0)

edited Jan 08 '13 at 19:11

answered Jan 08 '13 at 18:39

biziclop

14,466
3
49
65

I like the 3rd one, but I think the syntax is invalid. I think the end of the subquery needs to be "LIMIT -1 OFFSET 0" – laslowh Jan 08 '13 at 18:51
indeed, OFFSET requires LIMIT: http://www.sqlite.org/lang_select.html I don't know if SQLite optimizes out no limit + offset 0 or not. – biziclop Jan 08 '13 at 19:10
It appears to have the effect that I'm looking for, at least in 3.7.9. I could see that changing in the future, however. – laslowh Jan 08 '13 at 19:36
If nothing helps, then you might send a bug report to sqlite: http://www.sqlite.org/src/wiki?name=Bug+Reports , maybe they can give you a useful answer. – biziclop Jan 09 '13 at 10:32

SQLite order of operations in a query

3 Answers3