[thelist] database indexes (was:(no subject))

[rudy limeback]

> My team was just told that the ratio of indexes to information in our
> database design was too high.

hi jay

who told you this?  sounds like crapola to me

actually, having been a dba myself, it sounds like something a dba would
say when not much else is happening and they want something to do or
somebody new to annoy or push around...

of course, that's just my opinion, i could be wrong...

> We feel that the design is correct, but we do have a number
> of tables that only have 3-5 fields, one of which being an
> ID which is kept in a field in our main "person" table.

hate to break it to you, but there is no "correct" database design, there
are only designs that are more correct than others...

;o)

separate tables that quote "belong together" unquote (e.g. they have the
same pk) is called vertical partitioning and is a legitimate design
option -- again, it's done for performance reasons, and the ones who should
be testing the performance pros and cons are the dbas...

> How does the ratio of indexes to information impact
> dB performance? How can I justify not moving 7 or 8 tables
> with 3-5 fields each into the main table (giving it an additional
> 15-30 fields) other than on the fact that we have 20 odd hourse
> invested in code already?

ah, well, now it's getting trickier

first of all, indexes *improve* performance -- except for inserts, updates,
and deletes, where they slow it down

it's a balance, and it's not always easy to find

consider a person table with half males and half females -- adding an index
on sex code is likely not going to help retrieval performance, because a
smart database (of which sql/server 7 is a solid example, i'm not being
sarcastic, it's got a great optimizer) will just ignore the index, it'll
find results faster if it just scans the table sequentially

index optimization is a career in itself, and you'd have to spend a few
days digging through explain plan outputs and performance traces to get the
best index design for any given table

> Additionally, we just discovered that one of our associated tables
consists
> of information already in the company in another database on another
> machine. The DBAs want us to use that table rather than replicating it in
> our database. Can SQLServer work across databases like this?

this is a pretty deep problem, and not all of it will have technological
solutions (in other words, don't try to solve with technology what is
essentially a management or political problem)

ask your dbas to justify the increase in response time going to another
machine versus the maintenance problem of replicating redundant data

nothing wrong with replicating data when the situation calls for it

sounds like the dbas want you to do their job for them

of course, i could be wrong

just like the most rabid anti-smokers are often ex-smokers, there's nobody
can tell you the stupidity of corporate dba departments like somebody who's
been in them for years...


whoa, i'm being snarky tonight, eh?


rudy.limeback
r937.com
evolt.org