MS SQL Server Interview Question:

Does Index Slows Down INSERT Statements?

Submitted by: Administrator

If you want to see the impact of indexes on INSERT statements, you can repeat the same insert script on the table "ggl_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 GlobalGuideLineDatabase
GO

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

-- Create a table
CREATE TABLE ggl_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 ggl_links_url ON ggl_links_indexed (url);
CREATE INDEX ggl_links_counts ON ggl_links_indexed (counts);
GO

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

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


Submitted by: Administrator

-- 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.
Submitted by: Administrator