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?