Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Measuring Performance of INSERT Statements

By: FYIcenter.com

(Continued from previous topic...)

How To Measure Performance of INSERT Statements?

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

(Continued on next topic...)

  1. What Are Indexes?
  2. How To Create an Index on an Existing Table?
  3. How To View Existing Indexes on an Given Table using SP_HELP?
  4. How To View Existing Indexes on an Given Table using sys.indexes?
  5. How To Drop Existing Indexes?
  6. Is the PRIMARY KEY Column of a Table an Index?
  7. Does the UNIQUE Constraint Create an Index?
  8. What Is the Difference Between Clustered and Non-Clustered Indexes?
  9. How To Create a Clustered Index?
  10. How To Create an Index for Multiple Columns?
  11. How To Create a Large Table with Random Data for Index Testing?
  12. How To Measure Performance of INSERT Statements?
  13. Does Index Slows Down INSERT Statements?
  14. Does Index Speed Up SELECT Statements?
  15. What Happens If You Add a New Index to Large Table?
  16. What Is the Impact on Other User Sessions When Creating Indexes?
  17. What Is Index Fragmentation?
  18. What Causes Index Fragmentation?
  19. How To Defragment Table Indexes?
  20. How To Defragment Indexes with ALTER INDEX ... REORGANIZE?
  21. How To Rebuild Indexes with ALTER INDEX ... REBUILD?
  22. How To Rebuild All Indexes on a Single Table?
  23. How To Recreate an Existing Index?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...