Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Similar to: Question1, Question2 on SO. This is mostly not caused by the database, but by how you work with connections to the db. Some suggestion, you should try the MERGE statement instead of the Update/Insert couple. –  Marian Nov 22 '12 at 12:13
    
Thanks for that, @Marian, I'll look into the MERGE statement! –  edparry Nov 22 '12 at 13:32
    
This code is prone to race conditions, which may be causing the problem depending on the data and how the data is sent to the stored procedure -- see here. If you can change to MERGE, that would be better than fixing the locking issues with this code. –  Jon Seigel Nov 22 '12 at 14:25
1  
@JonSeigel note that MERGE is not magic; it is also prone to race conditions (though certainly safer than the code above). It also isn't necessarily going to solve the problem if you call the stored procedure 25,000 times and it is simply using MERGE each time instead of UPDATE/INSERT. :-) –  Aaron Bertrand Nov 22 '12 at 17:32
1  
@Aaron: Interesting, thanks for the link, I wasn't aware of that -- I figured that kind of thing would be handled automatically since it's a "single statement." The OP gave no indication of the how the application works, so we can't really make the assumption that it's single threaded. (If it is, then I agree with you.) I tend to believe that threading issues and errors are more likely application-related than SQL Server-related, which is why I went down that path in my comment. –  Jon Seigel Nov 22 '12 at 17:57

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
Excellent answer, thank you very much! –  edparry Nov 23 '12 at 16:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.