Extremely slow delete 1

[Maim]

Hi,

I have a table (ParentVal) with four child tables (ParentValA, ParentValB, ParentValC, ParentValD) each with foreign key constraints.

ParentVal has 15000 records
ParentValA has 1000000 records
ParentValB has 2000000 records
ParentValC has 15000000 records
ParentValD has 30000000 records

If I attempt to delete a record from ParentVal, which has no associated records in the child table, it takes 45 seconds, which is unacceptable. Exec plan shows clustered index scans on the child tables and clustered index seek on the ParentVal table.

If I disable the fk constraints on the child tables to the parent, the same delete takes less than a second but I don't want to disable foreign keys!

Can anyone offer a suggestion as to how I should proceed?

Thanks in advance!

-----------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook

 

[Maim]

Bump!

Anyone?

-----------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook

 

[gmmastros]

Each of the child tables should have an index on the column that matches the parent table. After putting the index, you should notice that you get 'index seek' instead of clustered index scan.

If you are using sql 2000, you can right click the 'clustered index scan' in the execution plan, click 'manage indexes', and add one for the foreign key column.

By doing this, you are also likely to speed up other queries.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Maim]

Hi,

The child tables have ParentValID as a clustered index. Should the index be switched for a non-clustered one?

Thanks


-----------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook

 

[gmmastros]

Probably not. Clustered indexes have better performance than non-clustered indexes.

How are you deleting the child records. Are you doing cascade deletes or are you using a stored procedure to do this?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Maim]

I'm deleting the children before deleting the parent records. Nonetheless, it still runs a clustered index scan on the child records when deleting the parent. Do you know of a way to force index use on foreign key checking?

Incidentally, I ran some tests on my beta db.

with foreign key on the child tables to the parent:
1. deleting from children first took milliseconds for each child
2. deleting the parent record took 2 minutes, ci scan on each child

disabled the foreign key:
1. deleting the children first took milliseconds for each child
2. deleting the parent took milliseconds, obviously no use of the child tables in the exec plan...

Thanks

-----------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook

 

[gmmastros]

I have a couple ideas. First... It could be a delete trigger causing the problem.

For each of your child tables, run this...

[tt][blue]sp_helptrigger '[!]TableName[/!]'[/blue][/tt]

It could also be a 'cascading delete' issue. So... also run this...

[tt][blue]sp_helpconstraint '[!]TableName[/!]'[/blue][/tt]

If either of these statements return data, post it here.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Maim]

Ah, sometimes we overlook the simplest things!

I was rechecking the indexes on the child tables and noticed the clustered index was created as a covering index (3 fields) with the aforementioned foreign key. So what happens when the foreign key runs its check? It won't use the index fully, hence ci scan!

I added a nonclustered index on the foreign key field alone and boom, milliseconds to delete the records!

Sorry gmmastros, I didn't verify your suggestion above about the indexes

Thanks nonetheless!




-----------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook