Cascade delete poor performance. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Cascade delete poor performance.

Hello, I have two tables linked by a foreign key, the first table identify a snapshot of counters with an ID, the second one the counters themself. I have something like 2 000 000 snapshots and 100 000 000 coutners records. In order to purge the snapshot a cascade delete was defined… but the performance turned to be very slow. If I remove the cascade delete and do the deletion of the counters "manually" and remove the cascade delete from the relation definition, the performance are something like 5 to 10 times better. Did someone already encounter such a strange behaviour ? For info the system is using : SQL Server 2000 SP3a on a cluster. Franck.
A first element in case someone is interresting in this topic… so I reply to myself !
I also detecte the same issue on some few other tables in my database (poor delete performace on table using cascade delete). I eventually check the Execution plan with query analyser. Here were my remarks:
* When the Server is searching for the records in the sub-table, it is using a Merge Join / Inner Join.
* The predictied number of rows then jump to values like 100 of billions… but the number of rows reported by rowcount can sometimes even be 0 !
* The Join algorithm is different depending on the tables. sometimes Merge Join, sometimes Hash Join.
* When I delete the records of the sub-table manually, the Join algorithm is not the same, it is using a Hash instead of a Merge. Here were my actions:
* First try to re-computer the statistics for all the involved tables, with full scan to try to change the Join used on a cascade delete: Failure.
* Secondly I use the OPTION (HASH JOIN) to force the server to use this option : I gain 50% performance. Well, see you for the next episode. Franck.
Is the counter data derived from existing data? In that case it might be quicker to truncate the counter data and insert new counter data.
No, Let’s say the counters are some kinds of non resetable audit register that are used to consolidate some financial revenue reports. The usual transaction/counters cross check.
The counters cannot be truncated at this stage of the project (already in production !). Franck.
]]>