Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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!

share|improve this question
1  
This is a good question - I have come across the same issue. –  Michael K Nov 16 '10 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 '10 at 17:42
    
Ye flipping gods... I bet the MD5 for that thing comes out to be "CthulhuFhtagn" –  GeminiDomino Nov 16 '10 at 17:46
add comment

5 Answers

up vote 11 down vote accepted

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.

share|improve this answer
    
I agree. Code that fiddles with values for display purposes should be in your app code. –  TehShrike Jan 1 '12 at 7:46
add comment

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."
share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
"The database hardware has significantly more resources than other servers and you can't change this." -- eh? Where did those two statements come from? –  Peter Boughton Nov 16 '10 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. –  GeminiDomino Nov 16 '10 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. –  Peter Boughton Nov 16 '10 at 17:52
    
Hrm. Not sure then. –  GeminiDomino Nov 16 '10 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. –  Xepoch Dec 28 '10 at 19:07
show 1 more comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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