On Mon, Feb 13, 2012 at 12:28 PM, Simon Slavin <[email protected]> wrote: > On 13 Feb 2012, at 7:51pm, Steinar Midtskogen wrote: >> One should think that the ability to calculate a moving average would >> be a pretty common request. But people do it in their application >> code instead? > > Actually, my expectation is the other way. I'm continually > surprised by posts on this list that people expect to be able > to use SQLite with no external programming at all. I often see > complicated compound JOIN and sub-SELECT SQL commands here > which can be replaced by four lines in any programming > language, yielding faster simpler code which would be easier to > document and debug.
More generally, I *often* see good C++ programmers pushing application logic into SQL, and getting it wrong because they don't really know SQL. As a result, my rule of thumb is to make the separation between application code and SQL code based on whether the code is filtering result sets. If the code reduces the number of results, it probably should be in the SQL, but if it just processes the results without removing any rows, it should probably be in application code. In a client/server system like MySQL, it can sometimes be worthwhile to additionally have the server do operations which reduce the physical size of the individual results (say taking a substring of a column), but for SQLite that is seldom worthwhile (application code can make better targeted decisions about memory allocation and the like). Since SQLite is running in-process, communication is cheap, so it is also often the case that there is no particular performance gain from having SQLite do calculations rather than your application code. SQLite can probably optimize SELECT COUNT(x) better than your application code can, but SELECT MAX(x) would probably be about the same performance if implemented in application code. -scott _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
