best data design practice on performance/size

best data design practice on performance/size

Post by Stev » Tue, 27 Aug 2002 16:20:07



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

 
 
 

best data design practice on performance/size

Post by Doug Mill » Wed, 28 Aug 2002 01:44:22



> Hi,

> I need some advice, tips and maybe some "tricks" on large database
> 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

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

 
 
 

best data design practice on performance/size

Post by Erland Sommarsko » Wed, 28 Aug 2002 07:24:10



> 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?

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

 
 
 

best data design practice on performance/size

Post by Stride » Wed, 28 Aug 2002 09:06:59



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

- Show quoted text -

Quote:> Thanks for your helps.

> Steve

 
 
 

1. Open Source examples of good design/programming practice?[Java]

Can anyone suggest some open source examples of good design/programming
practice preferably in Java?

I suppose http://sourceforge.net/ and http://www.gjt.org projects are
the most accessible.

Turloch O'Tierney


www.geocities.com/totierne

Background:
I have done 6 years commercial coding, but this has mostly been in
maintaining rather than design and development. In Applepie Solutions
(www.applepiesolutions.com) we are developing a new sms messaging system
(for GSM mobile phones) and I want to learn from previous (good)
practice. We are using Linux, java1.2 and UML and we intend to use the
following rough coding standards:

Thinking in java is pretty good, even if you just skip to the end

for the don'ts!
http://www.bruceeckel.com/TIJ2/index.html
[a critical report on the above]
http://www.softpanorama.org/Lang/Java/Tijplus/index.shtml

java faq
http://www.afu.com/javafaq.html

It does seem to make sense to document, describe  or even code unit
tests
ahead of implementation.

I am not sure if there is middleware we can put in the middle instead of
rolling our own threads etc. (say for example services are spread across
machines) I suppose if it is behind interfaces the implementation can be
easily changed.

jlint - java version of lint
http://www.ispras.ru/~knizhnik/jlint/ReadMe.htm

junit - simple unit testing framework
http://artemis.austinc.edu/acad/mathcs/cs/classes/cs56/junit32.zip

gnu make - because it is familiar

javadoc guideline:

suns coding standards:
http://java.sun.com/docs/codeconv/html/CodeConvTOC.doc.html

javadoc home page
http://java.sun.com/products/jdk/javadoc/index.html

jindent
freeware jindent download
http://bec.tucows.com/java/adnload/dljindent.html
[later commercial version available]

perl type regular expressions:
http://www.savarese.org/oro/downloads/index.html
[we can use as part of our product for free there is some licensing
restrictions eg no source]

I suggest we standardise on jdk 1.2.2 and borland jbuilder
http://www.borland.com/jbuilder/foundation/ if we need a non emacs/text
editor tool for builds though emacs does have jdb mode and jde

http://sunsite.auc.dk/jde/

example commented file from
http://java.sun.com/docs/books/jls/html/18.doc.html

Sent via Deja.com http://www.deja.com/
Before you buy.

2. ADO/VC++/VB - How to convert LPDISPATCH to _ConnectionPtr

3. Best practices in architecture/design for bulk-loading

4. multi-statement transactions

5. Designing Security, Looking for Best Practices...

6. postgreSQL 7.0.3 + kdevelop 1.4.1

7. Stored Procs, unique temp Tables, and Good Design Practice

8. Install 8I on a SUN/User rights.

9. Database design best practice

10. Database Design Best Practices?

11. OraFocus Weekly: RMan installation and usage, Buffer Cache size advice, Oracle 9iAS Best Practices