Example: Heavy insertion process from OLTP and triggers

Problem: You have to insert a large amount of data (10,000 to 50,000 rows) from a file to a table. You must insert the data row by row because each row is processed by a complex module that cannot be executed at the database level. The table you must insert to is heavily updated by other processes. When you run your inserts (insert by insert), there are many locks and deadlocks plus the insertion procedure takes hours. The table has a trigger on it and the DBA tells you that, except for your process, the trigger is not causing any problems. You look at the trigger and notice that it is well written from a performance perspective because it correctly handles situations where multiple rows are inserted to the table at once. How can you improve performance and avoid locks and deadlocks as much as possible?

Solution: In order to avoid locks, you should insert data as quickly as possible. I suggest inserting the rows into an intermediate table row by row or by preparing a local file and bulk inserting to the transitional table. After this step is complete, it would be quicker to use an "insert … select" or "insert… select top X" to the original table.

How to optimize database triggers

 Home: Introduction
 Part 1: Why use triggers?
 Part 2: How to design and implement triggers
 Part 3: Example: INSTEAD OF triggers versus AFTER triggers
 Part 4: Example: One trigger versus two for the same logic
 Part 5: Example: Nested triggers
 Part 6: Example: Heavy insertion process from OLTP and triggers
 Part 7: Example: Cursors in triggers

ABOUT THE AUTHOR:

Michelle Gutzait Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, and administrative and infrastructure tools development, reporting services and more. Copyright 2006 TechTarget

More from SearchSQLServer.com