As Drew shared error-handling is very unlikely to be causing performance issues. You need to expect it to take longer as you increase the volume of data, the goal is to make that change as small as possible. A couple of things are probably happening here that hurt performance:
1. Your transaction log is probably growing during this operation and that is going to slow things down. You can do a couple of things to help with this:[/li]
2. Table variables and table-valued parameters do not have statistics so the optimizer estimates 1 row. Once you hit a certain number of rows in a table variable/tvp the nested loop execution plan that is most likely created will NOT be a good choice and performance will get exponentially worse. Some options are:
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question