Seriously, are you kidding me?

**** UPDATE March 17, 2009 ****
Be sure to read the comments.
This doesn't appear to be as bad as it seemed…still glad I did the rant though; I learned more about what it's actually doing! :) 
And, it's still good to have good database design – NO MATTER WHAT!
****

OK… today's been a bit of a weird day. I've been doing research for some upcoming events and in general, I'm learning a few things. However, as one does with the web – you can get side-tracked. And, in watching the video (recommended by my good friend Timmie) highlighed in this blog post about DBAs and Developers: http://www.benhblog.com/2009/03/linq-dba-vs-developer.html, I followed a few other links as well and found this direct quote:

"I was surprised to learn that EF decided that since there was not a primary key it would just use all the non-nullable columns as a concatenated primary key.  This might not be what you want."

This quote is also from Ben's blog (and I agree) and specifically this post: http://www.benhblog.com/2008/11/entity-framework-and-tables-with-no.html. And, David Yack (a fellow RD) makes a GREAT comment that you can deselect this but I honestly cannot believe that this is the default???!

My response is……. Seriously, are you FREAKING kidding me?

OK, I don't want to go on a total rant here but this is so bad that I almost cannot believe it. And, well, it's somewhat par for the course with the day that I'm having (with other things that I've learned and been "working on"). I'll come back to more topics that fall under the category of "are you kidding me" in a second. Ah ha… there's a new category for me!!!

Anyway, why is that so bad?

  1. There's no guarantee that all non-null columns are even going to be unique (but, this is the LEAST of the problems)
  2. Making a WIDE composite PRIMARY KEY means that the underlying structure which is a UNIQUE CLUSTERED INDEX won't be as efficient. SQL Server creates this index to enforce the relational rule for a PRIMARY KEY-that it is UNIQUE. So, SQL Server will make this WIDE COMPOSITE SET OF COLUMNS the table's one and only clustered index (ugh – I hope at least it changes the index type to nonclustered…. someone please let me know if this is the case)?? However, if it's not…. then, the clustered index will now force SQL Server to order this table by this combination of columns (which probably don't have any form of pattern) so, this clustered index is probably going to be very prone to fragmentation (which in turn can negatively affect performance). And, even worse, ALL of the non-clustered indexes will ALSO be rebuilt to include ALL of the columns of the clustered index which are not already in the non-clustered index. In fact, this step alone – if a clustered index IS created – will cause ALL of the nonclustered indexes to be rebuilt in addition to the table being rebuilt.

So, please tell me this isn't the case? Sorry if this is a rant but I honestly cannot believe this one.

This is another reason where people just weren't even thinking about what was going on in the backend. Another scenario where a little bit of background knowledge of the "data store" would have REALLY helped.

Oh my?
kt

4 thoughts on “Seriously, are you kidding me?

  1. Yeah – totally unacceptable. I could SWEAR that I’ve seen LINQ to SQL doing some VERY similar things when it comes to UPDATES. (I just need to track down the specifics – then I can write my own rant about it too – because it’s an insanely dumb move.)

  2. Kim,

    I think you my be misinterpreting what is going on here. Or I could. But EF is NOT a database design tool. EF is a relational mapper. It needs to write/generate the SQL to update the data if you modify the entity and want to update it back to SQL server.

    I assume what is happening here is that if you DON’T have a PK in your table or identified in your entity, the only recourse that EF has to uniquely identify that record is via the old values it has.

    EF is NOT creating a PK in the database. Once again, it doesn’t touch the Db schemes.

    Then again, if you have a table in your Db that doesn’t have a PK, well, is that really EF’ fault? I really don’t think it is. It is out right bad db design. Using EF against a bad Db schema won’t magically fix your database.

    BOb

  3. Hey there Bob – OK, this would be a lot better than I understand. And, yes, I’d love to get to the bottom of this (hence the post :). I’m *VERY* glad to hear that it doesn’t change the schema and I get your point that it needs something to map the SQL back to the row. And, I also agree that bad design can’t be magically fixed (that’s actually part of the point of the series)…

    So, is this just that the SQL that’s generated uses ALL NON-NULL columns to reference the row being updated?! Well, then you’re right – it’s not nearly as bad (at all). Phew, I’m very glad it doesn’t change the schema.

    Having said that, I also totally agree with you – the problem is really in the database design. Something that needs to have time/thought put into it otherwise everything that sits on top of it will have "issues". This is just one of many.

    THANKS SO MUCH Bob! I’m glad to hear that this isn’t nearly as bad as it looked!!!
    kt

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.