Michael P. wrote in post #960385:
On 9 November 2010 18:46, Marnen Laibow-Koser [email protected]
wrote:
However, here’s a rule of thumb: never use Ruby for things like a
simple count of DB records. Let the DB do the things that it’s good at.
Doing a count of DB records on the Ruby side? I wouldn’t dignify that
with the term “personal preference”.
As a thought-experiment, how would you handle several counts in a row
on a rapidly changing table for some form of end-user report?
If I do one SQL query for a count by one set of criteria, and then do
another query for a similar query, but just grouped differently, I may
get different numbers because the underlying records in the table have
changed.
That’s what transactions are for – guaranteeing atomicity and
consistent state over multiple queries. This is a perfect use case for
them:
User.transaction do
mikes = User.count :conditions => {:name => ‘michael’}
active = User.count :conditions => {:active => true}
end
If I do the same thing in code; I do one DB query, and then use the
Ruby/Rails methods to manipulate that data how I need, and I can
always be assured that I am at least always operating on the set
same records for that request.
But you’re doing far too big a query. You’re asking for (let’s say)
thousands of records, which Ruby will then parse in memory.
If you do the count on the DB side, then the DB will be able to use
whatever indices and performance hacks are available to it to do the
counting more quickly – and yes, more maintainably – then your Ruby
application could.
This is the kind of situation that I have a “personal preference” to
do in code, not in the DB (although the DB may be better at it,
there’s a chance my numbers won’t be the same which will confuse
users).
Then your preference is, I think, poorly founded, as it does not take
the facts of the situation fully into account, and does not take full
advantage of the power of the DB.
Do you disagree with my reasoning? If so, why?
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
if the reader is Michael.
if the reader is Michael.
It also increases the count of slapped foreheads. 