CODEWITHSUNDEEP
mysqlsqlreportingaggregation
John Babson
John Babson

Reputation: 51

Recurring data demand - automated query, or store data directly in SQL?

This is a simple question even though the title sounds complicated.

Let's say I'm storing data from a bunch of applications into one central database/ data warehouse. This is data at a pretty fine level -- say, daily summaries of various metrics.

HOWEVER, I know in the front-end I will be frequently displaying weekly and monthly aggregates of this data as well.

One idea would be to have scripting language do this for me after querying the SQL database - but that seems horribly inefficient, perhaps.

The second idea would be to have views in the database that represent business weeks and months -- this might be the best way to do it.

But my final idea is -- couldn't a SQL client simply run a query that aggregates all the daily data into weeks (or months) and store them in a separate table? The advantage of this is that it would reduce querying time of any user, since all the query work is done before a website or button is even loaded/ pushed. Even with a view, I guess that aggregation calculation would have to be done as soon as the view was queried.

The only downside to having the queries aggregated from the weeks/ months perhaps even once a day (instead of every time the website is loaded) -- is that it won't be up-to-date/ may reflect inconsistencies.

I'm not really an expert when it comes to this bigger picture stuff -- anyone have any thoughts? thanks

Upvotes: 0

Views: 163

Answers (1)

Jaaz Cole
Jaaz Cole

Reputation: 3180

It depends on the user experience you're trying to create.

Is the user base expecting to watch monthly aggregates with one finger on the F5 key when watching this month's statistics? To cover this scenario, you might want to have a view with criteria that presents a window always relative to getdate(). Keeping in mind that with good indexing strategies and query design should mitigate the impact of this sort of approach to nearly nothing.

Is the user expecting informational data that doesn't include today's data? More performance might be seen out of a nightly job that does the aggregation into a new table.

Of all the scenarios, though, I would not recommend manual aggregation. Down that road are unexpected bugs and exceptions that can really be handled with a good SQL statement. Aggregates are a big part of all DBMSs', let their software handle that and work on the rest of your application.

Upvotes: 1

Related Questions