October 15, 2010 at 11:52 am
Dear Friends
I've got a delete statement which is taking 40 secs to run. There's only a row to delete but it's taking 40 secs . The table has got foreign keys which references other tables. The indexes exist on these foreign key columns.
The delete statement is:
DELETE FROM konten WITH(ROWLOCK) WHERE KONTONR = 123456
I have attached the Execution plan with this thread.
October 15, 2010 at 12:04 pm
can you post a table definition with index defs on the konten table?
Looks like you are facing into a DELETE..CASCADE performance issue... this delete has to touch alot of objects to remove even one row... Have you considered disabling the cascading delete on the foreign key on the konten table? You'd have to use some other mechanism to perform the child table deletes, but other options like triggers may impose less performance overhead.
October 15, 2010 at 2:40 pm
It is a good thing you posted the sqlplan :w00t:
Did you notice you are hitting 31 objects with this simple delete statement ?
Table [LEISTUNGSPREISE] is accessed using a clustered index scan multiple times in your plan to seek for Columns "KONTO_STEUER_VK" ,konto_steuer_prov, konto_prov, KONTO_BUCH. Looks like missing indexes.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 15, 2010 at 3:27 pm
Do tables that reference table konten have indexes on their foreign key columns?
October 15, 2010 at 3:52 pm
Michael Valentine Jones (10/15/2010)
Do tables that reference table konten have indexes on their foreign key columns?
Specifically, Do ALL tables that reference table konten have indexes on their foreign key columns?
You're also doing INDEX SCANS on this table: LEISTUNGSPREISE (3 times) - seems like the index isn't completely suitable.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 15, 2010 at 5:35 pm
WayneS (10/15/2010)
Michael Valentine Jones (10/15/2010)
Do tables that reference table konten have indexes on their foreign key columns?Specifically, Do ALL tables that reference table konten have indexes on their foreign key columns?
You're also doing INDEX SCANS on this table: LEISTUNGSPREISE (3 times) - seems like the index isn't completely suitable.
This table (like a few others) is referenced multiple times since there seem to be several foreign key references (different columns of the table LEISTUNGSPREISE referencing the same source konten.KONTONR). There will be several indexes needed to change the scans to seeks. Not sure if this will significantly influence the INSERT performance on the table LEISTUNGSPREISE.
Regarding the business case itself: I'm not sure if it's a good idea (= legally allowed) to physically delete a bank account# including all related transactions... But that's a different story...
October 16, 2010 at 4:15 am
WayneS (10/15/2010)
Michael Valentine Jones (10/15/2010)
Do tables that reference table konten have indexes on their foreign key columns?Specifically, Do ALL tables that reference table konten have indexes on their foreign key columns?
You're also doing INDEX SCANS on this table: LEISTUNGSPREISE (3 times) - seems like the index isn't completely suitable.
Correction: you're doing 4 PK clustered index scans (aka table scan) on the LEISTUNGSPREISE table, each time on a different column (KONTO_BUCH, KONTO_STEUER_PROV, KONTO_PROV and KONTO_STEUER_VK). It seems that these 4 columns would benefit from having an index on them for maintaining the Foreign Key relationship.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 16, 2010 at 4:30 am
Correction: you're doing 4 PK clustered index scans (aka table scan) on the LEISTUNGSPREISE table, each time on a different column (KONTO_BUCH, KONTO_STEUER_PROV, KONTO_PROV and KONTO_STEUER_VK). It seems that these 4 columns would benefit from having an index on them for maintaining the Foreign Key relationship.
There is a non clustered index on each of these fields. Do you think it will beenfit with just one index for these 4 columns?
October 16, 2010 at 11:35 am
ss-457805 (10/16/2010)
Correction: you're doing 4 PK clustered index scans (aka table scan) on the LEISTUNGSPREISE table, each time on a different column (KONTO_BUCH, KONTO_STEUER_PROV, KONTO_PROV and KONTO_STEUER_VK). It seems that these 4 columns would benefit from having an index on them for maintaining the Foreign Key relationship.
There is a non clustered index on each of these fields. Do you think it will beenfit with just one index for these 4 columns?
Strange these indexes aren't being used !
-Did you rebuild them before your action ?
- what's their fragmentation level ?
- do they contain a single value representing a large number of occurrences in relation to the other values ?
edited
- double check these indexe aren't disabled !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 16, 2010 at 1:03 pm
LutzM (10/15/2010)
WayneS (10/15/2010)
Michael Valentine Jones (10/15/2010)
Do tables that reference table konten have indexes on their foreign key columns?Specifically, Do ALL tables that reference table konten have indexes on their foreign key columns?
You're also doing INDEX SCANS on this table: LEISTUNGSPREISE (3 times) - seems like the index isn't completely suitable.
This table (like a few others) is referenced multiple times since there seem to be several foreign key references (different columns of the table LEISTUNGSPREISE referencing the same source konten.KONTONR). There will be several indexes needed to change the scans to seeks. Not sure if this will significantly influence the INSERT performance on the table LEISTUNGSPREISE.
Regarding the business case itself: I'm not sure if it's a good idea (= legally allowed) to physically delete a bank account# including all related transactions... But that's a different story...
How long are they required to keep it around?
October 16, 2010 at 2:22 pm
Ninja's_RGR'us (10/16/2010)
...How long are they required to keep it around?
Due to the table and column names I'd guess we're talking about a database that needs to be compliant with German laws... (just guessing, though)
In that case they have to keep the data at least 10 years (§257 German Commercial Code), tax regulations require 10 years as well (§147 German Fiscal Code), regardless whether it's a bank or any other kind of business. If there are any pending proceedings it will be even longer.
So your account# would still be blocked for 10 more years after the account has been canceled.
Archiving of old data is a totally different story... I'd expect most companies will archive data long before the 10 years are over....
But I'd also expect a company would not delete the account# itself from the main systems (which is pretty much the task described here) during archiving if the account# is no longer used but still archived in order to avoid the risk of duplicates.
I'm not even sure if it's a good idea at all to delete the "master account#" from the "master table".... But that's another totally different story...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply