November 14, 2002 at 12:24 am
Hi all
Senario:
Organistion table
95,000 rows
23 fk links to it from a variety of tables (ranging from 499 rows to 1.2Mil)
Single delete from organisation table 8 seconds
Plan shows sqlserver doing an index scan on the 23 fk's to determine if the deletion is valid.
I need to remove 170 rows, (no data in other tables referring to the records), and im looking at 22minutes minimum.
What the!
Thoughts and suggestions??
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
November 14, 2002 at 1:22 am
DELETE FROM corpsys.dbo.organisation
where exists
(select org_id
from corpsys.dbo.organisation B with (nolock)
where last_updated_by = 'NTISImport'
and organisation.org_id = B.org_id)
(167 row(s) affected)
17min 35sec
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
November 14, 2002 at 2:16 am
I'm afraid your out of luck. I don't believe there is any 'easy' option for getting this done.
The only solution I see is to remove the FK's, do the DELETE and then redo the FK's (with the NO CHECK option).
But of course, this is only possible if there is no other activity on the table that might cause inconsistenties with the RI.
November 14, 2002 at 3:06 am
Ensure each FK has an index on it.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 14, 2002 at 4:32 am
Also, I don't understand your query, this should be the same.
DELETE FROM corpsys.dbo.organisation
WHERE last_updated_by = 'NTISImport'
Unless you have multiple items with orig_id then this may help a bit.
DELETE FROM corpsys.dbo.organisation
where org_id IN
(select org_id
from corpsys.dbo.organisation B with (nolock)
where last_updated_by = 'NTISImport')
As for the index scans, unfortunately that will happen as FKs have to be checked. You do need these indexes but it will always be a scan.
Now one thing you may try to help you most is drop the FK's and create a batch process. But depending on you table and FK layout this might be quite confusing or very simple.
Note: When running code and needing to test multiple ways with need to restore this can be quite helpfull.
Do a batch like so in QA
BEGIN TRANSACTION
Do Select query to see before
Do delete query
Do Select query to see after
Then run either
COMMIT TRANSACTION
ROLLBACK TRANSACTION
In the same QA window without close it.
The transaction will be handled based on the last piece and makes testing easier since no restore before try again.
November 14, 2002 at 5:25 am
Are there any delete triggers on the table?
If so, are they doing something that will degrade performance.
November 14, 2002 at 1:12 pm
I agree with antares686. I believe the queries he suggested, which reduce the nesting, will help a great deal.
However, 23 foreign keys is the problem. Why in the world are there 23 foreign keys on this table. I believe an evaluation of these would provide the real solution. The ERD for this database must look like a spider web around this table.
November 14, 2002 at 4:00 pm
Hoping that some of the changes mentioned above can be implemented - if not maybe look at marking the rows as deleted initially, then run the query off peak. Alternatively, might(!) be better to try deleting fewer records at a time to reduce the total time required. Bandaid ideas, but sometimes they work!
Andy
November 14, 2002 at 9:34 pm
Hi
Thanks for the emails. Sorry about the query actually, my fault, this was 1 of the many in an attempt to improve overall performance. The
DELETE FROM corpsys.dbo.organisation
WHERE last_updated_by = 'NTISImport'
was the first attempt, but we tried a variety of others to speed up overall performance.
The 23fks, well, its a generic "organisation" table, its one of the key drivers for the whole app, the db is a single "app" but with numerous sub-applications inside, therefore, explaining the many fks' and interdependencies with the table.
I am revisting the indexing and triggers etc at present and will go from there
Thanks guys
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply