7

I have a system that holds some big amount of data. The database used is SQL Server. One of the tables have around 300000 rows, and there are quite a few number of tables of this size. There happens regular updates on this table - we say this as "transactional database" where transactions are happening.

Now, we need to implement a reporting functionality. Some of the architect folks are proposing a different database which is a copy of this database + some additional tables for reporting. They propose this because they do not want to disrupt the transactional database functionality. For this, data has to be moved to the reporting database frequently. My question here is, is it really required to have second database for this purpose? Can we use the transactional database itself for reporting purposes? Since the data has to be moved to a different database, there will be latency involved which is not the case if the transactional database itself is used for reporting. Expecting some expert advice.

Thomas
  • 1,970
  • 4
  • 28
  • 59
  • What type of reports are needed? Are these operational reports, long-view/strategic reports, or a mix? – Mike Dec 20 '13 at 05:17

3 Answers3

9

You need to do some research into ETLs, Data Warehousing and Reporting databases, as I think your architects may be addressing this in a good way. Since you don't give details of the actual reports I'll try and answer the general case.

(Disclaimer: I work in this field and we have products geared to this)

Transactional databases are optimised for a good balance between read/update/insert, and the indexes and table normalisations are geared to this effect.

Reporting databases are geared to be very very optimal for read access over and above all other things. This means that the 'normal' normalisation rules that one would apply to a transactional database won't apply. In fact high degrees of de-normalisation may be in place to make the report queries way more efficient and simpler to manage.

Running complex (especially aggregations over extended data ranges such as historical time frames) queries on transactional database, may impact the performance such that the key users of the database - the transaction generators could be negatively impacted.

Though a reporting database may not be required in your situation you may find that the it's simpler to keep the two use cases separate.

Your concern about the data latency is a real one. This can only be answered by the business users who will consume the reports. Often people say "We want real time info" when in fact lots if not all of their requirements are covered with non real time info. The acceptable degree of data staleness can only be answered by them

In fact I'd suggest that you take your research slight further and look at multidimensional cubes for your report concerns as opposed just reporting databases. There are designed abstract your reporting concerns to whole new level.

Preet Sangha
  • 64,563
  • 18
  • 145
  • 216
  • 1
    In addition to everything mentioned above, I would also add ETL (Extract, Transform, Load) to the list of topics to research. – Ola Ekdahl Dec 20 '13 at 05:35
  • @OlaEkdahl - yes of course - I see it each day and forgot to mention it. Thanks – Preet Sangha Dec 20 '13 at 05:35
  • 1
    good point in your last paragraph. way different approach that most people don't take into account – hubson bropa Dec 20 '13 at 05:37
  • Correct me, but isn't two databases inside same instance shares the same temptable (memory)? Moreover if both db's physical file exists in same physical disk, the separation won't give additional benefit since the RAID is basically the same? – Fendy Dec 20 '13 at 07:20
  • @Fendy - I'm not sure what you mean. Are you saying there can be performance issues when sharing resources such as server or disk? If so yes of course that's could happen. Any system needs to be system architected in way that makes sense. – Preet Sangha Dec 20 '13 at 07:54
4

I second Hubson's answer. I myself may not a decent sql server developers, but I have faced with big tables (around 1m rows). So more or less I have the experience for this.

Referencing to this SE answer, I can say that multiple DB on same harddisk won't give performance boost due to I/O capacity of harddisk. If you can somehow put the reporting DB to different harddisk, then you can gain the benefit by having one hdd intensive on I/O, and other in read only.

And if both databases exists in same instance, it shares the same memory and tempdb, which gives no benefit to performance or reducing I/O cost at all.

Moreover, 300k rows is not a big deal, unless it is joined with 3 other 300k tables, or having a very complex query that requires data cleanup, etc. It is different though if your data growth rate is increasing fast in the future.

What you can do to increase the performance of report, without having involving the performance impact for operational db?

  1. Proper indexing

    Beside requiring some storage, proper indexing can lead to faster data processing and you will be amazed with how it speed up processes.

  2. Proper locking

    NoLock imho is the best to use for reporting, unless you use different locking strategy than serialized one in database. Some skew in report result caused by uncommitted transaction usually not matter much.

  3. Summarize data

    A scheduled process to generate summarized data can also be used to prevent re-calculation for report reading.

Edit:

So, what is the benefit of having the second database? It is beneficial though to has it, even though does not give direct benefit to performance. Second database can be used to keep the transaction db clean and separated with reporting activity. Its benefits:

  1. Keeping the materialized data

    For example a summary of total profit generated each month can be stored in table which belong to this specific db

  2. Keeping the reporting logics

  3. You can secure access for specific people which is different with transactional db

  4. The file generated for db is separated with transactional. It is easier for backup/restore (and separating with transactional) and when you want to move to different harddisk, then it is easier

In short, adding another normal database for this situation will not give much benefit in performance, unless it is done right (separate the harddisk, separate the server, etc). However second database gives benefit in maintainability aspects and security strategies though.

Fendy
  • 4,565
  • 1
  • 20
  • 25
  • Your last comment about a second 'normal' database makes sense. A reporting database would not fit this category. – Preet Sangha Dec 20 '13 at 08:11
  • I admit that I have yet involved with reporting service of sql server :). I had experience with teradata though, and it is a great tool for reporting service. – Fendy Dec 20 '13 at 08:20
3

in a perfect world reporting and operational data should be separate. reason for this is you want to focus transactional tables for insert / updates (generally less indices) and reporting for selects (generally more indices). of course the real world is not the perfect world. so here's my rule of thumb. as long as you can do both on the same tables without performance loss while keeping the maintainability of reporting code then there's no real reason to separate. when it is time to make the jump you will probably be at a stage where relational databasing should at least be augmented with a BI solution. Another reason why the two should be separate, when the time comes.

one thing to keep in mind. most architects want to over architect, it is their job after all. keep them honest and make them prove their case. for your part, always hope you are so successful that you will need what they say so design for it, but don't try to implement the end game if it costs you significant speed of delivery issues.

hubson bropa
  • 2,720
  • 2
  • 29
  • 35
  • 1
    I'd add a caveat - "without performance loss while keeping the maintainability of reporting code" – Preet Sangha Dec 20 '13 at 05:37
  • always nice to see someone keeping that in mind, added – hubson bropa Dec 20 '13 at 05:39
  • I've seen very very complex transaction sql that was replaced with a few lines of MDX or reporting SQL, so yes the code is very very important. Also there is the case the simpler SQL on a reporting database can be optimised by the SQL Server more efficiently. – Preet Sangha Dec 20 '13 at 05:42