Dynamic server DELETE performance problem

Hi, Sorry if this has been discussed lately but haven't been following this group before (I'm new to Informix). The problem is this: I've got a table who has about 2.3 million rows of data in it. Nothing fancy (like TEXT or BLOB fields, just plain normal data). 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? TIA Keijo

The IDS2000 version of Informix does support the TRUNCATE command (older versions do not). However, TRUNCATE does not appear to be what you want. Deletes on any RDBMS are significantly affected by the presence of Indexes - the fewer the better. If you are locking the table during your delete, it may be worthwhile disabling your indexes before the delete and enabling them after, even if you are not deleting all rows - you will need to test this. Definitely, re-examine your indexes and remove, permanently, those that you can do without. If you are locking your database during the delete (one never knows!), you could consider disabling logging for the delete and re-enabling logging after it completes. Warning : You must take a "true" level-0 backup when reenabling logging. Configuration parameters of your instance may also be slowing down the delete. BUFFERS, LRU_MAX_DIRTY, the size of your physical log come to mind. Increasing the values of each usually helps. Your I/O subsystem is critical to performance. Monitor it, using OS utilities, during the delete. Rudy Keijo Karvonen wrote:. > The problem is this: I've got a table who has about 2.3 million rows of > data in it. Nothing fancy (like TEXT or BLOB fields, just plain normal > data). 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). > Keijo

Two answers and two comments: I. Get my dbdelete utility from the package utils2_ak available in the IIUG Software Repository. It was crafted to provide the fastest mass deletes possible. Keep the other suggestions about indexes in mind though to gain even more speed. II. What are you REALLY trying to accomplish? It sounds like you will be loading data that is time dependent and will be deleted when its usefulness is past. Consider fragmenting the table over N dbspaces one for each deletion period. So if you will be deleting quarterly create the table with 4 fragments and have a 5th dbspace available to create a new fragment for the next quarter if you need to load before deleting. Then to delete the old data you can detach that period's fragment to a separate table and if loading first or using 7.xx or 9.1x, drop it (and if loading last on 7.3x/9.1x create a new fragment). If loading last and you are using 9.[23]x you can truncate and reattach the fragment. This last brings me to my comments. As a new poster you did not know. It is VERY helpful to us to know your versions and platform (ie Sun - Solaris 7 or HP - HPUX 11.0 etc.) so we can tailor our answers and save bandwidth by eliminating all that 'if you have version...' stuff where possible. Second, try to describe the problem you are trying to solve rather than or in a addition to the possible solution you are having trouble implementing so we can save you from doing things the hard way. Art S. Kagel Keijo Karvonen wrote: > > Hi, > > Sorry if this has been discussed lately but haven't been following this > group before (I'm new to Informix). > > The problem is this: I've got a table who has about 2.3 million rows of > data in it. Nothing fancy (like TEXT or BLOB fields, just plain normal > data). 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? > > TIA > > Keijo