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