Slow delete performance from Query Table using a secondary index
Article ID: KB0081189
Updated On:
| Products | Versions |
|---|---|
| TIBCO Streaming | - |
Description
When using a secondary key to perform deletes of many rows the performance is much worse than expected.
Issue/Introduction
Design guidance
Resolution
Using a secondary index to match rows for deletion results in rebalancing the Primary Key (PK) index.
When you include a PK column as the index and do a delete based on it, you will get the rows in order and delete in order, which has no additional computation cost because no rebalancing occurs.
Without using the PK index the deletes effectively occur at random within the PK index and a rebalance operation is done for every row deleted. This extra work is what makes performance poor.
When performing bulk deletes make sure the field being used for deletion criteria is a member of the Primary Key.
When you include a PK column as the index and do a delete based on it, you will get the rows in order and delete in order, which has no additional computation cost because no rebalancing occurs.
Without using the PK index the deletes effectively occur at random within the PK index and a rebalance operation is done for every row deleted. This extra work is what makes performance poor.
When performing bulk deletes make sure the field being used for deletion criteria is a member of the Primary Key.
Feedback
Yes
No
Powered by
