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, 1626🔥, 0💬
Popular Posts:
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...
How To Create a Stored Program Unit in Oracle? If you want to create a stored program unit, you can ...
Is PL/SQL Language Case Sensitive in Oracle? PL/SQL language is not case sensitive: Reserved words a...
How To Round a Numeric Value To a Specific Precision in SQL Server Transact-SQL? Sometimes you need ...
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...