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, 2451🔥, 0💬
Popular Posts:
How To Generate CREATE VIEW Script on an Existing View in SQL Server? If you want to know how an exi...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
How To Connect the Oracle Server as SYSDBA in Oracle? This is Step 4. The best way to connect to the...
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...