Perfomance due to foreign key

Question

Perfomance due to foreign key

jagat.patel

SSC Eights!

Points: 908
More actions
April 1, 2014 at 10:25 am

#291894

I have a table with primari key. which is refrenced across 63 columns in 40 + tables
Now when i tried to delete one record in this table it takes lot over 5 min to delete it.
is their batter way to optimize delete??
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

Take a look at the execution plan. It's possible that you're seeing scans across all those other tables. Maybe some indexes are in order.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MMartin1 One Orange Chip Points: 27764 More actions · Answer 2

There may also be triggers in place doing writes somewhere.

----------------------------------------------------

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

jagat.patel (4/1/2014)
I have a table with primari key. which is refrenced across 63 columns in 40 + tables
Now when i tried to delete one record in this table it takes lot over 5 min to delete it.
is their batter way to optimize delete??
Thanks

You probably have a large table or tables that have a foreign key reference to the table you are deleting from without an index on the FK column. In that case SQL Server has to scan the entire table to see if it is OK to delete the row. An index on the FK column would prevent the table scan.

Generally, you should have an index on a foreign key column. It is not created by default when you created a foreign key

LutzM SSC Guru Points: 107049 More actions · Answer 4

...which is refrenced across 63 columns in 40 + tables...

Just wondering: Why is a PK column refernced by more than one column of another table?

There are cases when it is required (e.g. a hierarchy table referencing a persons table).

But there are also cases, where a multi-reference indicates some "room for improvement" of the DB design...

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]

MMartin1 One Orange Chip Points: 27764 More actions · Answer 5

LutzM (5/3/2014)
...which is refrenced across 63 columns in 40 + tables...
Just wondering: Why is a PK column refernced by more than one column of another table?
There are cases when it is required (e.g. a hierarchy table referencing a persons table).
But there are also cases, where a multi-reference indicates some "room for improvement" of the DB design...

I was thinking the same thing when I read the original post 🙂

----------------------------------------------------