Solved

Speed of Delete/Triggers

Posted on 2008-10-02
21
831 Views
Last Modified: 2012-05-05
I have a SQL 2005 database.

It has a main table, with 11 other related tables.  Each table has a common field.  There are triggers on the main table such that when a corresponding record is added to the main table, one is also added to the other 11, and when a Delete is done from the main table, the corresponding delete is done from the other 11.

When I run a query to delete a single record from the main table, the query takes about 2-3 seconds, whereas running a very complex Select query might only take a few mincroseconds.

Is the slowness due to the multiple triggers?  Would putting all of the triggers into a single trigger speed it up?  Would that be advisable?

Each table has an index on the common field, so I would not expect the slowness to be caused by that.  Is there another setting I can adjust that would affect the speed?
0
Comment
Question by:wsturdev
  • 9
  • 8
  • 3
  • +1
21 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22628448
is there a index on the related table on that foreign key field?
can you show the trigger code?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 22628597
This the SQL Syntax for the index on the main table (Tbl_General) on the common field called Apps_Cat_ID:

ALTER TABLE [dbo].[Tbl_General] ADD  CONSTRAINT [IX_Tbl_GeneralAppsCatID] UNIQUE NONCLUSTERED
(
      [Apps_Cat_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Every one of the related tables have an identical index.

Here is the code of one of the triggers attached to Tbl_General for the first of the related tables:

CREATE TRIGGER [Delete APER Record] ON [dbo].[Tbl_General]
AFTER DELETE
AS
DELETE from [dbo].[Tbl_APER]
WHERE [dbo].[Tbl_APER].Apps_Cat_ID in (select Apps_Cat_ID FROM deleted )

There is an identical trigger in Tbl_General for the remaining 10 related tables.

I just tried combining all of the triggers into one and it was just as slow.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22628838
If there is a relation between your main table and the others like the pk from main is refferenced by a foreign key in the others then you can set up a cascade delete in the main table, which will automatically do the deletes for you without the need of a trigger.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22628957
If you did it with a trigger you should have created only 1 trigger that will perform all the deletes for you in 1 shot, that is one trigger with 11 delete statements. The way you implemented the deletion has to fire 11 triggers! That's a very bad practice!
0
 
LVL 1

Author Comment

by:wsturdev
ID: 22629257
Zberteoc --

<If there is a relation between your main table and the others like the pk from main is refferenced by a foreign key in the others then you can set up a cascade delete in the main table, which will automatically do the deletes for you without the need of a trigger.>
I am pretty sure this is not the case, but will have to look into this later this evening, so I understand it better.
<If you did it with a trigger you should have created only 1 trigger that will perform all the deletes for you in 1 shot, that is one trigger with 11 delete statements. The way you implemented the deletion has to fire 11 triggers! That's a very bad practice!>
Okay, I understand about the bad practice.  I did put all deletes in a single trigger (disabled all of the original ones) and it was just as slow.  Is that expected?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22629862
Since the trigger runs in the context of the original transaction, does it make sense from a business perspective on your end to have your trigger insert the key fields from the records into a table?  Then, later (perhaps after hours), you could have a sql job just go through and delete the necessary records based on the values you put into the table from the trigger?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 22630941
chapmandew -- That is an interesting and tempting idea, but it won't work in my case.
There are a number of queries that pull from the 11 related tables without first checking to see if all the records in them have properly corresponding records in the main table.  In other words, if a user caused the deletion of record ID 2 of the main table at 8am, and the corresponding record ID 2 in one of the related tables was not deleted until 11pm, then all queries run between those two times against the related table would pull one record too many.
And, there is really no "after hours" for this database.  It is used around the clock and around the world.
Zberteoc -- I will continue looking into the foreign key issue in the morning.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22631858
can you run the delete with retrieving the explain plan?

can you also try the JOIN sytnax:
CREATE TRIGGER [Delete APER Record] ON [dbo].[Tbl_General]

AFTER DELETE

AS

DELETE t

  FROM [dbo].[Tbl_APER] t

  JOIN deleted d

    ON t.Apps_Cat_ID = d.Apps_Cat_ID

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22632628
how many indexes you have on those tables?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22632635
<<I am pretty sure this is not the case, but will have to look into this later this evening, so I understand it better.>>

What do you mean by that? It is not the case for what?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:wsturdev
ID: 22634724
Zebertoc --
<What do you mean by that? It is not the case for what?>
Sorry -- I misread the first part of your comment beginning "If there is a relation..." as "Is there a relation..."  I was reading and responding quickly because I was late being somewhere else, and did not read carefully.
<how many indexes you have on those tables?>
There are 8 indexes on the main table and 2 on each of the 11 related tables.
angleIII --
<can you run the delete with retrieving the explain plan?>
How do you do that? (and did you mean to say "without"?)
<can you also try the JOIN sytnax>
You example shows a join between the main table and the first related table.  HOw do I expand that to include the other 10 related tables?  Assume they are called Tbl_R_2, Tbl_R_3, etc, through Tbl_R_11.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22634757
<can you run the delete with retrieving the explain plan?>

before you run the query in sql management studio, activate "include actual execution plan" in the Query menu.

the delete to run is the delete on [dbo].[Tbl_General] for a test row

<can you also try the JOIN sytnax>
>You example shows a join between the main table and the first related table.
yes, you will have to delete from 1 related table at once.

guessing the related table names:
CREATE TRIGGER [Delete APER Record] ON [dbo].[Tbl_General]

AFTER DELETE

AS

DELETE t

  FROM [dbo].[Tbl_APER] t

  JOIN deleted d

    ON t.Apps_Cat_ID = d.Apps_Cat_ID
 

DELETE t

  FROM [dbo].[Tbl_APEX] t

  JOIN deleted d

    ON t.Apps_Cat_ID = d.Apps_Cat_ID
 

DELETE t

  FROM [dbo].[Tbl_APEY] t

  JOIN deleted d

    ON t.Apps_Cat_ID = d.Apps_Cat_ID
 

DELETE t

  FROM [dbo].[Tbl_APES] t

  JOIN deleted d

    ON t.Apps_Cat_ID = d.Apps_Cat_ID

Open in new window

0
 
LVL 1

Author Comment

by:wsturdev
ID: 22634809
Zberteoc --
<If there is a relation between your main table and the others like the pk from main is refferenced by a foreign key in the others then you can set up a cascade delete in the main table, which will automatically do the deletes for you without the need of a trigger.>
Is there also such a thing as a "cascade insert"?  I am currently using a trigger to add corresponding records to the 11 related tables whenever a record is added to the main table.  Is that appropriate, or should I be considering another method?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22634974
Please do one thing:

1. Write one delete statement for each of the tables for the same pk in the main table. Add the statistics time on before the deletes so it will show the duration in milliseconds in the Message tab.

2. Run a delete only for the main table (select that part of the script up) for a key value while the trigger is on and check the duration in Message tab.

3.Disable the DELETE triger in main table and run all the script (all the deletes) for another key value and see how long it takes in Message tab. Make sure you give the @Apps_Cat_ID variable each time a proper value.

If it takes the same or close amount of time for both tests there is nothing you can do. The DELETE and INSERT performance is affected by the number of indexes you have one the tables.

Come beck with the conclusions.
DECLARE @Apps_Cat_ID INT

SET @Apps_Cat_ID=<value_here>
 

SET STATISTICS TIME ON
 

-- main table delete

DELETE FROM [dbo].[Tbl_General]	WHERE Apps_Cat_ID=@Apps_Cat_ID

-- other tables delets

DELETE FROM [dbo].[Tbl_APER]	WHERE Apps_Cat_ID=@Apps_Cat_ID

-- ... the rest of the tables here ... 

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22635019
There is no such thing as a "cascade insert" as it doesn't make much sense to insert only the key value of a row as oppose with the delete where it can be based on one value only.

The cascade delete purpose is to enforce the referential integrity by deleting all the children for a given key value in a parent table so it will keep from having orphan entries in the children tables. With insert you don't need that because usually the parent can exist on its own without having any children when created.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22635083
Of course, the cascade delete is the way to go as I assume it will be faster anyway. For inserts I don't think you have to worry as long as you have the relations in place along with the cascade delete turned on.

The relation will prevent from inserting rows in the children tables before having a parent. You make the connection between parent and children in the business layer usually, that is the application that actually does the inserts. At the database level you only need to implement the proper relation and constraints.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 22635538
The Delete from the main table requires some extra parameters.  The triggers only need the single key field.  I ran this delete statement:
DELETE FROM tbl_general WHERE ((KT_Entry_Type is null or KT_Entry_Type = '') or (application_name is null or application_name='')) and (Entry_Type = 'KT Only') and (Apps_Cat_ID >=1001 and Apps_Cat_ID <=1500)
First, I used the 11 individual triggers with 1 Delete statement each and got this eventual message:
"The query has exceeded the maximum number of result sets that can be displayed in the Execution Plan pane. Only the first 250 result sets are displayed in the Execution Plan pane. "
Then, I switched to a single trigger with 11 Delete statements and got the same eventual message:
"The query has exceeded the maximum number of result sets that can be displayed in the Execution Plan pane. Only the first 250 result sets are displayed in the Execution Plan pane. "
Each time it took 13 seconds -- I assume this was because of the overhead of trying to display the execution plan.
 
0
 
LVL 1

Author Comment

by:wsturdev
ID: 22635574
<can you also try the JOIN sytnax>
I set up a single trigger with the joins, and it looks like it runs at the same speed as the trigger with the 11 Delete statements without the joins.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 22636335
<Please do one thing>
I set up the following 3 scenarios, each with Statistics ON:
1. Single Trigger with Individual Statements - no JOINs
2. Single Trigger with Individual Statements - with JOINs
3. Individual Delete Statements with the trigger disabled
I made sure that in each test, I was trying to delete the exact same number of records.
I took the results from the Messages window for each and moved then into an Excel workbook onto 3 different tabs.  Then, in a fairly unscientific process, I deleted all rows that did not have mention of CPU Time or Elapsed Time.  Then I summed the CPU Time and Elapsed Time for each scenario and this is what I got:
1. Trigger with NO joins:           CPU Time = 781ms       Elapsed Time = 3236ms
2. Trigger WITH joins:               CPU Time = 1125ms     Elapsed Time = 3581ms
3. Deletes with NO Trigger      CPU Time = 926ms        Elapsed Time = 3495ms
<If it takes the same or close amount of time for both tests there is nothing you can do.>
I think you can argue that those results are pretty close, with a slight edge to number 1.
But, now I have another question:  If I am attempting to delete 1 record from each of 12 total tables, why does the messages window show, in the case of each Scenario, a collection of between 2362 and 2378 "SQL Server Execution Times:" and "SQL Server parse and compile time:" statements?
I realize this probably relates to the apparently massive Execution Plan I reported earlier, and granted, I am only talking about 2 seconds of wait time for the Deletes to take place, but what in the world is gong on?
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 22636646
that is because when you run the script with all the deletes it will show the duration for each delete statement separately. You can sum the duration to find the result.

Anyway, as I said, the best way to do this it is with cascade delete but you will not get much better time then the script with 12 deletes. This is what the server needs to complete them.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 22636912
OK, I will try to set up a cascade delete over the weekend, just to satisfy my curiosity about it.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
search for a string in all tables 4 15
Sql query 34 22
Increasing Identity length in sql server 4 20
shrink datafile Sql server 4 17
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now