Database Design for 100GB DB

Question

Post reply

Database Design for 100GB DB

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

CrazyMan (5/27/2008)

yes jeff, its the Ph column

I was wrong about the databases at work... although they return all of the information for a given customer a full year back in less than 14 seconds, it's because the Usage table, which contains about 4 million rows in each database, has been summarized. The data is totally static once it appears, so the data is summarized and put into a summary table (almost like a data mart). It even beats and indexed view.

They could have made it even faster if the constraint they put on it was a clustered primary key instead of a UNIQUE index AND they had used a Fill Factor of 100 instead of just 90. The summary tables don't actually have a primary key.

If you need the individual rows for a customer 2 years back, as you say, then you need to make sure that it's properly indexed for the query and that the views use UNION ALL instead of just UNION. My personal feeling is that you don't need the individual rows... just a summary for previous months like the guys did at work.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Oh yeah... almost forgot. Each daily database has it's own summary table. All of the days for each month are collect my a view for each month. Then, there's a view each that looks 3 months, 6 months, and 12 months back. Also, in each view, since the data is totally static, WITH (NOLOCK) has been used freely... saves a little bit more time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions