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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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
    
The real issue is not storage, it's that you can't really DO anything with that data. It's a damned flat file dumped into an RDBMS. Any interesting questions (unique number of X, for example) will have to be answered over and over and over again in sub-queries... hence the @JNK recommendation to yes, dump it like that, THEN clean it up into something you can get useful information out of. – Stu Mar 18 at 1:25
    
Also, when devs start mentioning things like "MongoDB", "Big Data" and the likes... they're looking to pad their resume unless: 1) you're talking about multi-TB amounts AND 2) the data is disparate, meaning there are things in there you cannot parse, like PDF blobs. If #1 is not applicable, it's Messy Data; if #1 is satisfied but #2 is not, it's Lots Of Data. – Stu Mar 18 at 1:29

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.