For those interested in the initial "misuse of aggregate" issue of this thread, there is now a ticket:
http://www.sqlite.org/src/tktview?name=2f7170d73b e On Mon, Feb 9, 2015 at 9:19 AM, Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
