Collections:
Index Slowing Down INSERT Statements in SQL Server
Does Index Slows Down INSERT Statements in SQL Server?
✍: FYIcenter.com
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.
⇒ Index Speeding Up SELECT Statements in SQL Server
⇐ Measuring Performance of INSERT Statements in SQL Server
2016-11-13, 1817🔥, 0💬
Popular Posts:
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...
What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are mai...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...
What Is SQL*Plus in Oracle? SQL*Plus is an interactive and batch query tool that is installed with e...