On Thu, Nov 13, 2008 at 06:39:47PM -0800, Eus wrote:
> Isn't that something like this is better handled at the application level instead of the DB level?
>
> IOW, isn't that the cost of doing the query above far more expensive than doing a little coding at the application
level?
That's something you'll have to test to really find out, but keep in
mind the database has several advantages over the application in the
general case. First, the database has indexes (presumably), and the
application would just have a raw dataset. The dataset might be
delivered in sorted order if you query it right, which might obviate
that advantage. Or it might not. Second, the database has all the data
locally. If your application lives on some other server, the data
returned by your initial query needs to be streamed across a network,
and converted to data types your application language understands. This
makes a particularly noticeable difference when the result data set is
much smaller, due to filtering in the query, than the original tables.
Other considerations depend on your definition of "expensive". Getting
the programming right to do in application code what you could do in one
query might be a fair bit of work. There has been quite a lot of effort
spent on the database to make sure it performs correctly and
efficiently; probably more so than has been spent on the code you're
writing to replace the query. That said, the database is designed to
support generic operations, and your code might exploit some
characteristic of the data the database can't know about to achieve
better results. In general, it's probably best to let the database
handle data, and let your application handle "business logic" that
transcends the data. Interpretations of the above vary from person to
person :)
All that said, my first guess is that you're better off writing the
query than doing the processing in the application.
- Josh / eggyknap