INSERT INTO Databasename2..EMPLOYEE2(EMPID, STARTTIME, ENDTIME, TASK, EVENTID, ADDRESS1)

SELECT a.EMPID, a.STARTTIME, a.ENDTIME, a.TASK, a.EVENTID, a.ADDRESS1

FROM dbo.table1 a LEFT OUTER JOIN Databasename2..EMPLOYEE2 b

ON a.EMPID = b.EMPID

AND a.STARTTIME = b.STARTTIME

AND a.EVENTID = b.EVENTID

AND a.ADDRESS1 = b.ADDRESS1

WHERE b.EMPID IS NULL

There was one clustered index on Databasename2...EMPLOYEE2.EMPID and after analysing execution plan I created a non-clustered index on columns Databasename2..EMPLOYEE2 - STARTTIME, EVENTID, ADDRESS1 query performance improved by 2 hours (earlier it was taking 5 hours) and also execution plan was looking good too but I still couldn't reach the target of 30 mins. Then again I tried with different index combinations also included covering indexes but none of them seem to be helping the query performance.

So I copied backups onto Dev servers and then dropped all indexes and ran the query and it just took 5 minutes to complete.

As clustered index on EMPID is a must on the table for good searches I have decided to drop the clustered index at the start of the procedure run and then recreating it back at the end.

Drop and re-create clustered index takes 5 minutes in total. So my procedure is completing in just 10 minutes ?

Glad the issue is finally resolved 🙂

Thank you all for the help and support 🙂