Not exactly since aggregates are implemented as functions. In the case of sum(a + b + c) you have the overhead of one aggregate setup call, one call per row (after the three additions are done) and one finalizer call to retrieve the aggregate and release the context.
In the case of sum(a) + sum(b) + sum(c) you have three initializer calls being made to set up three different aggregate contexts. Then on each row you call the increment function three times for three different contexts, then after the aggregate is complete you make three calls to finalize the three aggregates and release their contexts, then add up the sum. The number of additions is the same, but the latter (multiplicity of aggregate contexts) adds significantly to the size of the code path. This may be on the order of only a couple thousand instructions per row, but it is a couple *more* thousands of instructions per row than the former sum(a + b + c) case. This will not be significant where you are dealing with 10 rows, but when you have thousands or millions of rows it is quite significant. It will also use more energy and concomitantly increase the temperature of the CPU, thus contributing to global warming. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of R.Smith >Sent: Monday, 9 February, 2015 04:51 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] ordinary CTE containing sum() > > >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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
