Yes it is bunch of destination table fileds joined to the source table fields but not a denormalized table though. This was working fine until there were only 10 fields in table A but as soon as it is increased to 70 fields sp took ages to complete

Let me explain the code

At source there is table A and at destination table B,C and so on with 2 million records

BEGIN TRY

BEGIN TRANSACTION

DELETE FROM table A --- 500,000 records

INSERT INTO table A

SELECT filelds from table a and table b and so on

LEFT JOIN and

LEFT JOIN … and

WHERE ….

ORDER BY …

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

Instead of permanent table A I advised developer to use temporary table and it was much quicker any thoughts why taht might be