Delete Query Slow

Question

Delete Query Slow

sks_989

SSC Eights!

Points: 843
More actions
March 6, 2018 at 8:33 pm

#327720

I have 2 table i have created index on on both table
CREATE NONCLUSTERED INDEX [IX_NC_source_t_Test_PCode] ON [Common].[source_t_Test]
(
    [Code] ASC,
    [P_Code] ASC,
    [RP Code] ASC,
    [ES] ASC
)
INCLUDE (     [ItemID],
    [ATime],
    [PrimaryICode],
    [P First Name],
    [P Last Name],
    [P Number],
    [R P First Name],
    [R P Last Name],
    [R Number],
    [CreatedDateTime],
    [CreatedBy],
    [ModifiedDateTime],
    [ModifiedBy],
    [RS],
    [UIndicator]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE NONCLUSTERED INDEX [IX_NC_source_t_Temp_Test_PCode] ON [Common].[source_t_Temp_Test]
(
    [Code] ASC,
    [P_Code] ASC,
    [RP Code] ASC,
    [SS] ASC
)
INCLUDE (     [ATime],
[PrimaryICode],
    [P First Name],
    [P Last Name],
    [P Number],
    [R P First Name],
    [R P Last Name],
    [R Number],
    [CreatedDateTime],
    [CreatedBy],
[FName],
    [PVersion]
[FReload] ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DELETE final
            FROM Common.source_t_Test final
            inner join Common.source_t_Test_temp temp on final.[Code]=temp.[Code]
            and final.[P_Code]=temp.[P_Code] and final.[RP Code]=temp.[RP Code]
            and final.ES=temp.[SS]
There are 30 mil records on both table. when i delete it takes about 4 hours.
I was thinking to create 1 more field and update that field as delete indicator and then delete record from final table.
Before i do this route i want your suggestion.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

sks_989 - Tuesday, March 6, 2018 8:33 PM
I have 2 table i have created index on on both table
CREATE NONCLUSTERED INDEX [IX_NC_source_t_Test_PCode] ON [Common].[source_t_Test]
(
    [Code] ASC,
    [P_Code] ASC,
    [RP Code] ASC,
    [ES] ASC
)
INCLUDE (     [ItemID],
    [ATime],
    [PrimaryICode],
    [P First Name],
    [P Last Name],
    [P Number],
    [R P First Name],
    [R P Last Name],
    [R Number],
    [CreatedDateTime],
    [CreatedBy],
    [ModifiedDateTime],
    [ModifiedBy],
    [RS],
    [UIndicator]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE NONCLUSTERED INDEX [IX_NC_source_t_Temp_Test_PCode] ON [Common].[source_t_Temp_Test]
(
    [Code] ASC,
    [P_Code] ASC,
    [RP Code] ASC,
    [SS] ASC
)
INCLUDE (     [ATime],
[PrimaryICode],
    [P First Name],
    [P Last Name],
    [P Number],
    [R P First Name],
    [R P Last Name],
    [R Number],
    [CreatedDateTime],
    [CreatedBy],
[FName],
    [PVersion]
[FReload] ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DELETE final
            FROM Common.source_t_Test final
            inner join Common.source_t_Test_temp temp on final.[Code]=temp.[Code]
            and final.[P_Code]=temp.[P_Code] and final.[RP Code]=temp.[RP Code]
            and final.ES=temp.[SS]
There are 30 mil records on both table. when i delete it takes about 4 hours.
I was thinking to create 1 more field and update that field as delete indicator and then delete record from final table.
Before i do this route i want your suggestion.

What are these indexes used for?
What key combination guarantees unique rows?
What clustered indexes do you have on these tables?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sks_989 SSC Eights! Points: 843 More actions · Answer 2

ChrisM@Work - Wednesday, March 7, 2018 5:31 AM
sks_989 - Tuesday, March 6, 2018 8:33 PM
I have 2 table i have created index on on both table
CREATE NONCLUSTERED INDEX [IX_NC_source_t_Test_PCode] ON [Common].[source_t_Test]
(
    [Code] ASC,
    [P_Code] ASC,
    [RP Code] ASC,
    [ES] ASC
)
INCLUDE (     [ItemID],
    [ATime],
    [PrimaryICode],
    [P First Name],
    [P Last Name],
    [P Number],
    [R P First Name],
    [R P Last Name],
    [R Number],
    [CreatedDateTime],
    [CreatedBy],
    [ModifiedDateTime],
    [ModifiedBy],
    [RS],
    [UIndicator]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE NONCLUSTERED INDEX [IX_NC_source_t_Temp_Test_PCode] ON [Common].[source_t_Temp_Test]
(
    [Code] ASC,
    [P_Code] ASC,
    [RP Code] ASC,
    [SS] ASC
)
INCLUDE (     [ATime],
[PrimaryICode],
    [P First Name],
    [P Last Name],
    [P Number],
    [R P First Name],
    [R P Last Name],
    [R Number],
    [CreatedDateTime],
    [CreatedBy],
[FName],
    [PVersion]
[FReload] ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DELETE final
            FROM Common.source_t_Test final
            inner join Common.source_t_Test_temp temp on final.[Code]=temp.[Code]
            and final.[P_Code]=temp.[P_Code] and final.[RP Code]=temp.[RP Code]
            and final.ES=temp.[SS]
There are 30 mil records on both table. when i delete it takes about 4 hours.
I was thinking to create 1 more field and update that field as delete indicator and then delete record from final table.
Before i do this route i want your suggestion.
What are these indexes used for?
What key combination guarantees unique rows?
What clustered indexes do you have on these tables?

We have use index to process fast.

This is uniqueness - [Atime] [Code] [P_Code] [RP Code] [ES]
Have cluster index on source_t_Test on Itemid there is no cluster index on source_t_Test_Temp

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Same as INSERTs and UPDATEs, DELETEs can be throttled by indexes. You usually only need one index for the join to identify target rows, and it doesn't require an INCLUDE section, on the contrary the extra weight would slow it down (fewer rows per page). Try disabling ordinary indexes (except the one used for the join) before the delete and rebuilding them afterwards.
Use a manual table lock if you can, it's less costly than row or page locking.
If the proportion of the table affected by the delete is high, you may have to split the delete into batches to avoid blowing your tran log.
Depending on your RAM you might want to investigate cache priming. Updating (or deleting) rows in the data cache is fast, having to fetch them from disk first is slow. Separating out these two steps can be very much quicker than a convention DELETE.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden