Thread: Table with 90 columns

Hi,

I have created a table that has now around 90 columns of type text,
integer and date. There could be further extensions in future for more
columns. Up to now, the table has around 3000 tuples. My question now
is, is it better, e.g. for performance reasons, to make 2 or more tables
out of this, or doesn't make that sense? Any answer is highly appreciated.

TIA Michael

>
> Ligia Pimentel schrieb:
> > Yes, a very wide table (many columns) will be less efficient than a table
> > with less columns (this is a matter of relational concepts and
> > normalization).
> >
I must admit that I do not understand this comment:
what has normalization to do with performance?

As far as I understand normalization it is meant to avoid *redundance* and
not to improve performance. Actually normalization in general decreases
performance becaus a join over several tables is much less efficient than a
select on a single table. In most (but not all!) cases it is more important
to avoid inconsistencies due to redundance however.

The only disadvantage of a table with many columns that I can imagine occurs
when the columns are addressed by name rather than by index. If postgres
implements the column lookup by a linear search through all column names,
this can become an issue in very wide tables.

Even in that case I do not know whether a breakup in n tables might increase
performance.

Christoph Dalitz

Hi.

You are right, if you are always using always all 90 fields of the record
and if it is normalized means you would have to make a lot of joins with
other tables, but, MY THEORY is that if the records are longer, the buffer
will read less records each time you access (physically) the disk, and (even
on an optimally indexed table, you will have more disk access, so it wil be
slower. Of course, this could be set up by fixing buffer sizes, but... I
would always suspect a table with 90 columns has a lot of redundancy on it,
and you will find other problems later, (again, I work by this rule...).

Anyway, On this case, I think that changing the database structure would
imply changes on the server side, not on the client side (unless all the
logic of the application is on the client-side, on user pages) which is not
desirable... again and this is MY THEORY, I would use server side components
(java servlets, ISAPIs, or something like that) which would make your
application more manageable,...

On the other side, remember, If you give the same problem to 10 software
engineers, you would surelly get 10 different solutions, so, I guess, if it
works for you...

Ligia


You wrote...
>As far as I understand normalization it is meant to avoid *redundance* and
>not to improve performance. Actually normalization in general decreases
>performance becaus a join over several tables is much less efficient than a
>select on a single table.


_________________________________________________________________
Join the world�s largest e-mail service with MSN Hotmail.
http://www.hotmail.com