An Un-indexed Foreign Key Gotcha

Question

An Un-indexed Foreign Key Gotcha

Viewing 8 posts - 16 through 22 (of 22 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Good Article with useful info. Thanks

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

mister_zed Mr or Mrs. 500 Points: 566 More actions · Answer 2

The indexed FKs have nothing to do with performance; increased performance MIGHT be a side-effect, but - as you point out - it might as well not be such an effect. The idea of indexing FK columns has to do with the locking mechanisms.

In order to be sure that you can delete a parent record you must also make sure that there are no children belonging to that parent. This can only be achieved by actually reading the child table(s). When you don't have any usable index on the child table, then you must read all records - and they get locked at the same time, just because they are part of an updating (read-write) transaction.

This is very common truth för many relational databases, including SQL Server, Oracle, Firebird and others, and it has so been since "ancient" times.

P.S. I thought it was common knowledge... 😉

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

The indexed FKs have nothing to do with performance;

I think that is a bit of a stretch. There is almost always at least some performance benefit (often dramatic), both in efficient join/seek and in improved concurrency from reduced locking.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

jswong05 Hall of Fame Points: 3553 More actions · Answer 4

Please read Thomas Kyte's books. He wrote for Oracle but the principles about "transaction processing" and "locking" and "index" are the same.

When FK records can be fastly located by index, the transaction processing will be fast, that reduces required lock-time, hence improves performance. This applies to any records.

Jason
http://dbace.us
😛

mister_zed Mr or Mrs. 500 Points: 566 More actions · Answer 5

I agree it's a bit of a stretch. 🙂

My point was that indexing might have different purposes, optimizing search operations being just ONE of them.

Gary Varga SSC Guru Points: 82166 More actions · Answer 6

sqlpro (11/9/2009)

thanks for the reply. probably i misunderstood but following is the link i got my understanding from.its here http://www.informit.com/library/content.aspx?b=STY_Sql_Server_7&seqNum=145%5B/quote%5D

Careful. You are quoting from a book that is over ten years old. This is usually fine if the book is about theoretical or academic work but that book is a "Teach Yourself To Be A Dummy In 60 Seconds" styled book on SQL Server 7.

I know some people may still be supporting SQL Server 7 installations.

I know that a lot of people like these types of books.

I only said be careful.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

TomThomson SSC Guru Points: 104773 More actions · Answer 7

TomThomson

SSC Guru

Points: 104773

January 11, 2010 at 9:13 am

#1101878

A really nice article. Good explanation.

Tom

Ionut Hrubaru SSC Veteran Points: 276 More actions · Answer 8

Enforcing delete cascade will also help here, instead of creating indexes. I believe it should eliminate the deadlock. Of course if you enable delete cascade it's no point in deleting from child table first and then parent table. We have to do just a delete from parent table.