Deleting a million rows slows down SELECT statement.
Posted on 2010-09-16
We have six tables that contain an ID. We want to delete all the rows in these tables for the same ID.
We are using the following delete statement: delete from <table name> where id=1;
We do this same delete for all six tables.
Once we do this, any code performing a select on these tables takes a long time to finish. This happens even on empty tables. There will be up to 1 million rows with this same ID. We have tried several things. Performing commits after all the deletes are done, performing a commit after each delete. The only thing that really seems to work is to completely truncate the tables. I have looked online and found that some people move the data they need to keep to a temp table, delete the old table, and rename the temp table to the old table name. We can’t do this because the tables need to be accessible at all times.