SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete Query Slow


Delete Query Slow

Author
Message
sks_989
sks_989
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 136
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.

ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)

Group: General Forum Members
Points: 164176 Visits: 21487
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
Exploring Recursive CTEs by Example Dwain Camps
sks_989
sks_989
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 136
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
ChrisM@Work
SSC Guru
SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)

Group: General Forum Members
Points: 164176 Visits: 21487
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search