Search code examples
javasqldatabaseappserver

Should you add logic to application server or database

Assume I have 5 stores in 5 different cities. I want to find out how much I earned per day per city for this month.

I have 2 options.

1) Logic on the App server:

for (each city)
  for (each day) {
      Query(city, day);
  }
}

This would result in city * day queries.

2) Logic on DB server: Add a single complicated aggregation query.

Which of the two options is preferred? Looking for pros and cons of both ? Which one appears more readable?

Solution

  • It doesn't make any sense to query the db multiple times for the data from the same table (as I understand that it would look like) and process results yourself when database can do this for you in one go. You would be putting unnecessary stress on the network, db and your application.