Home Forums SQL Server 2014 Development - SQL Server 2014 Delete Query Slow RE: Delete Query Slow
March 7, 2018 at 10:18 am
ChrisM@Work - Wednesday, March 7, 2018 5:31 AMsks_989 - Tuesday, March 6, 2018 8:33 PMI have 2 table i have created index on on both tableCREATE 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