Four min is way too long. You need to split up what you are doing so that the final insert is a single statement with all the data already on the box.

So in general you can follow the following steps, although each DB has its own special bulk operations.

1. Collect all the data before you even talk to the database
2. Insert that data into a temp table that won't lock any other processes
3. Check there are no constraints that will fail for the rows
4. Copy the data over from the temp table to the real table in one go.

Edit - SO from your edit it looks like you have a sproc on the database itself doing the collection from the external resource.

This is bad. You don't want the database to do any work like calling 3rd parties or looping through files. Do all that in the application on a seperate machine.

Its generally good advice not to use any sprocs at all. As then you will know for sure that you don't have any business logic in them.

Four min is way too long. You need to split up what you are doing so that the final insert is a single statement with all the data already on the box.

So in general you can follow the following steps, although each DB has its own special bulk operations.

1. Collect all the data before you even talk to the database
2. Insert that data into a temp table that won't lock any other processes
3. Check there are no constraints that will fail for the rows
4. Copy the data over from the temp table to the real table in one go.

Edit

The way you are doing it with a linked database means that the database will be running that select while all the data downloads from the other database.

Bring the data in with an application instead. Following the steps above

Four min is way too long. You need to split up what you are doing so that the final insert is a single statement with all the data already on the box.

So in general you can follow the following steps, although each DB has its own special bulk operations.

1. Collect all the data before you even talk to the database
2. Insert that data into a temp table that won't lock any other processes
3. Check there are no constraints that will fail for the rows
4. Copy the data over from the temp table to the real table in one go.

Four min is way too long. You need to split up what you are doing so that the final insert is a single statement with all the data already on the box.

So in general you can follow the following steps, although each DB has its own special bulk operations.

1. Collect all the data before you even talk to the database
2. Insert that data into a temp table that won't lock any other processes
3. Check there are no constraints that will fail for the rows
4. Copy the data over from the temp table to the real table in one go.

Edit - SO from your edit it looks like you have a sproc on the database itself doing the collection from the external resource.

This is bad. You don't want the database to do any work like calling 3rd parties or looping through files. Do all that in the application on a seperate machine.

Its generally good advice not to use any sprocs at all. As then you will know for sure that you don't have any business logic in them.