Best approach is to filter data in the database or application?

Question

Suppose we have a table 'Vendas' with the following columns 'estado', 'cidade', 'vendedor', 'valor'.

'estado' and the 'cidade' would be where the sale was held and 'vendedor' would be what made this sale. 'valor' would be the value of the sale.

What’s the best, cheapest, smartest approach to accumulating these values by 'estado' or 'cidade' or 'vendedor'?

Make 3 selects:

sql = "SELECT estado, sum(valor) 
       FROM vendas
       GROUP BY estado";

sql = "SELECT cidade, sum(valor) 
       FROM vendas
       GROUP BY cidade";

sql = "SELECT vendedor, sum(valor) 
       FROM vendas
       GROUP BY vendedor";

or

Make 1 select only and do the calculations with the programming language

sql = "SELECT * FROM vendas";

Leave it up to the programming language (in this case I work with Java) to do these calculations, and the programmer’s manual work would be heavier?

Answer 1

Let the database work for you.

Not that this is an absolute truth. But bringing a lot of data to the filtering application is almost always a mistake. I say it almost because there can always be a scenario that I don’t know can be useful or the only viable way. But I can remember one like that in my head.

Bringing in too much data costs the database, infrastructure and application, not to mention that you’ll have an extra code to deal with.

This case is clearly best to use full SQL and bring only what you need. See if you can make the three queries in one query only. Probably can’t, because the most used technologies hinder this, but it depends a little on how everything is organized.

There may be some very specific and odd case that it would be better to bring everything, even then it would be little gain and I find something so out of the curve that I doubt it would actually happen.

Of course you need to architecture the application to work well, you need to see if the database is properly configured to support this query adequately.

There are controversies as to which limit should be passed to the database. Many scalability problems occur because the developer overloads the database with things that would look better in the application. There will always be the discussion on the use of Stored Procedures or not.

I say and I repeat, many of the techniques and tools that exist today were created to solve a problem created by another technique that was unnecessary or worse than simple. Often people ignore the Razor of Occam. Of course there will always be the argument that the more complex is doing something more. There are cases that are true, but there are cases that are just complicated by not evaluating the whole, or by liking it, or just knowing how to do it.

The "good" part is that it created a huge industry because of so much new problem. We look like hamsters on the wheel.

Related: Why using "SELECT * FROM table" is bad?.

Answer 2

I think that using both SELECT * FROM sales and the 3 SELECT’s very strange, it might be better to make a table that organizes at least one column, like the seller’s name, for example. Then I’d be like this:

sql = "SELECT * FROM vendas
       GROUP BY vendedor";

the "seller" up there can be replaced by city or state

Answer 3

I’m sorry I disagree with your friend Maniero as to "Let the database work for you...".

In my view, decoupling the application of the technology adopted in the database is always the best strategy, as it delivers portability and facilitates migration between database versions. In addition, a programmer who "lets the bank work" must also be a bank AD or DBA, otherwise he or she will be misusing the BD and the "spell can turn against the wizard". If the application is Java, whose JPA standards are already much more than mature, it is much better to hand over the responsibility for building the 'selects' to Querybuilder and let it take into account all the details such as CHARSET and Dialect.

Answer 4

Each case is a case.

I believe you are saying that, in the interface, the user will be able to select the view of a sales report, with option by city, state or seller.

The past example uses SQL ANSI, which runs on which database you want. It makes no sense to use the programming language (java) with a loop to sum it up. You can use Hibernate to abstract SQL, or not.

A solution and use a selection structure (if), and according to the option selected in the interface runs the corresponding SQL, presenting the list as the option chosen.