Speed of Delete/Triggers

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?
LVL 1
wsturdevAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
is there a index on the related table on that foreign key field?
can you show the trigger code?
0
wsturdevAuthor Commented:
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
ZberteocCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ZberteocCommented:
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
wsturdevAuthor Commented:
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
chapmandewCommented:
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
wsturdevAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
ZberteocCommented:
how many indexes you have on those tables?
0
ZberteocCommented:
<<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
wsturdevAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
<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
wsturdevAuthor Commented:
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
ZberteocCommented:
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
ZberteocCommented:
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
ZberteocCommented:
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
wsturdevAuthor Commented:
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
wsturdevAuthor Commented:
<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
wsturdevAuthor Commented:
<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
ZberteocCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wsturdevAuthor Commented:
OK, I will try to set up a cascade delete over the weekend, just to satisfy my curiosity about it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.