[cal85]

> even on a local SQLite DB there is likely some non zero benefit to minimising the amount of data retrieved.

Why though? I get why it can be a big perf win to push that kind of logic into a remote DB - fewer and smaller payloads being sent over the network. But what makes you say there is likely a non-zero benefit even with local SQLite? (I don’t really have a clear mental model of how embedded SQLite works, so you may well be right, I just want to understand why.)

[frollogaston]

First thing that comes to mind is you've got a complex query with some of these UDFs involved in the middle, rather than just transforming the end result. Doing the equivalent without UDFs would be an entirely different query with a different plan, or more likely you'd have to split into separate queries.

[sgarland]

Because I/O isn’t free? If you can write code that does fewer things, it will be faster.

[intalentive]

If you have to pull the data into your application then it's all in memory at once. SQLite streams from disk, so the memory usage at any given time is less. Also, if the application language is slower than C, then you get a performance boost there as well.