I have a requirement for copying an entity (ex: Program) from web (ASP.NET MVC page) where user can select an option to copy entire data related to a Program. In this case Program entity has data in atleast 30 to 40 tables and currently we have a stored procedure that takes ProgramID and then uses bunch of MERGE USING WHEN NOT MATCHED THEN INSERT (to cater to few business rules and logic), AND INSERT INTO statements to destination tables. The entire SP is in a transaction block and since there are quite a few tables involved, this operation is locking DB and also we are performance issues. Also since the user is doing this action from screen, the expectation on the response time is somewhere under a minute
Any alternative suggestions on improving the performance?