Re: performance using UPDATE vs DELETE/INSERT

Well, I don't know if nulling a byte really free space... But depending the update you're trying a unload/delete/insert schema is faster than just an update. I have expereciences with updates with correlated subquerys, for example if you want to delete some records in one table depending only if they exists in another one and the existence test involves more than one field (ie a compund query). This scenario was way to slow. In the end it was faster unloading the data (including its changes), deleting the rows (not using correlated querys) and finally loading the unloaded data. The problem at large was having to use correlated querys in the update (ie. exists). If your schema permits and you can test for existence could be divided in discrete non-correlated-updates then you won't have much trouble. Special note: This happen some years ago with Online 5.x. Maybe now IDS 9.x could handle this cases better. It would be nice if you could do an update/delete based on a join; this could be done in other DB and surely this may alleviate a bit this situations and as much I know IDS doesn't support this. Chucho! -----Original Message----- From: "DCP" <dcp_news@nyed.uscourts.gov> To: informix-list@iiug.org Date: Wed, 17 Nov 2004 11:50:52 -0500 Subject: 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 Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com sending to informix-list