Menu
I have a query like
DELETE from tablename where colname = value; which takes awfully long time to execute. What could be the reason? I have an index on colname.
563
So for EVERY select/update/delete, SQL has to do a full scan of the entire table in order to find the records to action. In order to speed up the delete, create a clustered index on the date field.
TRUNCATE is faster than DELETE , as it doesn't scan every record before removing it. TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE . Unlike DELETE , TRUNCATE does not return the number of rows deleted from the table.
There could be several explanations as to why your query takes a long time:
SELECT NULL FROM tablename WHERE colname=:value FOR UPDATE NOWAIT,ON DELETE TRIGGER that does additional work,UNINDEXED REFERENCE CONSTRAINTS pointing to this table (there is a script from AskTom that will help you determine if such unindexed foreign keys exist).
69
it could be that your table is related to multiple tables have a huge row count.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
