- Local time
- Today, 08:18
- Joined
- Feb 28, 2001
- Messages
- 27,366
We cannot forget that the issue is an UPDATE with multiple indexes. Remember that updating key fields has to update the indexes as well. So for clarification, on the "real" table (as opposed to any testing table), how many records are involved? (Not how many are updated but how many records are in the table?) And in that table, how many fields are indexed?
We had an absolutely abysmal 15-way JOIN in a Navy personnel database. When we were doing global updates (i.e. working on the table as a whole with the potential for >10,000 updates), we took the tactic of taking off all indexes that weren't involved in the WHERE clause of the UPDATE statement and then recreating the indexes afterwards. This was for ORACLE, but past a certain point they are all the same in performance. I.e. when you are dinking with >10,000 of anything, the speed difference between different DBMs is a few percent apart. They ALL have to find records, update records, update indexes, etc. Most of them use some variant of B-trees. Past a certain number, the other load on the system will outweigh the differences in the way they work.
So... with a lot of indexes, an update of a really large number of records will be inefficient by a lot. Removing indexes will reduce the amount of work required to update those indexes, and for some reason, it is often faster to dissolve and later rebuild the indexes than it is to dynamically update them during the query. I suspect the condition technically known as "thrashing" has a lot to do with the speed issue.
We had an absolutely abysmal 15-way JOIN in a Navy personnel database. When we were doing global updates (i.e. working on the table as a whole with the potential for >10,000 updates), we took the tactic of taking off all indexes that weren't involved in the WHERE clause of the UPDATE statement and then recreating the indexes afterwards. This was for ORACLE, but past a certain point they are all the same in performance. I.e. when you are dinking with >10,000 of anything, the speed difference between different DBMs is a few percent apart. They ALL have to find records, update records, update indexes, etc. Most of them use some variant of B-trees. Past a certain number, the other load on the system will outweigh the differences in the way they work.
So... with a lot of indexes, an update of a really large number of records will be inefficient by a lot. Removing indexes will reduce the amount of work required to update those indexes, and for some reason, it is often faster to dissolve and later rebuild the indexes than it is to dynamically update them during the query. I suspect the condition technically known as "thrashing" has a lot to do with the speed issue.
