Re: DB Performance decreases due to often written/accessed table

On 10/19/06, Jens Schipkowski <jens(dot)schipkowski(at)apus(dot)co(dot)at> wrote:
> On Thu, 19 Oct 2006 19:32:22 +0200, Merlin Moncure > > 1. your database design is the real culprit here. If you want things
> > to run really quickly, solve the problem there by normalizing your
> > schema. denomalization is the root cause of many, many, problems
> > posted here on this list.
> Believe it is normalized. We also seperated configuration and runtime
> data. And this is a runtime table.
> This table holds short living data for devices to be registered by a
> registration server. The INSERTs are triggered by external devices. The
> master data tables are perfectly normalized too. What you are seeing is
> not the real column names. I changed it due to readability. attr* have
> really different names and meanings. A "twin" (in real, initiator/member
> of the same conferencing group) is defined by these attributes. Due to
> high flexibility of this system (serverside configuration/ deviceside
> configuration for runtime) there is no other way to normalize.

ok, fair enough =) still, it feels odd that you are relating two
tables on all 6 attributes. istm there is something more elegant
possible, hard to say.

> > 2. barring that, the above query will run fastest by creating
> > multi-column indexes on regtwin (attr*) fields. and reg(attr*). the
> > real solution to problems like this is often proper idnexing,
> > especially multi column. saying indexes take to long to build is like
> > saying: 'i have a problem, so i am going to replace it with a much
> > worse problem'.
> I will index it. Just prepared the test and will run it tomorrow.
> > 3. try where exists/not exists instead of where in/not in
> Did try it, before I switched to NOT IN. It was 10 times slower.

double check that when properly indexed.

merlin