August 26, 2008 at 10:50 pm
OK, let me start by stating I'm not interested in 'Best Practices', only real world experience.
I'm working on a project where tables have Unique Clustered indexes, but NO primary key definitions (or foreign keys definitions for that matter). What is the performance impact of this approach?
There are many Best Practice action point that can / should be implemented, but for various reasons it's not going to be that simple to 'down tools' and rewrite significant tranches of code. So I'm starting off with some simple examples, hence this question. Personally I'd never design a database without declaring PK's etc, but whats the performance impact of not declaring the PK? I have a feeling that the real world impact may be 'not a lot'.
pcd
August 27, 2008 at 2:15 am
There shouldn't really be any performance impact.
The main problems you're going to encounter is that you won't be able to use foreign keys (as they require a unique or primary key constraint) and you won't be able to replicate from this DB.
Now the lack of foreign keys may, in some cases, result in a less optimal plan from the query optimiser, since it knows less about the data than it would with the constraint. See - http://blogs.msdn.com/conor_cunningham_msft/archive/2008/08/07/foreign-keys-are-our-friends.aspx. Also, of course, you'll have to worry about data integrity without foriegn keys. Are you doing that in triggers, in the front end or somewhere else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2008 at 2:40 pm
As long as the business tier can maintain the data integrity, the performance impact is only limited to the speed of that layer. Usually the database layer is optimized to handle performance of integrity checks.
However, if I'm not mistaken there are tons of applications with horrible database design that still perform "as well as can be expected with minor index tweaking". Peoplesoft is one of them... I think they're up to over 35,000 tables in the database now if I'm not mistaken. A great deal many of the tables are configured exactly as you describe. And Peoplesoft is pretty widely used by a lot of companies.
Performance tuning for this is a best effort. Typically the "business tier" of these applications are so slow anyway, so they use a "cache and batch" approach to updating the database with an intermediary agent.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply