Re: Dynamic server DELETE performance problem

You'd be better off dropping and re-creating the table, rather than deleting all the rows. Think that's what oracles truncate does, anyway. >>> "Obnoxio The Clown" <obnoxio@hotmail.com> 08/16/00 10:10am >>> From: Keijo Karvonen <keijo.karvonen@_SPAMFILTER_iobox.fi> > Occasionally I want to clear the whole table and currently I'm >using plain SQL like "DELETE FROM <table>" where <table> is the table >name. This takes a whopping 2 hours to complete which is unacceptable. >Dropping table is another possibility which is fast but this will change >in future so that I can't drop the table anymore (in future the whole >table is not deleted but just certaing rows). >My question is this: isn't there any way to increase this DELETE >performance or is it really so that Informix is dead slow in this kind >of SQLs? I know from Oracle they have this TRUNCATE command which clears >a table quite fast, however can't find anything like this in Informix. >Is there such a command in Informix in the first place or somethig >similar? > >Our system is Informix IDS 7.30TC7 running in WindowsNT4 box. The >database is divided into two chuncks and this big table occupies one >chunck and all the other tables in the database are on the other. Both >chuncks are on a SCSI-2 -drive (so I guess disk i/o should be OK if this >has anything to do with this). > >Any suggestions? I'm confused. Surely a TRUNCATE deletes *all* the rows in a table? So if you can't delete all the rows in a table, how is a TRUNCATE going to help you? There is a TRUNCATE command, but it's only available in XPS, so it probably won't help you. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com