Collections:
Measuring Performance of INSERT Statements in SQL Server
How To Measure Performance of INSERT Statements in SQL Server?
✍: FYIcenter.com
When indexes are defined for a table, each time a new row is inserted to the table, all the indexes must be updated. This extra update work could slow down the insert statement.
To find out how much slower the INSERT statements will be on tables with indexes, you need to measure the performance of INSERT statements on tables without indexes. This tutorial shows you how to measure the time spend on inserting 100000 rows to fyi_links without indexes:
USE FyiCenterData; GO -- Drop the old table, if needed DROP TABLE fyi_links; GO -- Create a table with no indexes CREATE TABLE fyi_links ( id INT, url VARCHAR(80) NOT NULL, notes VARCHAR(1024), counts INT, created DATETIME NOT NULL DEFAULT(getdate()) ); GO -- Empty the table if needed DELETE FROM fyi_links; GO -- Performance test of INSERT DECLARE @start_time DATETIME, @end_time DATETIME; SET @start_time = GETDATE(); INSERT INTO fyi_links 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: 18013 -- Second time (100000 row(s) affected) Milliseconds used: 736 -- Third time (100000 row(s) affected) Milliseconds used: 780
⇒ Index Slowing Down INSERT Statements in SQL Server
⇐ Creating a Large Table with Random Data for Indexes in SQL Server
2016-11-13, 2590🔥, 0💬
Popular Posts:
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...
How To Insert New Line Characters into Strings in SQL Server Transact-SQL? If you want to break a st...
How To Use SQL*Plus Built-in Timers in Oracle? If you don't have a stopwatch/timer and want to measu...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...