SQL Server - INSERT Performance with Autocommit and Using Transactions

Let's consider how autocommit mode affects the INSERT performance in Microsoft SQL Server and whether using transactions (turning autocommit off) can help improve the performance.

SQL Server - INSERT in Autocommit Mode

By default, SQL Server works in autocommit mode, so it commits the transaction after each DML or DDL SQL statement. Let's measure the insert performance in the autocommit mode:

SQL Server: 

  -- Drop and create a test table
  IF OBJECT_ID('sales', 'U') IS NOT NULL
    DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id INT,
     created DATETIME
  );
  GO
 
  SET NOCOUNT ON
 
  -- Run a loop to insert 100,000 rows
  DECLARE @i INT = 1,
                  @start DATETIME = GETDATE();
 
  WHILE @i <= 100000 BEGIN
  INSERT INTO sales (id) VALUES (@i);
  SET @i = @i + 1;
  END
 
  -- Output the execution time in seconds 
  SELECT DATEDIFF(ss, @start, GETDATE()); 
  GO

I executed this batch multiple times, and each time I got the following results:

Execution time Insert rate
91 seconds 1099 rows per second

SQL Server - INSERT using Transactions

Now let's turn autocommit off, and insert 100,000 rows and issue a commit after each 10,000 row:

SQL Server: 

  -- Drop and create a test table
  IF OBJECT_ID('sales', 'U') IS NOT NULL
    DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id INT,
     created DATETIME
  );
  GO
 
  SET NOCOUNT ON
 
  -- Run a loop to insert 100,000 rows
  DECLARE @i INT = 1,
                  @start DATETIME = GETDATE();
 
  WHILE @i <= 100000 BEGIN
 
    -- Start a transaction
    IF @i % 10000 = 1 
       BEGIN TRANSACTION;
 
     INSERT INTO sales (id) VALUES (@i);
     SET @i = @i + 1;
 
    -- Commit after each 10,000 row
    IF @i % 10000 = 0
      COMMIT;
 
  END
 
  -- Output the execution time in seconds 
  SELECT DATEDIFF(ss, @start, GETDATE()); 
  GO

Again I executed this batch multiple times, and each time I got the following results:

Execution time Insert rate
3 seconds 33333 rows per second

Note that I got the insert performance 30 times faster using transactions compared with autocommit;

Oracle - INSERT using Transactions

Now let's compare the results with Oracle installed on the same system. Oracle does not autocommit each DML statement, so I will insert 100,000 rows and issue a commit after each 10,000 row:

Oracle: 

  -- Drop and create a test table
  DROP TABLE sales;
 
  CREATE TABLE sales
  (
     id NUMBER(10),
     created DATE
  );
 
  -- Run a loop to insert 100,000 rows
  DECLARE i NUMBER(10) := 1;
                 startd DATE := SYSDATE;
  BEGIN
    WHILE i <= 100000 LOOP
 
       INSERT INTO sales (id) VALUES (i);
       i := i + 1;
 
      -- Commit after each 10,000 row
      IF MOD(i, 10000) = 0 THEN
        COMMIT;
      END IF;
 
      END LOOP;
 
     -- Output the execution time in seconds 
    DBMS_OUTPUT.PUT_LINE((SYSDATE - startd)*24*60*60);
    END;
    /

This batch was also executed multiple times, and each time I got the following results:

Execution time Insert rate
26 seconds 3846 rows per second

Test Results

I got the following results during the tests:

Database Execution Time (seconds) Insert Rate (rows per second)
SQL Server Autocommit mode 91 1099
SQL Server Transactions (10,000 rows batch) 3 33333
Oracle Transactions (10,000 rows batch) 26 3846

System Information

Performance stats is always relative, and the following software/hardware was used for the tests in this article:

SQL Server:

Oracle:

System:

Also note that all tests were ran multiple times and in different order to get stable results on the system.

Resources

Author

Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012