|
Simple delete query is taking more than 15 hrs. Table contains around 6 852 968 records. Query is : Find below for the Create table query of that table. Pls provide your valuable suggestions to close this issue. Thanks in advance.
(comments are locked)
|
|
Any foreign keys to from this table? I have the same problem. When I try to delete one record out of 200 million, it takes about 5 hours even if I use the PK column. When I disable/remove the FK constraints, it takes about 1 second.
(comments are locked)
|
|
Also, do you have CASCADE_DELETE activated on the table?
(comments are locked)
|
|
Just another thought, you said that you were expecting to delete 3K rows from a table that has 6.8M rows and you said was taking 15 hours, as pointed out the column is indexed. When was the last time that the statistics were updated? Are there any delete triggers on the table that might be causing issues?
(comments are locked)
|
|
nonclustered located on PRIMARY >> file_syscode Well, you've got an index on that field, so it should be quick. 1) Make sure all indexes (or at the very least this one and the clsutered index) have been recently rebuilt. 2) Other than that it will be the Constraints - which all have to be checked. Rebuild the indexes that the Constraints are relying on if that has not been done recently.
(comments are locked)
|
|
Query is expected to delete around 3000 records only. Following are the indexes created on the table.......... nonclustered located on PRIMARY >> transaction_date nonclustered located on PRIMARY >> customer_syscode nonclustered located on PRIMARY >> prematch_syscode nonclustered located on PRIMARY >> file_syscode clustered, unique, primary key located on PRIMARY >> mf_tran_syscode nonclustered located on PRIMARY >> customer_syscode, transaction_date, transaction_type, scheme_option Also the table is having some more constraints...... I am getting following msg, when i am trying to paste the create table scripts in the forum Please either edit your original question, or add a Comment to the Answer. Please don't clutter up the site with comments entered as an Answer - because it isn't an Answer, right?!
Oct 27 '09 at 06:59 AM
Kristen ♦
(comments are locked)
|
1 2 next page »
Please edit your question to include the create table statement. Also indicate whether file_syscode has an index and how many rows you think should be deleted by this statement.