12

Okay, I'll cop to it: I'm a better coder than I am at databases, and I'm wondering where thoughts on "best practices" lie on the subject of doing "simple" calculations in the SQL query vs. in the code, such as this MySQL example (I didn't write it, I just have to maintain it!) -- This returns the username, and the users age as of the last event.

SELECT u.username as user, 
       IF ((DAY(max(e.date)) - DAY(u.DOB)) < 0 ,   
       TRUNCATE(((((YEAR(max(e.date))*12)+MONTH(max(e.date)))
       -((YEAR(u.DOB)*12)+MONTH(u.DOB)))-1)/12, 0),  
       TRUNCATE((((YEAR(max(e.date))*12)+MONTH(max(e.date))) -            
       ((YEAR(u.DOB)*12)+MONTH(u.DOB)))/12, 0)) AS age   
FROM users as u
JOIN events as e ON u.id = e.uid
...

Compared to doing the "heavy" lifting in code:

Query:

SELECT u.username, u.DOB as dob, e.event_date as edate
FROM users as u
JOIN events as e ON u.id = e.uid

code:

function ageAsOfDate($birth, $aod)
{    //expects dates in mysql Y-m-d format...
     list($by,$bm,$bd) = explode('-',$birth);
     list($ay,$am,$ad) = explode('-',$aod);

     //Insert Calculations here 
     ...
     return $Dy; //Difference in years
}

echo "Hey! ". $row['user'] ." was ". ageAsOfDate($row['dob'], $row['edate']) . " when we last saw him."; 

I'm pretty sure in a simple case like this it wouldn't make much difference (other than the creeping feeling of horror when I have to make changes to queries like the first one), but I think it makes it clearer what I'm looking for.

Thanks!

3
  • 1
    This is a good question - I have come across the same issue.
    – Michael K
    Nov 16, 2010 at 17:08
  • Here's a good example of when not to do it: calendar.sql (Yes, that is my monstrosity, yes, it was a bad idea, and no, it isn't slow.)
    – greyfade
    Nov 16, 2010 at 17:42
  • Ye flipping gods... I bet the MD5 for that thing comes out to be "CthulhuFhtagn" Nov 16, 2010 at 17:46

5 Answers 5

13

You want to do all set-based operations in the database for performance reasons. So aggregation functions, sorting functions, joins etc.

This age calculation, I'd do in code. The only reason I might ever do something like this in a database query is if it required lots of columns that I wouldn't otherwise select that could actually amount to enough data to meaningfully slow down my query. Selecting a few integer values will not make a meaningful performance difference. And even if it makes a moderate performance difference I will be biased towards keeping this logic in the application code.

1
  • I agree. Code that fiddles with values for display purposes should be in your app code.
    – TehShrike
    Jan 1, 2012 at 7:46
4

Each case is different

Is the logic...

  • needed by other clients? DRY: in the database
  • used for further processing? eg sort by age descending: in the database
  • requires regional settings? dd/mm/yyyy or mm/dd/yyyy: in the client
  • used often? Why calculate it again and again: use computed and persisted column in the database

In this case, I might use a computed and persisted column in the database

It could be worse: you could have this in the database:

"Hey! ". u.username." was ". <datecalc>. " when we last saw him."
3

Basically you should look at two things: CPU usage and network traffic. You should not generate enormeous responses, transfer them over the network and then summarize in the frontend, as the database can do this much better.

For data manipulation it is a trade-of. If the database spends comparable amount of cpu cycles to your frontend code doing the same thing - given that the amount of data transferred is roughly equivalent), then it doesn't matter where. Then do it where you have the largest amount of programming expertise. Frequently, you can get a VERY long way with a careful select and that might be very useful.

1

You mentioned one: area of expertise. Maybe the structure of the database isn't too intensive, so you decide to offload some of the logic development to a team member that is more database centric. May not be ideal, but if you're crunched for time...

The database hardware has significantly more resources than other servers and you can't change this. This may not apply to this specific situation, but may need to be considered.

There are other applications that may need the logic outside of your code. Some report writing tools may not be able to utilize a web service or an API. You could duplicate the logic or if you feel the requirements may diverge.

6
  • "The database hardware has significantly more resources than other servers and you can't change this." -- eh? Where did those two statements come from? Nov 16, 2010 at 17:35
  • I think Jeff might be talking about standalone Database servers. I probably should have specified that I work mostly on LA[MP]P setups. Nov 16, 2010 at 17:40
  • 1
    A LAMP setup is no reason not to have a standalone database server, and nor is a standalone database server a guarantee of more resources nor not being able to change this. Nov 16, 2010 at 17:52
  • Hrm. Not sure then. Nov 16, 2010 at 18:21
  • @Peter Boughton, DB and app in the same server have order of magnitude less time for interface connection and magnitudes greater IO throughout, there are real reasons to locate these two together.
    – Jé Queue
    Dec 28, 2010 at 19:07
0

I always err on putting as much processing at the DB. Your syntax above could also be written with DB functions that would be IMO a very clean solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.