Improve Delete Performance in Table With Many Related Tables
-
This simple delete statement drives CPU to 100% for long periods of time when a user requests 1,000's of rows to be deleted at once. This causes an equal number of delete statements to be issued for each unique key value leading to RBAR hell. The application will be updated to prevent this, and possibly aggregate the deletes to a single statement, but what would be the best way to improve this condition in the interim? There is an index that could possibly add some improvement, but there must be a better way.
Here is the query plan: https://www.brentozar.com/pastetheplan/?id=rkZygv7hp
-
It's probably going so slow as you have no indexes on the FK column on the child tables.
-
A lot of the child tables have very few rows, so I don't think an index on the FK column would have much effect.
- tgitchel wrote:
A lot of the child tables have very few rows, so I don't think an index on the FK column would have much effect.
How many rows are in tblAlert_Owners_Unfiltered?
-
That table has ~ 62k rows, which is probably why the index recommendation is for there for that table. Most of the other table are in the hundreds of rows or less.
-
I'm not sure why the index recommendation has INCLUDE columns on it because it has to delete the row from the table.
Is there an FK to tblRecords_Unfiltered on tblAlert_Owners_Unfiltered? If so what columns are in the FK?
-
I was curious about that as well but then I found the materialized view that references the columns in question:
CREATE VIEW [dbo].[vqryAlertsCount]
WITH SCHEMABINDING
AS
SELECT ao.AlertOwnerID,
ao.EntityID,
ao.PostponedUntil,
sg.UserID
FROM dbo.tblAlert_Owners_Unfiltered AS ao
INNER JOIN dbo.tblAlerts_Unfiltered AS a
ON ao.AlertID = a.AlertID
INNER JOIN dbo.tblActions_Unfiltered AS ac
ON ac.ActionID = a.ContextPK
INNER JOIN dbo.tblAction_Owners_Unfiltered AS aco
ON aco.ActionID = ac.MasterActionID
INNER JOIN dbo.tblRecords_Unfiltered AS r
ON r.RecordID = aco.EntityID
INNER JOIN dbo.tblSecurityGroup_Members_Unfiltered sg
ON r.OwnerGroupID = sg.SecurityGroupID
INNER JOIN dbo.tblUser_Offices ofc
ON ofc.OfficeID = r.OfficeID
AND ofc.UserID = sg.UserID
WHERE (ao.IsDeleted = 0)
AND (a.IsDeleted = 0)
AND (ac.IsDeleted = 0)
AND (aco.IsDeleted = 0)
AND (r.IsDeleted = 0)
AND (sg.IsDeleted = 0)
AND (ao.IsConfirmed = 0)There is no FK to tblRecords_Unfiltered on tblAlert_Owners_Unfiltered.
-
Because of this indirect reference, that suggested index may have way more impact than estimated.
-
What index have you got on that view?
-
CREATE UNIQUE CLUSTERED INDEX [IX_AlertsCount] ON [dbo].[vqryAlertsCount]
(
[EntityID] ASC,
[UserID] ASC,
[AlertOwnerID] ASC,
[PostponedUntil] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 91, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] -
You could see how the performance is if you drop the clustered index, after dropping the clustered index the view will just behave like a normal view.
Check what indexes are recommended for the delete after you have dropped the index.
After the delete operation recreate the clustered index on the view.
-
Here are the query stats before and after removing the index on the view:
-
Here are the statistics after restoring the indexed view and adding the recommended non-clustered index from the execution plan:
-
I was hoping that deleting multiple rows in a single statement would improve the overall performance, but deleting 5 rows yielded slightly over 5x the runtime:
-
Could you change the method of deleting rows so that you drop the clustered index on the view, then delete the thousands of rows, then reinstate the clustered index?
Also check that the the child tables have indexes on the FK columns relevant to the delete.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply