SQL DELETE performance
delete from a A where a.ID = 132.
Table A contains about 5000 records and A.ID is the primary key in table A. But it takes a long time to delete. Sometimes its waiting time too. This table contains three indexes referenced by three foreign keys. Can anyone explain to me why it takes a long time even though we delete based on the primary key. And please tell me how can this problem be optimized ...?
source to share
Obviously it won't take long. However, there isn't enough information here to figure out exactly why. I can tell you that you should focus on foreign keys.
This can slow things down if they place constraints on other, much larger tables. You might also find out that your timeouts are due to integrity checks that prevent deletion (then it begs the question why you don't get exceptions instead of timeouts).
The next step is to remove foreign keys and then check the performance. Then add each one at a time and test the performance as you go.
Are there any other operations (eg Inserts, Selects, Updates)?
source to share
Possible reasons:
1) cascade delete operations
2) trigger (s)
3) the type of the primary key column is something other than an integer, thus causing a type conversion on each pk value for comparison. this requires a full table scan.
4) does your query actually end with a dot as you posted it in the question? if so, the number can be thought of as a floating point number instead of an integer, thus causing a type conversion like 3)
5) your delete request is waiting for some other slow request to release the lock
source to share
First thought: foreign key indexes?
- This refers to the cascading deletions mentioned
- All child table clutches are checked, and if you only have 500,000 child rows it might take a while, of course ...
Second thought: are triggers firing?
- On this table or on child tables or trying to cascade through code, etc.
- God forbid, the cursor for each line in DELETED ...
source to share
Like other observers, foreign keys are likely suspects.
First, because ON DELETE CASCADE can gain traction if dependent tables in turn refer to other tables that can be referenced in turn, and so on.
Second, because other users might have row locks that need to be removed. This is the most likely reason for timeouts. It is possible that this will depend on the taste and version of your database. For example, older versions of Oracle (<= 8.0) needed to lock the entire dependent table if no foreign key columns are indexed.
source to share