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, 1547🔥, 0💬
Popular Posts:
How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL? If you want get the...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
How To Create a Dynamic Cursor with the DYNAMIC Option in SQL Server Transact-SQL? If the underlying...
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...
How To Assign Debug Privileges to a User in Oracle? In order to run SQL Developer in debug mode, the...