Primary Keys and FKeys

Question

Primary Keys and FKeys

Jason Messersmith

SSC Eights!

Points: 824
More actions
June 4, 2010 at 3:29 pm

#225352

Is there an advantage to not including keys built into the databases?
I am working a 1.2 tb data warehouse. All the relationships are in an SSAS view.
Is there a performance increase by building the database like this?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

The FK constraints are more of a data integrity issue than a performance issue. The big thing is to make sure you have appropriate indexing on your tables or your queries will grind to a snail's pace.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Grey Cat Mr or Mrs. 500 Points: 524 More actions · Answer 2

Keys are for keeping data integrities. On fact tables, usually dimension fields are foreign keys to dimention tables. They should not have performance impact.

Indexes will help the search performance but slow down the ETL processes. Some ETL processes remove the indexes before data loading and add indexes back after the loading.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Agreed with the others here. Keys are about integrity. Performance is index related.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 4

To add an "it depends" opinion:

I've read that there are cases when a PK-FK relation will help the query analyzer to come up with a more efficient plan. No sample code to prove it though...

Other than that, I second the statements from above.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jason Messersmith SSC Eights! Points: 824 More actions · Answer 5

Jason Messersmith

SSC Eights!

Points: 824

June 4, 2010 at 4:22 pm

#1176756

thx

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

lmu92 (6/4/2010)
To add an "it depends" opinion:
I've read that there are cases when a PK-FK relation will help the query analyzer to come up with a more efficient plan. No sample code to prove it though...

That's true. I do not have hard proof of it on SQL Server but I do have hard proof of it on Oracle environment.

FK constraints - if enabled - do have a negative impact in performance, they add the overhead of checking if there is or not a parent key but, if not enabled overhead is not longer there but still tells rdbms that there is a relationship in between those two tables, on that particular column.

Particularly for Data Warehouse environments Oracle relies on FK's defined as disable-rely-novalidate to enable "star transformation" feature which takes advantage of what Oracle calls bitmap indexes.

Sorry for the paragraph before, just wanted to make the point rdbms have the ability to take advantage of FKs even if disabled. 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.