Multiple SQL searches vs searching through one returned array

Question

Is it faster to do multiple SQL finds on one table with different conditions or to get all the items from the table in an array and then separate them out that way? I realize I'm not explaining my question that well, so here is an example:

I want to pull records on posts and display them in categories based on when they were posted, say within one year, within one month, one week, etc. The nature of the categories results in lower level categories being entirely contained within upper level ones.

Should I do a SQL find with different conditions for each category, resulting in multiple calls to the database, or should I do one search, returning all of the items and then sort them out from the array? Thanks for your responses, sorry I'm new at this.

Answer 1

Typically I would say that you are going to get better performance by letting your database engine do the sorting work. Each database engine has this functionality and typically it can do it faster than you can.

So I would vote to use the database to get your multiple groups rather than trying to do it yourself in memory.

Answer 2

I typically perform one large sql query and then break the array up in ruby to minimize the number or duration of database connections.

This isn't necessarily any faster, and I have never benchmarked it, but less reads to the db hopefully means it will scale longer.

Answer 3

Edit: Nevermind, I didn't quite understand the question. Just let SQL perform the ordering for you in a convenient fashion and then process the array yourself.

You can probably make it even easier if you let your SELECT statement generate helper columns to say which categories (e.g., based on the date) a record belongs to.

Answer 4

The simplest, and easiest to understand would be to perform multiple queries for each criteria, and then form each of those result sets into a group. I don't think you want to start traversing result sets and duplicating rows.

If you really want to do it in one query, you could try a UNION query.

SELECT *, 1 as group from posts WHERE date > '2009-07-24 00:00:00' ORDER BY date DESC 
UNION ALL 
SELECT *, 2 as group from posts WHERE date > '2009-07-17 00:00:00' ORDER BY date DESC 
UNION ALL 
SELECT *, 3 as group from posts WHERE date > '2009-06-24 00:00:00' ORDER BY date DESC 
UNION ALL 
SELECT *, 4 as group from posts WHERE date > '2008-07-24 00:00:00' ORDER BY date DESC

After that you just need to traverse the list once, and filter into new lists by the "group" column.

Answer 5

It depends. If you're using OR operators in your procedures, then things could get kind of slow. It would be better at that point to use multiple SQL statements.

But really, you need to analyze the query plans and decide for yourself if it is efficient enough or not. Run real world examples and TEST TEST TEST.