Tips: Deleting a Lot of Rows from a Database

Recently, I had to remove a large number of rows, almost 10 million, from a very active database table in a LIVE system. Forget why for now as that is the subject of another post. The basic problem was how to remove 99.8% of the rows without impacting users or removing the few rows that we actually wanted to keep. To make matters worse, the field that was determined to have all the answers didn’t have an index.

Adding an index to the field in question would only solve part of the problem. It would take resources just to apply the key to a table that large, especially one that already had several indices. There are also locking issues and let us not forget the Transaction Log usage. We are talking Gigabytes of space for any new index and for temporary Transaction Log space.

Continue reading

D6 Observations, Part I

This is a first of a sequence of posts of things that I notice as I downloaded the new release last night.

  • Time to stop putting off those upgrades. D6 doesn’t support SQL Server 2000 any more. Your development boxes need to be upgraded as well since Windows 2000 is officially on the scrap pile. In general, not a lot of supported platforms. Upgrading your Content Server to 5.3 sp5 as an intermediate step in order to upgrade all your 3rd party components may have just gone from being a good idea to a required step.
  • Content Server still C++ has for the application. Fabian was correct. The DMCL API, the old C++ foundation for all applications, is now part of the Java DFC. A copy of the DMCL will still be provided for legacy support, but no new features are to be provided.

Continue reading