Table Size Efficiency

Question

We're doing some normalization of our data because it's too modular in some respects. The thing is that the table is getting very wide, with 400 or so columns so far. I've seen that the maximum amount is 1024 but I'm interested in knowing about paging with large table structures. If we had say, 1000 columns, but some were quite large (varchar(max) for example), then would there be a reduction in speed during queries? It's probably going to be accessed thousands of times a day so making sure it's not doing something like paging is quite important.

Basically, what's the maximum we can have before we notice a performance hit?

Answer 1

Technically it would depend on the query for that data, while I don't know the ins and outs of MS SQL Server too well, if all queries to such a table were only ever querying the primary key index it would be fast.

The bigger problem is how many repeats of the same varchar data is there? And does it even repeat in multiple fields as well as records, generally you'll want to seperate this of to an index<->data table then use integer indexes in main table. After all integers are faster to query than string matching, and it's less data storage.

Answer 2

It depends on hardware, OS, number of rows, DB configuration, indexes, and the queries themselves - a lot of things.

When it comes to paging, the main question is how much data is frequently accessed (this is essentially a lot of index data, as well as row data that is frequently retrieved) versus how much physical memory the machine has.

But in terms of general performance, you really need to take into account what sort of queries are being issued. If you're getting queries all over the place, each looking at different columns, then you'll hit performance problems much more easily. If a small set of columns are used in the bulk of queries, then you can probably use this to improve your performance by a reasonable amount.

Note that if your table has lots of repeated data, you really should review your database design.