Keith,
No SQL format can guarantee anything. And changing SQL format won't change
its execution plan - SQL optimizer will decide that for you. And I don't
know where did you take your information from but I can guarantee you that
with your query SQLite will execute strftime many times and OP's problem
will persist.
Pavel
On Jul 22, 2012 2:24 PM, "Keith Medcalf" <kmedc...@dessus.com> wrote:
> select a.name, a.assignedTo, a.validFrom
> from aliases a,
> (select name, max(validfrom) as mvf
> from aliases,
> (select strftime('%s', 'now') as now) as c
> where validFrom <= now
> group by name) as b
> where a.name=b.name and a.validfrom=b.mvf;
>
> should also work. I changed the syntax slightly:
> from a join b join c on a.x=b.y and b.z=c.p is just a different spelling
> of from a, b, c where a.x=b.y and b.z=c.p
> designed to make it clear (COBOL style) which clauses are join columns
> (ON) and which are row selectors (WHERE). It should have zero effect on
> the actually query plan.
>
> Effectively, creates a temp table with a single row containing a single
> value now, which is used as a selector against the rows of aliases. This
> format should guarantee that the strftime function is only ever executed
> once.
>
> sqlite> explain query plan select a.name, a.assignedTo, a.validFrom
> ...> from aliases a,
> ...> (select name, max(validfrom) as mvf
> ...> from aliases,
> ...> (select strftime('%s', 'now') as now) as c
> ...> where validFrom <= now
> ...> group by name) as b
> ...> where a.name=b.name and a.validfrom=b.mvf;
> SELECT item[0] = {0:0}
> item[1] = {0:1}
> item[2] = {0:2}
> FROM {0,*} = aliases (AS a)
> {1,*} = SELECT agg_flag
> item[0] = AGG{2:0}
> item[1] = AGG_FUNCTION:max(AGG{2:2})
> FROM {2,*} = aliases
> {3,*} = SELECT FUNCTION:strftime(item[0] = '%s'
> item[1] = 'now')
> END (tabname=sqlite_subquery_53E850_) (AS c)
> WHERE LE({2:2},{3:0})
> GROUPBY {2:0}
> END (tabname=sqlite_subquery_53E4D0_) (AS b)
> WHERE AND(EQ({0:0},{1:0}),EQ({0:2},{1:1}))
> END
> sele order from deta
> ---- ------------- ---- ----
> 1 0 1 SCAN SUBQUERY 2 AS c (~1 rows)
> 1 1 0 SCAN TABLE aliases (~333333 rows)
> 1 0 0 USE TEMP B-TREE FOR GROUP BY
> 0 0 1 SCAN SUBQUERY 1 AS b (~100 rows)
> 0 1 0 SEARCH TABLE aliases AS a USING AUTOMATIC
> COVERING INDEX (name=? AND validFrom=?) (~7 rows)
> sqlite>
>
> ---
> () ascii ribbon campaign against html e-mail
> /\ www.asciiribbon.org
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
