I need to store about 25k rows worth of data into a single table about once a week. To achieve this, I'm creating an object (created from parsing XML), and sending the data within that object to a stored procedure. There's an object containing data for each row in the table.
Here's the stored procedure:
ALTER PROCEDURE [dbo].[BankImport] -- Add the parameters for the stored procedure here @bankName nvarchar(50) = null, @shortBankName nvarchar(50) = null, @branchName nvarchar(50) = null, @sortCode int, @addresseeName nchar(60) = null, @postalName nchar(60) = null, @addressLine1 nchar(100) = null, @addressLine2 nchar(80) = null, @cityOrTown nchar(50) = null, @areaOrCounty nchar(60) = null, @postCode nchar(12) = null, @fasterPayments nchar(10) = null, @directDebits nchar(10) = null, @chaps nchar(10) = null, @chequeCreditClearing nchar(10) = null AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF EXISTS (SELECT 1 FROM [dbo].[banks] WHERE sortCode = @sortCode) UPDATE [dbo].[banks] SET @bankName = bankName, @shortBankName = shortBankName, @branchName = branchName, @sortCode = sortCode, @addresseeName = addresseeName, @postalName = postalName, @addressLine1 = addressLine1, @addressLine2 = addressLine2, @cityOrTown = cityOrTown, @areaOrCounty = areaOrCounty, @postCode = postCode, @fasterPayments = fasterPayments, @directDebits = directDebits, @chaps = chaps, @chequeCreditClearing = chequeCreditClearing WHERE sortCode = @sortCode ELSE INSERT INTO [dbo].[banks] VALUES ( @bankName, @shortBankName, @branchName, @sortCode, @addresseeName, @postalName, @addressLine1, @addressLine2, @cityOrTown, @areaOrCounty, @postCode, @fasterPayments, @directDebits, @chaps, @chequeCreditClearing ) END
It works okay on smaller datasets, but the full-size contains each branch for each bank in the UK. It manages to get through to about 15k rows before giving this error:
Thread was being aborted.
Is this likely to be more of a time-out issue, or inefficiency, or is there something else that I'm missing?