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.