Groups > comp.databases.ms-sqlserver > #1943

Re: cluster or hash table advise needed

Path	csiph.com!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: cluster or hash table advise needed
Date	Tue, 29 Sep 2015 22:15:16 +0200
Organization	Erland Sommarskog
Lines	27
Message-ID	<XnsA524E262AB726Yazorman@127.0.0.1> (permalink)
References	<fa7449c7-daca-4a6d-81ba-603063464b4f@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx02.eternal-september.org; posting-host="28c81df667803bece5d72ef7e4457df0"; logging-data="18606"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/sBcR5UC/rRXZS5rVma7f3"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:QxnPgSQ9YJYI3YD/9PbPlQif7+E=
Xref	csiph.com comp.databases.ms-sqlserver:1943

Show key headers only | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

cluster or hash table advise needed "M.G." <michael@gurfinkel.us> - 2015-09-29 12:06 -0700
  Re: cluster or hash table advise needed Erland Sommarskog <esquel@sommarskog.se> - 2015-09-29 22:15 +0200
    Re: cluster or hash table advise needed "M.G." <michael@gurfinkel.us> - 2015-09-29 13:55 -0700

csiph-web