SQL Server Performance

INSERT performance

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Heartsbane, Oct 8, 2009.

  1. Heartsbane New Member

    I've got an import table that contains about 500k records of unencrypted data, and a target table with around 150 million rows of encrypted data. I have written a stored proc to insert/append the imported data into the target table, but need to improve it's performance. In a nutshell, I'm using a standard INSERT...SELECT statement, similar to:INSERT
    INTO AccountMaster WITH (TABLOCK)
    (AccountID, FirstName, MiddleName, LastName, Address1, Address2, City, State, PostalCode, BirthDate)SELECT
    EncryptByKey(Key_GUID('AccountKey'), convert(varchar(11),s.AccountID)),EncryptByKey
    (Key_GUID('AccountKey'), s.FirstName),EncryptByKey
    (Key_GUID('AccountKey'), s.MiddleName),EncryptByKey
    (Key_GUID('AccountKey'), s.LastName),EncryptByKey
    (Key_GUID('AccountKey'), s.Address1),EncryptByKey
    (Key_GUID('AccountKey'), s.Address2),EncryptByKey
    (Key_GUID('AccountKey'), s.City),EncryptByKey
    (Key_GUID('AccountKey'), s.State),EncryptByKey
    (Key_GUID('AccountKey'), s.PostalCode),EncryptByKey
    (Key_GUID('AccountKey'), convert(varchar(10),s.BirthDate,101))FROM
    Stage_AccountMaster s;
    Looking at the Execution Plan and SQL Profiler, about 54% of the execution time is used in a Table Scan of the imported table, 1% on a Compute Scalar for the encrypt/converts, and the other 45% on the Table Insert to commit the data to the target table.
    Are there any alternative ways to do this to improve performance? The target table does not have any clustered index, INSERT triggers, etc. I've looked at things like INSERT...OPENROWSET(BULK) and other options but I'm not reading data from a file but from another table in the same database so that doesn't seem to apply.
    Any ideas?
  2. Adriaan New Member

    It's usually a good idea to insert large numbers of rows in smaller batches.
    Does the target table have any indexes? Does it have a PK?
  3. TommCatt New Member

    There's nothing you can do about the table scan because, well, you actually are scanning the entire table. If there are any indexes on the target table, you will get better performance on a bulk move if you disable them during the insert and enable them afterward. An index will speed up queries at the expense of making inserts a little slower. This is normally a great trade-off as tables are generally queried a lot more often than written to.
    You can also break up the one big insert into a series of smaller inserts. This will not speed up the process -- it will just release the lock on the table now and then so other processes will have an opportunity to access it between insert operations. Only do this, though, if necessary.
    Something you don't mention but could impact the operation. Do you remove the rows from the staging table after the transfer? I assume so -- it is a staging table after all. I only ask because I have seen where the target table was emptied and completely refilled every day from the staging table. This worked fine for the first year or so, but as the staging table grew to millions of rows, this operation starting impacting the operation of the system. A simple change to transfer only the rows that had changed since the last update worked wonders.

Share This Page