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
2016-11-13, 707👍, 0💬
Popular Posts:
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...
How To Define an External Table in a Text File in Oracle? You can use the CREATE TABLE statement to ...
How to download and install Microsoft SQL Server Management Studio Express in SQL Server? Microsoft ...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...