Index Slowing Down INSERT Statements in SQL Server

Q

Does Index Slows Down INSERT Statements in SQL Server?

✍: FYIcenter.com

A

If you want to see the impact of indexes on INSERT statements, you can repeat the same insert script on the table "fyi_links" of the same structure with two indexes: one non-clustered index on column "url" and one non-clustered index on column "counts". See the tutorial exercise below:

USE FyiCenterData
GO

-- Drop the old table, if needed
DROP TABLE fyi_links_indexed;
GO

-- Create a table
CREATE TABLE fyi_links_indexed (
  id INT,
  url VARCHAR(80) NOT NULL,
  notes VARCHAR(1024),
  counts INT,
  created DATETIME NOT NULL DEFAULT(getdate())
);
GO

-- Create two indexes
CREATE INDEX fyi_links_url ON fyi_links_indexed (url);
CREATE INDEX fyi_links_counts ON fyi_links_indexed (counts);
GO

-- Empty the table if needed
DELETE FROM fyi_links_indexed;
GO

-- Performance test of INSERT
DECLARE @start_time DATETIME, @end_time DATETIME;
SET @start_time = GETDATE();
INSERT INTO fyi_links_indexed 
   SELECT id, rand_string, REVERSE(rand_string), 
      rand_integer, rand_datetime
   FROM fyi_random
SET @end_time = GETDATE();
PRINT 'Milliseconds used: '+CONVERT(VARCHAR(20),
   DATEDIFF(MILLISECOND,@start_time,@end_time));
GO

-- First time
(100000 row(s) affected)
Milliseconds used: 15516

-- Second time
(100000 row(s) affected)
Milliseconds used: 2766

-- Third time
(100000 row(s) affected)
Milliseconds used: 3186

Comparing the result between this tutorial and the previous tutorial, two indexes make 100000 insert statements about 4 times slower. Note that the first measurement of both tests seems to be affected by the database engine caching.

 

Understanding and Managing Indexes in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-13, 800👍, 0💬