Have Geek Brain

Relational database design pattern question - read-only massive database

I have an interesting task of creating a database that imports data from about 500 different sources.

Each source has its own schema, and many are very different. However, they are all data about a common entity.

My first thought is a typical entity / Attribute / Value schema, however after converting denormalized import from one source (550k rows) to AEV, I end up with 36 million rows in the Attribute_Value table. With the relevant indices, it's still very fast, but it's just one of 500 import sources.

I don't think this will scale, however it does a very nice logical separation, we don't need to merge import sources, so we could build (in theory) 50 or so separate databases.

I'm looking for people who have worked with massive data sources and their experience with things when the row count is in the hundreds of millions.

0


a source to share


2 answers


Have you considered OLAP solutions? They are probably meant for situations like yours. Massive amount of data to read and analyze.



+2


a source


I have tables with billions + rows, the number of rows is not as critical as the level of fragmentation and the width of the table itself, the wider the table, the less you can fit on the page

next to OLAP / SSAS Have you considered using partitioned functions (new in SQL Server 2005)



You can also take advantage of page and row level compression (new in SQL Server 2008), this will help you store more data in RAM, I did my own compression testing, see this link to see how it compares to no compression Quick view compression in SQL 2008

+2


a source






More articles:


All Articles