Speed of Delete/Triggers

is there a index on the related table on that foreign key field?
can you show the trigger code?

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.

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.

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!

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?

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?

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.

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

Select allOpen in new window

how many indexes you have on those tables?

<<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?

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.

<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

Select allOpen in new window

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?

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 ... 

Select allOpen in new window

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.

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.

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.
 

<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.

<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?

OK, I will try to set up a cascade delete over the weekend, just to satisfy my curiosity about it.