July 6, 2015 at 8:09 am
Hi I have a table dbo.CustomerServices with over 10 Million Rows
I have none unique Indexes on CustomerID and ServiceID
I've also created a unique Index across both columns.
My delete statement is as follows.
DELETE FROM dbo.CustomerService
WHERE CustomerID =7 and ServiceID =21.
This is currently taking 27 seconds to execute, deleting approx. 75,000 rows.
The main over head being the index seeks on CustomerID and ServiceID.
I can improve performance by performing a soft delete (Updating a bit column(isdeleted)) and then purging the records at a more convenient time. This runs in a second but is not ideal.
It would involve changing a lot of code adding predicates to account for the isdeleted=0.
Does anyone have any other solutions?
July 6, 2015 at 8:18 am
Is the table's PK a foreign key in other tables. Are some foreign keys using the CASCADE-ing?
What about triggers?
You can always perform the deletion in batches, let's say each batch with some thousands or rows, and always avoid deleting high number of rows, especially if there are FKs or triggers.
Igor Micev,My blog: www.igormicev.com
July 6, 2015 at 8:28 am
Igor Micev (7/6/2015)
Is the table's PK a foreign key in other tables. Are some foreign keys using the CASCADE-ing?What about triggers?
You can always perform the deletion in batches, let's say each batch with some thousands or rows, and always avoid deleting high number of rows, especially if there are FKs or triggers.
Igor,
The table's PK is a foreign Key in other tables.
Also CustomerID and ServiceID are Foreign Keys to the Customer and Services Table respectively. There are no triggers defined.
I've thought about batch processing the deletes but I'm still concerned the overhead will be to high.
Cheers,
Simon
July 6, 2015 at 8:35 am
SimonH (7/6/2015)
Igor Micev (7/6/2015)
Is the table's PK a foreign key in other tables. Are some foreign keys using the CASCADE-ing?What about triggers?
You can always perform the deletion in batches, let's say each batch with some thousands or rows, and always avoid deleting high number of rows, especially if there are FKs or triggers.
Igor,
The table's PK is a foreign Key in other tables.
Also CustomerID and ServiceID are Foreign Keys to the Customer and Services Table respectively. There are no triggers defined.
I've thought about batch processing the deletes but I'm still concerned the overhead will be to high.
Cheers,
Simon
Without trying you wouldn't know. What about the cascade-ing of the FKs?
Igor Micev,My blog: www.igormicev.com
July 6, 2015 at 8:44 am
Igor,
I've not used delete on cascade.
Looking into it this might work.
I was under the impression this only worked if you had joins in your delete statement.
Would I add this to the foreign key constraints.
CustomerID and ServiceID
Regards,
Simon
July 6, 2015 at 8:47 am
SimonH (7/6/2015)
Igor,I've not used delete on cascade.
Looking into it this might work.
I was under the impression this only worked if you had joins in your delete statement.
Would I add this to the foreign key constraints.
CustomerID and ServiceID
Regards,
Simon
No, I just wanted to know if you use it.
Having CASCADE-ing ON DELETEs in FKs would make the operations delete operations slower.
Igor Micev,My blog: www.igormicev.com
July 6, 2015 at 8:55 am
Ill have a look at Batch deleting.
Thanks.
Simon
July 6, 2015 at 10:04 am
27 seconds is quite a long time to delete from a table if the system is live. You could come across errors where inserts and updates are failing as the table is locked. Best delete with a batch amount small enough not to interfere with other statements:
DECLARE @BatchSize int
DECLARE @RowsDeleted int
DECLARE @TotalRowsDeleted int
SET @TotalRowsDeleted = 0 -- Initialise
SET @BatchSize = 1000
SET @RowsDeleted = -1 -- Initialise
WHILE @RowsDeleted <> 0
BEGIN
DELETE TOP(@BatchSize) dbo.CustomerService
WHERE CustomerID =7
AND ServiceID =21.
SET @RowsDeleted = @@ROWCOUNT
SET @TotalRowsDeleted = @TotalRowsDeleted + @RowsDeleted
END
PRINT 'Deleted ' + CONVERT(varchar(20),@TotalRowsDeleted) + ' rows'
You have already said there is an index on CustomerID and ServiceID so this should be just a quick (or slow) as your method but won't lock up your database in the process.
July 6, 2015 at 5:43 pm
Since the primary key on the table from which you are deleting records is a foreign key on other tables, check to ensure that the foreign keys are properly indexed on the child tables. If they are not, then the database engine is probably doing a full table scan on the child table for every record you delete from the parent table.
If this is your problem, then it is easy to fix. Just add indexes on the foreign key columns. 🙂
Hope this helps.
July 10, 2015 at 8:46 am
SimonH (7/6/2015)
...I can improve performance by performing a soft delete (Updating a bit column(isdeleted)) and then purging the records at a more convenient time. This runs in a second but is not ideal.
It would involve changing a lot of code adding predicates to account for the isdeleted=0.
Does anyone have any other solutions?
From my experience performing deletes on large tables, the majority of processing time is the result of transaction logging and (depending on database activity at the time) intermittent blocking from other sessions as well. Another problem with a delete operation is that it requires updating not only the table page but also pages across all non-clustered indexes on that table.
As you've seen, logical deletion by setting an indicator column is a lot more efficient. In many of the transactional databases I work with, deletes are logical and filtering of deleted rows is implemented within a view. Both ad-hoc queries and stored procedures reference the view instead of the base table, so it's transparent.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 10, 2015 at 12:29 pm
SimonH (7/6/2015)
...I can improve performance by performing a soft delete (Updating a bit column(isdeleted)) and then purging the records at a more convenient time. This runs in a second but is not ideal.
It would involve changing a lot of code adding predicates to account for the isdeleted=0.
Does anyone have any other solutions?
If updating a column on the table is quick then finding the rows for a delete would also be quick. So I suggest that each row is probably quite large (contains a large number of bytes) and the problem with the long time is that they are quite large rows which take more time to delete than small rows. If this is the case then you need to delete the rows in several transactions to prevent locks on the table that would stop other sessions updating/reading the table.
July 10, 2015 at 1:16 pm
SimonH (7/6/2015)
Hi I have a table dbo.CustomerServices with over 10 Million RowsI have none unique Indexes on CustomerID and ServiceID
I've also created a unique Index across both columns.
My delete statement is as follows.
DELETE FROM dbo.CustomerService
WHERE CustomerID =7 and ServiceID =21.
This is currently taking 27 seconds to execute, deleting approx. 75,000 rows.
The main over head being the index seeks on CustomerID and ServiceID.
I can improve performance by performing a soft delete (Updating a bit column(isdeleted)) and then purging the records at a more convenient time. This runs in a second but is not ideal.
It would involve changing a lot of code adding predicates to account for the isdeleted=0.
Does anyone have any other solutions?
Has anyone else noticed that there's a statement here that's contradictory to other statements? The poster indicates he "also created a unique Index across both columns", and I have to assume from context that he/she means CustomerID and ServiceID. IF that is true, then how can there be 75,000 records for any combination of values for those two fields ? A Unique Index would fail on non-compliant data, wouldn't it ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply