Save reports to a table or run a database query?

Asked

Viewed 560 times

0

I am setting up an e-commerce where I have an area to generate sales reports. These reports are divided by year and month.

Currently I have a query sql where segment the results by year and month and then get the results I want, for example:

  • Sales
    • Total sales;
    • Total sales in R$;
  • Product
    • List of products;
    • Best selling product;
  • Sending
    • Total sales sent by post;
    • Total sales sent by carrier;
  • Paying off
    • Total online payments;
    • Total payments with deposit;

And so on and so forth...

My doubt is the following, as the reports are fixed, IE, when the month ends has no more to change the data of that month, it would be better I save a table with the reports? Or should I keep them through a new query and filter this information whenever the user accesses the report page?

When making the query every time I need to display the report, I feel it is a very heavy and time consuming query, as there are several related tables, for example, tipoPagamento, tipoEntrega, cliente, etc...

Already registering the report data in a table, the query is simpler, but I will have more information in the database. Note: Within the save scheme in a new table, only the list of products would remain through query, as it would generate unnecessary duplication.

I’m using PHP and database MySql.

php mysql

2 answers

2


Saving the results is a good option to optimize because it is common to consult several times the same condition. For example, consulting the entire month is obvious and common. This saves a lot of repetitive processing. Consulting a fortnight or weekly is also common. Find out what is the "default" or what is the most relevant frequency of users.

Of course, this will not make a difference to a quantity not very large. Let’s say up to 800,000 records will not make that much difference. But it is very relative to say such number because it can vary a lot according to the environment and complexity of queries. The ideal (particularly speaking) is just to observe. When you realize you’re starting to slow down, it’s time to optimize.

Example of static filter

inserir a descrição da imagem aqui

This is the search filter on the iTunes sales report page).

There are static options "Yesterday", "7 days, "30 days", etc.
These are the options that probably users consult most often and so probably the internal system automatically generates the "cache" of the queries, returning the data very quickly. And it really returns very fast, considering the amount of data you have.

Alternatively you have the custom search where the user sets the period you want. In this case, if the chosen period has no cache, the query takes a little time depending on the period. But if you repeat the same query, it already returns very fast because a cache is probably generated.

For a small system with little volume you don’t have to worry too much about it. You can implement it on demand. Most of the time it is "in vain" to build something optimized right off the bat, not knowing if the project will even have a large volume.

The rules vary from business to business. Here’s another example, from Amazon.co.jp

inserir a descrição da imagem aqui

Amazon has no option to customize the period. It’s all static.

Which static filter rules to choose is based on opinion, that is, it depends on the business model of each case.

I used examples from two large companies to emphasize and show that even giants apply this method. There is no magic, for example, using fulltext, using innoDB or Myisam, applying indexes and keys here and there. It doesn’t make much difference. But the way you assemble queries can present a big difference such as modeling. And obviously the result caching techniques.

  • The filter itself will only be monthly, at least there are no plans to switch to a different reporting method, until these reports are administrative company. But I liked the expansion and considerations you addressed, including with practical example.

1

I recommend leaving the report via SQL query. Once your report can have start and end date filters, the user could access it at any time and make the necessary comparisons. Regarding queries, try to optimize your query as much as possible, but I see no problem in it since it is well prepared.

Regarding the data, it would have to have a validation, maybe a Trigger so that it is not allowed to change data after a specific time.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.