Anytime you're working with large result sets the database is going to be more efficient. Using the middle tier makes a lot of sense in some cases, but putting that data into code is in some ways throwing away the very thing DBs are good at -- handling large sets of data. For instance, an app I've worked on needed to calculate sums of previously paid quantities (along with a bunch of other stuff), for a few thousand records at a time. Doing it row by row took more than 10 minutes (on a dual xeon with 6gb of ram). By switching the calculations to be based on the results of a join, it went down to 10-15 seconds. I think this is a decent illustration of a case where taking advantage of the data layer was really the right thing to do, rather than building and tearing down 50,000 individual database hits (even with connection pooling) from the middle tier. Every app is different, and so there's never going to be a rule of middle tier vs data tier. DISCLAIMER: I'm a RoR newbie who's been lurking on the list. If I said something that doesn't hold true for RoR, I apologize in advance, and please ignore me :)
on 2006-03-17 21:13
on 2006-03-17 21:35
You've got a valid point here making it a good example. The way we handled this challenge though, was to issue a batch process performing a bulk transaction on the database server. We didn't construct thousands of objects and perform summations on them. On your other point, how many web apps have you seen where you grab large chuncks of data and send it to the client? Wouldn't you paginate it instead?