On 2/8/2015 10:23 PM, James K. Lowden wrote:
I have a couple of efficiency questions for those who know:
1. Is the left-join on a CTE apt to be more effecient than the version
that uses a correlated subquery in the SELECT clause?
I'm guessing it matters in some DBs but from testing it seems much the
same in SQLite.
2. Is there any performance difference between
sum(ca1 +ca2 + exam)
and
sum(ca1) + sum(ca2) + sum(exam)
I would expect the left join is faster than a correlated subquery, and
that fewer aggregates is better than more.
Now this is easy to check but the answer is simple too - I know it looks
in SQL terms like something more complicated is taking place, but in
reality it's all the same, consider that it is just like asking which of
these are faster:
(1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9)
-- OR --
(1 + 2 + 3) + (4 + 5 + 6) + (7 + 8 + 9)
Count the plusses, they are the same. The difference to the
processor/engine (ultimately) of your two queries are merely order of
adding, but no difference to addition operations or amount of function
calls. (Unless "adding" by itself is a significantly different/slower
operation when done inside the aggregate function than outside it, but
that would fit somewhere between devious and insane).
IF you could somehow get rid of the loop or change the compound
iteration count it might have a viable effect, but that is not the case
here.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
