Should I put this filtering in SQL or my application code?

Question

Assume I have the following models in my web Application:

Table User:
   (attr1, attr2, attr3, ...)
Table Prize:
   (condition1, condition2, condition3, prize_val)

The application logic is: if a user satisfies all the conditions of a Prize, I want to grant him the prize. The condition could either be NULL (True for all users) or a specific value. Every condition can be computed with the user attributes. I can do the filtering in 2 ways:

1. Get all the prize rules from database (at most 100), and iterate the rules in my application code, checking if the current user satisfies the rule, to get a prize list.

2. User SQL to do the filtering like this:

   SELECT prize from Prize where (condition1=NULL or condition1=user_condition1) and (condition2=NULL or condition2=user_condition2) ...

My question is: which one is more efficient?

And a more general question is: when is it better to do filtering in application code, instead of SQL?

PS. The reason I even think about iterations in code is this: If I iterate in code, and condition1 is NULL for a prize, I don't need to compute the condition1 value for the user (this computation can be expensive); But If I take the SQL approach, I have to pre-compute every condition value for the user.

Answer 1

Rule of thumb: SQL Query is always more efficient when comparing with iterations in the code.

About filtering - when you filter on SQL it will return less data than if you filter in the App. Also I think that filter in the query is faster than filter in the code.

Answer 2

1. You have a matrix of conditions with prize in each row.

2. The condition values can change over time along with prize value

So, it is advisable to keep in database. Data should be in database and logic should be in code. In your case the conditions are providing data, which changes. But logic remains constant.

Hope I'm clear.

Answer 3

Your Prize table is not normalized. I see a one-to-many relationship with Condition.

When you do that your filter is a Join instead of ever-more complex WHERE clause.

In either case I think this is best done by the database. You need to take care that you deal with primary keys. Your queries will perform badly if they aren't indexed properly.