We are designing a table with high insert / delete activity. The table maintains sequence of actions per specific experiments. These are the attributes:
Expected number of records - around 10 million, expected number of
inserts (pure additions) around 5000 a day, number of change
(delete/insert) around 1000
M.G. (michael@gurfinkel.us) writes:
We are designing a table with high insert / delete activity. The table maintains sequence of actions per specific experiments. These are the attributes:
Important question: is the activity concurrent or not? That is, can data for multiple experiments be written simultaneously?
Expected number of records - around 10 million, expected number of
inserts (pure additions) around 5000 a day, number of change (delete/insert) around 1000
OK, so that is not really high activity. :-)
And then I would assume that there is no concurrent activity.
I would say that you scrap the ACTIVITY_SEQUENCE column, as it seems
to serve no purpose. Make (EXP_ID, ACT_SEQ, ACT_ID) a primary key, because
a having a primary key based on data is a good thing. You may never
look at indivitual data, but you don't want duplicates. And make
this PK clustered, that seems to fit your use of the table very well.
--
Erland Sommarskog, Stockholm, esquel@sommarskog.se
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 49:13:15 |
Calls: | 6,648 |
Files: | 12,200 |
Messages: | 5,330,097 |