Very Computer

Hi,

I need some advice, tips and maybe some "tricks" on large database
design. I know the basic principles but dont have experience on
practical implemantations on enterprice database designs.

Its a must that every field should hold only "one" value. But if i
have "3bit" and "5bit" long two data, is it possible two hold them in
one field and do all the search update by manipulating the field bit
by bit? ( Is there enough functions to make select, update on bit
wise?)
(say a field just with the "day of the week"-[1-7:3bit] and hour of
the day"-[1-24:5bit] data)

How important to gain "a few bytes" like this just for the sake of
performance? or does it make "worse" when it comes to all data
manupulation on fields?

(the table probably would have around 40000-50000 records every day
for a year !. so it might be as big as 10-15 millions of records)

do you have more practical advice to gain performance on those sort of
large tables? May be some techniques on dividing the one large table
"horizantally" and "verticaly" into small tables?

Thanks for your helps.

Steve

sql server doesn't do bit stuff really. stick to integers or whatever
is really appropriate for the data.

Quote:> How important to gain "a few bytes" like this just for the sake of
> performance? or does it make "worse" when it comes to all data
> manupulation on fields?

The second is true. A few bytes don't matter.

Quote:> (the table probably would have around 40000-50000 records every day
> for a year !. so it might be as big as 10-15 millions of records)

Oh. I thought you said a big table.
Sounds like you are trackign hits on a web site or something. You
might consider having one table that logs or adds the data with almost
no indexes. Then periodically (daily, weekly, monthly whatever) move
it to an OLAP or reporting table with lots of indexes and do your
reporting off the indexed tables.

Quote:

> do you have more practical advice to gain performance on those sort of
> large tables? May be some techniques on dividing the one large table
> "horizantally" and "verticaly" into small tables?

3 suggestions. Find someone who can design databases to help you get
it setup.
Barring that, Post more info here, and we'll help you.
Barring that, just do it. If it starts slowing down, you will have
learned more and can redo it after you do one of the above 2 options!

;-)           ;-)

-doug miller

Yes, but no one in his sane mind would do it.

Quote:> ( Is there enough functions to make select, update on bit  wise?)
> (say a field just with the "day of the week"-[1-7:3bit] and hour of
> the day"-[1-24:5bit] data)

There are operators for bitwise and, bitwise or, bitwise xor and
bitwise inverse. If you define the columns as binary, you might
even be able use substring to get the pieces.

Quote:> How important to gain "a few bytes" like this just for the sake of
> performance? or does it make "worse" when it comes to all data
> manupulation on fields?

Particularly it makes things worse when it comes to maintenance of
the system. The gain you get in performance by reducing the pages
size by compressing the columns is marginal, maybe 10% if you're lucky.

And you could lose performance (and now are talking 100% or 1000%!) if
you have bit-packed a column which you need to use in important searches.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Quote:> Hi,

> I need some advice, tips and maybe some "tricks" on large database
> design. I know the basic principles but dont have experience on
> practical implemantations on enterprice database designs.

> Its a must that every field should hold only "one" value. But if i
> have "3bit" and "5bit" long two data, is it possible two hold them in
> one field and do all the search update by manipulating the field bit
> by bit? ( Is there enough functions to make select, update on bit
> wise?)
> (say a field just with the "day of the week"-[1-7:3bit] and hour of
> the day"-[1-24:5bit] data)

> How important to gain "a few bytes" like this just for the sake of
> performance? or does it make "worse" when it comes to all data
> manupulation on fields?

> (the table probably would have around 40000-50000 records every day
> for a year !. so it might be as big as 10-15 millions of records)

We do that many inserts in a single day.  As others have said, I wouldn't
bother with trying to save bits here and there.

For one thing it's not very portable if you ever need to change RDBMs.

I'd concentrate on your hardware and database design.

Put your transaction logs on their own RAID 1+0 set of spindles and your
data on a separate physical RAID.

Quote:

> do you have more practical advice to gain performance on those sort of
> large tables? May be some techniques on dividing the one large table
> "horizantally" and "verticaly" into small tables?

    Yes, but quite honestly, this is far from  a large table.    Go with
good fast drives, etc. and you'll do far better than

Quote:> Thanks for your helps.

> Steve