Re: Many small tables vs. Few large tables

On 15 Oct 1996 06:48:19 GMT, Douglas Sur <doug.sur_at_citicorp.com> wrote:
> Given one system with many small tables versus a system with a few large
>tables, which design is a more performance hog?
>
>

  Depends what you are talking about. When it comes to batch processing which accesses most of the data, then a accessing a few large tables is faster, since you don't have to hit a ton of indices. However for interactive, or small query operations the smaller tables end up being faster because the indices generally end up being smaller if you normalized properly.

  A good rule of thumb is to start out completely denormalize your data completely (1 record), then completely normalize (0 repeating fields). Then determine what should be de-normalized based on 90+% hit rate, i.e. if you are always going to get the linked data, and the max # of occurances is fixed, de-normalization will probably improve performance. Of course all of this should be done on paper/screen first.

  Most importantly, every situation is different.

• Keith Moore President KMA Computer Solutions, Inc.

-- 
/*----C/C++--Java--COBOL--Pro*C/Pro*Cobol--OCI--Java--Delphi--ODBC----* 
 *        When the project must be saved at all costs:                *
 * KMA Computer Solutions, Inc.   Project Troubleshooting/Recovery    *
 *---------Linux---AIX---HPUX---SYSV---Novell---NT---OS/2---'95-------*/