May 10, 2019 at 4:16 pm
I have a table Mytable1 with 735507 rows and this table has 16 columns with about 6 columns with datatype varchar. I am trying to delete about 48,000 rows from this table but it is taking very long ( about 10 minutes ) to delete even 200 rows.
Here are the things I have tried-
Before deleting I ran the following
alter table MyTable1 nocheck constraint all
create a view vwToDeleteSelect that selects 1000 rows at a time -- this is to delete in batches
delete vw from vwToDeleteSelect vw
inner join #tempTable1 t
on vw.key1 = t.key1
I have a list of key1 values that I want to delete in the temp table and I have indexed the temp table on key1 column upon creation of the temp table.
I have even tried deleting the indexes , keeping the indexes, but no performance gain. Are there any other ideas on how I can gain the delete performance?
May 10, 2019 at 4:54 pm
Is there any blocking from other processes ?
May 10, 2019 at 4:55 pm
No, I am the only one running this query at the moment.
May 10, 2019 at 5:49 pm
May 10, 2019 at 5:58 pm
Look at the query plan. Is SQL able to do seeks to find the 1000 rows or does it have to scan the whole table first?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 10, 2019 at 6:21 pm
It has scan to find the 1000 rows
code for the view
select top 200 key1 from [dbo].[Mytable1]
where key1 not in (select key1 from Mytable2)
order by Key1
--I was not ordering before still no performance gain.
May 10, 2019 at 8:02 pm
Before deleting I create a view vwToDeleteSelect that selects 1000 rows at a time -- this is to delete in batches delete vw from vwToDeleteSelect vw inner join #tempTable1 t on vw.key1 = t.key1 I have a list of key1 values that I want to delete in the temp table and I have indexed the temp table on key1 column upon creation of the temp table.
There is no need to create a view. You can just delete 1000 rows in a loop.
Do you have many indexes on dbo.MyTable1?
What is the average size of a row?
I would create a script like this:
So the script would be:
IF OBJECT_ID('tempdb..#tempTable1','U') IS NOT NULL BEGIN
DROP TABLE #tempTable1
END
--
-- Insert ALL the rows that are going to be deleted
SELECT key1
INTO #tempTable1
FROM dbo.MyTable1 a
WHERE NOT EXISTS(SELECT * FROM Mytable2 b WHERE b.key1 = a.key1)
--
CREATE UNIQUE CLUSTERED INDEX IX_#tempTable1_1 ON #tempTable1(key1)
--
IF OBJECT_ID('tempdb..#RowsToDeleteInBatch','U') IS NOT NULL BEGIN
DROP TABLE #RowsToDeleteInBatch
END
CREATE TABLE #RowsToDeleteInBatch(key1 int NOT NULL PRIMARY KEY CLUSTERED);
--
DECLARE @BatchSize int = 1000
DECLARE @RowCount int = @BatchSize
--
WHILE @RowCount = @BatchSize BEGIN
--
-- Insert the keys that are going to be deleted into table #RowsToDeleteInBatch and delted them from #tempTable1
DELETE TOP(@BatchSize) X
OUTPUT key1 INTO #RowsToDeleteInBatch
FROM #tempTable1 X
--
DELETE TOP(@BatchSize) X
FROM dbo.MyTable1 X
INNER JOIN #RowsToDeleteInBatch t ON t.key1 = X.key1;
--
SELECT @RowCount = @@ROWCOUNT;
--
TRUNCATE TABLE #RowsToDeleteInBatch;
--
END
DROP TABLE #RowsToDeleteInBatch;
DROP TABLE #tempTable1;
GO
May 10, 2019 at 8:52 pm
Thank you I will try this.
May 11, 2019 at 8:02 pm
Thank you I will try this.
You really need to answer the questions about how many indexes there are. You'll also need to identify how many foreign keys there are. We had a table where it took over 5 minutes to delete just one row. It turned out to be the FKs... they weren't correctly indexed. Once we indexed those, time to delete a row when down to just a couple of milliseconds.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2019 at 7:40 pm
Guras wrote:Thank you I will try this.
You'll also need to identify how many foreign keys there are. We had a table where it took over 5 minutes to delete just one row. It turned out to be the FKs... they weren't correctly indexed. Once we indexed those, time to delete a row when down to just a couple of milliseconds.
Yes, particularly foreign keys on other tables that reference dbo.MyTable1 would be a cause of slow deletes as the SQL engine would have to check the absence of the related child row on the other table. These are more likely to be unindexed columns as FK's are all related to either a primary key or unique index. The OP should run the following query to check that there are no other tables referencing the table he is deleting from:
EXEC sp_fkeys @pktable_name='MyTable1', @pktable_owner='dbo'
After rechecking the question it looks like maybe Mytable2(key1) needs an index for this reason?
May 13, 2019 at 3:38 pm
Yes, the indexing on the FOREIGN KEY on the referencing tables did it. Thank you !
May 13, 2019 at 3:44 pm
Yes, the indexing on the FOREIGN KEY on the referencing tables did it. Thank you !
Awesome. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply