August 5, 2008 at 5:27 am
Hi,
there is a problem with a delete of 2 records.
delete from smalltable
where name like 'u%'
The table has only 500 records, but there are other big tables referencing the smalltable.
When I preview the execution plan, i see, there are joins of the smalltable to all the big-referencing-tables or their indizes. I think this is the intern way of checking, if the records of smalltable are allowed to be deleted?`
What can I do to improve performance without loosing data integrity?
Good day,
Tobias
August 5, 2008 at 10:40 am
Make sure the column that is foreign keyed to the large tables are indexed in the large tables.
Check for triggers on the tables impacted.
August 6, 2008 at 12:10 pm
One thing you can possibly do is to make sure you have good statistics on the columns involved in the referential integrity on the large referenced tables. SQL Server 2005 maintains statistics by column update counters rather than row-updates as was the case with SQL Server 2000. Updating the stats for those columns involved either by rebuilding the indexes or by issuing an UPDATE STATISTICS command with a higher scan density may cause SQL Server to use more efficient joins to perform the constraint checking.
Otherwise you can check the constraints beforehand by issuing a SELECT statement joining the referenced tables based on the deletion criteria, and if no records are returned, disable the foreign key constraints, delete the values, and re-enable the foreign key constraints. Not sure if that will be any quicker than issuing the delete statement outright, but its worth a try.
August 7, 2008 at 6:25 am
Thank you. For my situation, disabling the foreign key worked fine
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply