thank you jeff,

from your comments, on what column should i need to create index?

Also, the requirement is, i have a process which runs once in a month and takes records from comp_details table to calculate the amount to be applied on user_bank table for the users and make the ProcessStatus = 1 for the amount applied for the users.

If temp variable doesn't a fit for this logic then what should i do? It would be great if you could help me changing the logic.

Even i tried to create Index on

CREATE NONCLUSTERED INDEX CI_compID ON [dbo].[Comp_Detail] ([CompID]);

CREATE NONCLUSTERED INDEX CI_processStatus ON [dbo].[Comp_Detail] ([processStatus]);

for 100k records it took 6 seconds, 200k it took 13 seconds,300k took 29 seconds, 400k 42 seconds and 500k took 54 seconds.

Is there any other way still i could improve the speed?