performance using UPDATE vs DELETE/INSERT

IDS 7.31.UC4 & IDS 9/40/UC4: We have a relatively large table (1.2 million rows) with a BYTE field that can be as large as 20Meg or so. Every few hours, the table is scrubbed of unneeded BYTE values. The record is retained, but the BYTE field is nulled out. This process takes about 10 minutes per run. Question: Our central office insists that Informix does not reclaim space freed up during an update and therefore they must delete the row, then add it back in again. Plus, they claim that the delete/insert is faster than an update. My understanding was that Informix does reclaim such space and the fragmentaiton of the data and indices caused by the delete/insert process more than offset any temporary performance gain during the scrub itself. I've been asked to prove this by building some benchmark, but I'm not sure how to show long-term performance is changed or how fragmentation of the extents my be best measured. Does anyone have any knowledge of how the two mothods compared as far as short term and long term performance? -- DCP

DCP wrote: > IDS 7.31.UC4 & IDS 9/40/UC4: Hardware? Not that it makes much difference. > We have a relatively large table (1.2 million rows) with a BYTE field that > can be as large as 20Meg or so. Every few hours, the table is scrubbed of > unneeded BYTE values. The record is retained, but the BYTE field is nulled > out. This process takes about 10 minutes per run. Are the BYTE blobs in a blob space or 'IN TABLE'? If you don't say, they're IN TABLE. If IN TABLE blobs are 20 MB or so, then each blob occupies 10,000 or so pages - quite a lot. You'd probably do better with a blob space configured with a larger page size. Blobs in blob spaces don't thrash the logical log; blobs IN TABLE do. > Question: Our central office insists that Informix does not reclaim space > freed up during an update and therefore they must delete the row, then add > it back in again. Plus, they claim that the delete/insert is faster than an > update. Hmmm...can I be bothered to read the manuals to verify what I'm going to say? No. OK - here's what I remember; check with the manuals before making life-endangering decisions based on what I say. Case 1: blobs in a blob space - the space associated with the old value of the blob is not released until the next time the logical logs are backed up, when the old blob value is copied out to the backup medium (nominally a tape drive) and then the space is freed. The advantage of this is that the blob does not have to be stored in the logical log, so it doesn't fill the logs as quickly. The downside is that the space is not available for reuse immediately. Case 2: blobs in table - AFAICR, the old record, including the blob information, would be written to the logical log (I hope you've got a big logical log buffer and big logs) just as with any other data. However, just as with anything else, once the disk space associated with blob is allocated to the table, that extent is not released for other tables to use - though the table will reuse it when it needs more space. So, there is some justice in what your central office says - but the space is not lost permanently. Whether delete/insert is faster than update is hard to assess; the operational workload is similar - the double operation means more index maintenance, so an update should normally be quicker than delete and insert. If you don't have a logged database, the rules change, of course. > My understanding was that Informix does reclaim such space and the > fragmentaiton of the data and indices caused by the delete/insert process > more than offset any temporary performance gain during the scrub itself. Yes - Informix reuses the space - but it does not reclaim it for general use. It will only be used by the table with the blob column. I don't know that delete plus insert will cause fragmentation particularly, but if the only column being changed is the blob column (or other non-indexed columns), then the DELETE/INSERT combination forces two changes in each index where the UPDATE causes none. If some of the indexed columns are changed, then those indexes need to be modified, which is about as expensive as DELETE/INSERT. There's probably an extra round-trip between application and server for the DELETE/INSERT pair too. I would not worry too much about the fragmentation within an extent. If I was going to worry about it, I'd be splitting blobs into a blob space, and indexes would be detached. > I've been asked to prove this by building some benchmark, but I'm not sure > how to show long-term performance is changed or how fragmentation of the > extents my be best measured. This fragmentation is not - I think, unless I'm misinterpreting the question - related to ALTER FRAGMENT type fragmentation. Rather, it is the internal fragmentation and possible wasted disk space. I'd do some investigation of how big the blobs are, and how many appear in a given period of time, and how many are deleted (nullified) in the same period of time. On the average, I guess those numbers are the same - so you aren't gradually gaining unnullified blobs. Then go about creating a blob loader and updater program - possibly based on the example blob manipulation programs that accompany SQLCMD, available at the IIUG Software Archive - and see how your example table grows as you run half-a-dozen inserts followed by half-a-dozen purges - simulating the cycle where records are added with blob, then later modified to remove the blobs. You don't indicate how long the de-blobbed records are kept - which will control whether the table is continually growing or whether it has a finite size. For speed, you can use the timing code that comes with SQLCMD. For disk use, you'll need to consider how you want to measure it, but 'oncheck -pt' might be part of the answer, or you could look in sysmaster. > Does anyone have any knowledge of how the two mothods compared as far as > short term and long term performance? Did anyone consider having a table with just two columns - an integer column that is a foreign key referencing the main table and a BYTE column containing the data. You can simply insert a new record in the table when data arrives, and purging the table is a simple delete. The only snag is that you might need to code an outer join if you ever want to see the data including the blob values where it is available. You could even do that in a view. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/