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
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 suspect you could re-check your relational design and find many fields
that are very repetitive. If you really think you need all this columns in
the same table because of the nature of your application (which I doubt), it
will be reasonable to divide the fields in the table according to the
frecuency of use of each group of fields (a lot of fields will be almost
fixed, -not updated often- and other will be updated frequenly). Like the
parts of the record that are related to general iformation and the other
fields that are related to transactions or balances or status.
I hope this helps.
http://www.devshed.com/Server_Side/MySQL/Normal/Normal1/print_html
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887&pg=1
Good day!
Ligia
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 suspect you could re-check your relational design and find many fields
that are very repetitive. If you really think you need all this columns in
the same table because of the nature of your application (which I doubt), it
will be reasonable to divide the fields in the table according to the
frecuency of use of each group of fields (a lot of fields will be almost
fixed, -not updated often- and other will be updated frequenly). Like the
parts of the record that are related to general iformation and the other
fields that are related to transactions or balances or status.I hope this helps.
http://www.devshed.com/Server_Side/MySQL/Normal/Normal1/print_html
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887&pg=1Good day!
Ligia
Unfortunately I need every column in my application. The problem is,
that the application is Web-based, and it would be a lot more logic
needed on the client-side to separate into general and other
information. The client is just a web-browser, so it is difficult (hence
partly possible) to implement that logic. Is there any hint, how many
columns per table are still reasonable?
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
Import Notes
Reply to msg id not found: 20020916132006.77A37476B8F@postgresql.orgReference msg id not found: 20020916132006.77A37476B8F@postgresql.org | Resolved by subject fallback
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
Import Notes
Resolved by subject fallback