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.
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 |
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;
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 |
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 |
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.
Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012
Discussion
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