Database Design question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database Design question

Author

Topic

peddi_praveen
Starting Member

48 Posts

Posted - 2003-12-15 : 01:36:08

Hi Guys,
We have table with few million records in it. For Reports, we are fetching from this table.
As it is too huge table, it is been a performance issue. To improve the performance, Iam thinking of Storing Statistics in summary table. Seeking your advice on how to implement the following requirement in db design.

The Table Basic storage (simplified)
Record Status As on
20/01 21/01 22/01 23/01 24/01 25/01/2003
R1 A D D A A A
R2 A A D D A A
R3 A D D A A A
R4 A A A D A A
R5 A A D A A A
R6 A D D D D A
R7 A A A D A A
R8 D A D A A A
R9 A A A D A A
R10 A D D D D A
R11 A A
R12 A A
Active As on 9 5 3 4 6 12

* A = Active
* D = Deactive

Please note that this is in history database,Actual Table Storage:

RecNo Status LastUpdateTime VersionId
R1 A 20/01/2003 0
R2 A 20/01/2003 0
R3 A 20/01/2003 0
R4 A 20/01/2003 0
R5 A 20/01/2003 0
R6 A 20/01/2003 0
R7 A 20/01/2003 0
R8 D 20/01/2003 0
R9 A 20/01/2003 0
R10 A 20/01/2003 0

R1 D 21/01/2003 1
R2 A 21/01/2003 0
R3 D 21/01/2003 1
R4 A 21/01/2003 0
R5 A 21/01/2003 0
R6 D 21/01/2003 1
R7 A 21/01/2003 0
R8 A 21/01/2003 1
R9 A 21/01/2003 0
R10 D 21/01/2003 1

.....................................
uNIQUE IDENTIFICATION IS BASED ON RecNo, VersionId,
whenever R1 changes, version id will be generated and new record will be created in table.

following are My Requirements:
1.When User inputs a particular date range, we should be able to fetch Number of Distinct Records which are active any time within the date range.

Example: for a given date range of 20/01/2003-21/01/2003,
I should be able to fetch 10 records,( r1 - r10).

as mentioned above, this history table is too big m and fectching count(Distinct Rec_No) is been a problem.

Seeking Help,
Praveen Kumar

mr_mist
Grunnio

1870 Posts

Posted - 2003-12-15 : 04:21:41

I'm not sure what you're trying to show us about the current structure. Could you post the DDL (create table) statements for your current setup?

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources