Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, Datetime and float. When asked for a reason for such denormalized table, we were told thats how the record record sets were provided by vendors and hence the mapping table has to be designed in that approach. In addition, table will be used for cross joining across other normalized tables and table will potentially grow to larger size. Currently there are few tables (with 375 columns) with similar design and average of 3 million rows or more on the same database. These existing denormalized tables are partitioned by date. No performance issues on these existing tables as they are not being heavily used yet.

Questions:

  1. Other than saying it is a bad design, do you see any obvious issues with such design that can convince the developers to reconsider their designs strategy in a normalized environment. Of course Normalized versus Denormalized have their own pros and cons.
  2. In your experience, have you seen any common real time performance issues with such columnar tables such as locking, takes longer for DML etc? The developers even mentioned about considering MongoDB as their alternative if SQL Server shows any performance issues.
  3. Do you have some high level performance issues that could possible occur if such tables are designed more often in OLTP. Please note that the company is flexible with adding CPU or memory if needed for any IO or CPU issues.

I know in SQL 2012,there is columnstore index but haven't explored yet.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

Some issues to bring up:

  • For OLTP, inserts will be really slow in a table that wide

  • You will be wasting a lot of space by repeating redundant information

  • Columnstore is a non-modifiable index type so you can't use it in an OLTP environment

  • You greatly complicate referential integrity controls this way. You can't just make foreign keys to make sure you are getting valid values for fields.

  • Indexing will be a nightmare

The real issue here is the developers not understanding design.

Keeping the client data in it's native format is FINE. I do this kind of thing for a living, and I get tables with 500+ fields all the time. The way to handle it is to separate your RAW data from your BUILT data.

If the client gives you a massively wide table, you need to normalize it yourself to make a usable data set. There's nothing stopping you from creating a process that breaks out that data into appropriate tables.

share|improve this answer
    
Thanks.I agree with breaking down the tables which is what I was about to suggest them. Warehouse designing is diff from OLTP but the issue is that they have had this design before I came in. They do not want to introduce any code or framework changes. Additionally, they do not use the database for much of their processing except when persisting the data. Most of the data manipulation are done in application memory and hence least importance is shown towards database concepts esp design.Space is not a concern yet although long term it will be.Thanks again for your inputs.Greatly appreciate it. –  DBAuser Jul 23 '12 at 23:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.