I would like to have a table that would be de-normalized. This makes for a fairly large table with almost 600 columns.
The question I have is that the columns are typically varying in length. For flexibility I am thinking of just making them all varchar 256 even though most of them could easily be varchar 40
Is there an issue with performance by doing this?
Within the server there will be little to no difference since the server only stores the number of bytes that are actually present in each column.
There will be a client side difference though. When a column is described the server will tell the client that the column could be as large as 256 bytes. This will result in the client allocating (at least) 256 bytes for the column (note: exact number will depend on API). This also affects the stream layer within the client library: the low level stream will be expecting up to 256 bytes for each column and therefore will need to allocate a large enough buffer to hold N rows (when N will vary) when prefetching the rows from the server. There are limits on the size of buffer that will be allocated so depending on the number of rows being fetched it could limit the number of rows that are prefetched (and hence could reduce throughput if you are fetching thousands or millions of rows).
answered 29 Jul '16, 10:03
I recently worked on converting a database from SQL Anywhere 9 to Microsoft SQL Server where one of the tables contained 5,110 columns, most of which were defined as LONG VARCHAR.
It contained 456,811 rows, 472M total = 459M table + 3.1M ext + 9.5M index, 1,083 bytes per row, so it was clearly sparse. The "bytes per row" includes all disk space allocated to the table (all data and index pages) so it overstates the total amount of column data in the rows.
As far as I know it was a key table with no performance issues reported to me.
FWIW MSS was utterly incapable of handling such a [cough] curiosity without major surgery (on the table, not MSS :)
answered 29 Jul '16, 10:32