Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Perfomance due to foreign key Expand / Collapse
Author
Message
Posted Tuesday, April 1, 2014 10:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 9, 2014 10:20 AM
Points: 427, Visits: 258
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
Post #1557098
Posted Tuesday, April 1, 2014 10:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 14,029, Visits: 28,404
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1557111
Posted Friday, May 2, 2014 11:45 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:40 PM
Points: 421, Visits: 1,000
There may also be triggers in place doing writes somewhere.
Post #1567137
Posted Friday, May 2, 2014 10:28 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:34 PM
Points: 3,109, Visits: 11,516
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

Post #1567211
Posted Saturday, May 3, 2014 12:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 6,842, Visits: 13,369
...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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1567289
Posted Sunday, May 4, 2014 1:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:40 PM
Points: 421, Visits: 1,000
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
Post #1567317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse