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:

  • Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
  • Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7600:)

Oracle:

  • Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta

System:

  • Windows 7 Home Premium, 32-bit
  • CPU AMD E-350 1.6 GHz, dual core
  • RAM 3 GB, DDR3-1066
  • HDD - HITACHI HTS725032A9A364, 320 GB, 7200 rpm, SATA

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

Discussion

, November 09, 2012 8:54 am

i tried it on sql server using a #tempTable and using begin transaction took 27 seconds for insert the 100 000 rows, and not using transaction took 17 seconds

You could leave a comment if you were logged in.