問題描述

我有一個(gè)保存大量數(shù)據(jù)的系統(tǒng).使用的數(shù)據(jù)庫是 SQL Server.其中一個(gè)表有大約 300000 行，并且有很多這種大小的表.此表會(huì)定期更新 - 我們將其稱為事務(wù)數(shù)據(jù)庫"，其中發(fā)生事務(wù).

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.

現(xiàn)在，我們需要實(shí)現(xiàn)報(bào)告功能.一些架構(gòu)師建議使用不同的數(shù)據(jù)庫，該數(shù)據(jù)庫是該數(shù)據(jù)庫的副本 + 一些用于報(bào)告的附加表.他們提議這樣做是因?yàn)樗麄儾幌肫茐氖聞?wù)性數(shù)據(jù)庫功能.為此，必須經(jīng)常將數(shù)據(jù)移動(dòng)到報(bào)告數(shù)據(jù)庫.我的問題是，是否真的需要為此目的擁有第二個(gè)數(shù)據(jù)庫?我們可以將事務(wù)數(shù)據(jù)庫本身用于報(bào)告目的嗎?由于必須將數(shù)據(jù)移動(dòng)到不同的數(shù)據(jù)庫，因此會(huì)涉及延遲，如果事務(wù)數(shù)據(jù)庫本身用于報(bào)告，則情況并非如此.期待一些專家的建議.

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.

推薦答案

您需要對 ETL、數(shù)據(jù)倉庫和報(bào)告數(shù)據(jù)庫進(jìn)行一些研究，因?yàn)槲艺J(rèn)為您的架構(gòu)師可能會(huì)很好地解決這個(gè)問題.由于您沒有提供實(shí)際報(bào)告的詳細(xì)信息，我將嘗試回答一般情況.

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.

(免責(zé)聲明:我在這個(gè)領(lǐng)域工作，我們有適合這個(gè)領(lǐng)域的產(chǎn)品)

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

事務(wù)數(shù)據(jù)庫針對讀取/更新/插入之間的良好平衡進(jìn)行了優(yōu)化，并且索引和表規(guī)范化針對此效果.

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

報(bào)告數(shù)據(jù)庫非常適合讀取訪問而不是其他所有事情.這意味著將應(yīng)用于事務(wù)數(shù)據(jù)庫的正常"規(guī)范化規(guī)則將不適用.事實(shí)上，高度的非規(guī)范化可能已經(jīng)到位，以使報(bào)告查詢更有效、更易于管理.

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.

在事務(wù)數(shù)據(jù)庫上運(yùn)行復(fù)雜的(尤其是在擴(kuò)展數(shù)據(jù)范圍內(nèi)的聚合，例如歷史時(shí)間框架)查詢，可能會(huì)影響性能，從而使數(shù)據(jù)庫的關(guān)鍵用戶 - 事務(wù)生成器可能受到負(fù)面影響.

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.

雖然在您的情況下可能不需要報(bào)告數(shù)據(jù)庫，但您可能會(huì)發(fā)現(xiàn)將兩個(gè)用例分開更簡單.

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.

您對數(shù)據(jù)延遲的擔(dān)憂是真實(shí)存在的.這只能由將使用報(bào)告的業(yè)務(wù)用戶回答.人們通常會(huì)說我們想要實(shí)時(shí)信息"，而實(shí)際上很多(如果不是全部)他們的需求都包含在非實(shí)時(shí)信息中.可接受的數(shù)據(jù)陳舊程度只能由他們來回答

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

事實(shí)上，我建議您稍微進(jìn)一步研究一下，并針對您的報(bào)告關(guān)注點(diǎn)查看多維多維數(shù)據(jù)集，而不僅僅是報(bào)告數(shù)據(jù)庫.將您的報(bào)告問題抽象到全新的水平.

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.

這篇關(guān)于用于報(bào)告和日常交易的數(shù)據(jù)庫的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！