4

My company has a product centered around a fairly normalized mssql server database. At this point we have several reports and a web application that needs the data in a much flatter fashion to display the data appropriately to the user. Today we are using views/stored procedures to provide flattened data model needed by the reports and web app.

Currently there are performance problems though with several of the views due to the amount of aggregate data and joins being performed inside the views. My thought was to instead provide denormalized tables that could be optimized for performance for both the reports and the web application. These denormalized tables would be built at some interval of time or maintained as updates happened to the underlying oltp tables.

Is this a common thing to do? Am I asking for trouble as far as performance just keeping these tables up to date? What mechanisms are the best for building these sort of tables? Is there a better way to do this than these denormalized tables?

4

What you are thinking about is relatively common and pre-processing the data can indeed help the performance of reports and web pages. The 'cost' to this is that the pre-processing takes time, so your denormalized tables (you can think of this as a mini-OLAP Data Warehouse) are never fully up-to-date.

I do not consider that to be a big problem, but just one that your users need to understand. (E.g. - "I just finished the xyz transaction and it is not in my report.")

Any reports that need the latest transaction will need to include at least some elements from the live data, instead of the pre-processed "OLAP" data.

Here is a generic link explaining the relationship. http://datawarehouse4u.info/OLTP-vs-OLAP.html

The web page also explains some nuances of the OLTP/OLAP divide, but there is nothing really surprising in the description.

  • I think the out of date data isn't too big of a deal but I would like to minimize it as much as possible while not affecting performance too much on the OLTP database. This could involve some proactive incremental updates if underlying OLTP tables are updated. We have complete control over all applications modifying data in the OLTP database and we also have a centralized mechanism for detecting changes to underlying OLTP tables that can be asynchronous. – Cole W Dec 9 '14 at 15:04
  • Sounds good. You could use Service Broker or Change Data Capture to gather data that can be gathered and processed asynchronously. (This allows the main work to continue as soon as the data is queued.) Then you may still schedule the bigger build steps less frequently. – RLF Dec 9 '14 at 16:24
0

If possible I would avoid creating denormalized tables. Historically this led to manteinance problems for me, after all it is more tables to take into account. If the database engine supports it, first I would create indexes on the views with the performance problems and then you can force the execution plan to use the view's indexes insted of expanding the view using the NOEXPAND hint to the view.

Doc about how to do it in Sql Server http://msdn.microsoft.com/en-us/library/ms187373.aspx

However, creating indexes in views is not always possible there are some restrictions. http://technet.microsoft.com/en-us/library/aa933148%28v=sql.80%29.aspx

  • 1
    We have thought about that in the past but it seems like I've seen a number of people saying that indexed views turned out to be a big problem for them. I will investigate this further though to see if it will work for us. – Cole W Dec 9 '14 at 13:41
  • Yes in certain situations it could be a problem. There is a famous article about it here. However, I think that it can be a good option in most circunstances and the only proposal I can think of apart from using denomarlized tables. – JGA Dec 9 '14 at 14:19
  • Yeah and he actually points to a problem we are trying to resolve as well by moving away from our current views and that's lock contention. – Cole W Dec 9 '14 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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