“Thread was being aborted” error when inserting 25k rows

Question

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?

Answer 1

Strongly recommend you look into Table-Valued Parameters and treat your insert/update as a set (using MERGE probably) instead of calling a singleton stored procedure 25,000 times. You can skip a lot of steps here (in C# you can just stuff your 25K rows into a DataTable, for example, making it easy to pass to a stored procedure in a single call), and reduce overhead significantly.

If you create a type for your bank rows:

CREATE TYPE dbo.BankRow AS TABLE
(
  SortCode int PRIMARY KEY,
  BankName nvarchar(50),
  ShortBankName nvarchar(50),
  BranchName nvarchar(50),
  AddresseeName nchar(60),
  PostalName nchar(60),
  AddressLine1 nchar(100),
  AddressLine2 nchar(80),
  CityOrTown nchar(50),
  AreaOrCounty nchar(60),
  PostCode nchar(12),
  FasterPayments nchar(10),
  DirectDebits nchar(10),
  Chaps nchar(10),
  ChequeCreditClearing nchar(10)
);

Your procedure then might look like this:

ALTER PROCEDURE dbo.BankImport
  @BankRow dbo.BankRow READONLY
AS
BEGIN
  SET NOCOUNT ON;

  MERGE dbo.Banks AS b
    USING @BankRow AS tvp
    ON tvp.SortCode = b.SortCode
  WHEN MATCHED THEN 
    UPDATE SET BankName = tvp.BankName,
               ShortBankName = tvp.ShortBankName,
               ...         
  WHEN NOT MATCHED THEN
    INSERT (SortCode, BankName, ShortBankName, ...)
    VALUES (tvp.SortCode, tvp.BankName, tvp.ShortBankName, ...)
END
GO

So now you can pass your DataTable into the stored procedure once instead of calling the singleton stored procedure 25,000 times. You'll have to add the other columns where I put ... and you'll also have to adjust your C# code to work with a TVP.

I wrote up a quick article on TVPs here, including a quick example on C# syntax:

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

And Erland's article is useful as well:

http://sommarskog.se/arrays-in-sql-2008.html