AW: delete performance

What is the deleting sqlexec waiting for the most time? You migth use this SQL statement - select sum(cumtime), reason from sysmaster:sysseswts where cumtime >1000 and reason != 'condition' and sid = <the_sessions_id> group by reason order by 1 desc; or have a look at onstat -g ath / and or onstat -g wst Deleteing from an indexed table is tal´king its time, but 2 min per 1000 rows sounds extreme´ly long So there is going on something Setting PDQ to 1 might help so the query is executed parallel. Regards Tilman ________________________________ Von: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] Im Auftrag von Floyd Wellershaus Gesendet: 19 October 2006 13:56 An: informix-list@iiug.org Betreff: delete performance Am I overlooking something here ? I just became aware that a high profile ec program is having major slowness doing deletes from a big table. The table contains 53 million rows, is indexed on the columns being queried for the delete, is fragmented on other columns. I've been told there were some issues ( before my time here, and no specifics from the developers ) when doing a simple delete, ie... delete from table where columna=integer. So now, what they are doing is setting up a select cursor by selecting 2 columns, then setting up a delete cursor then fetching from the select cursor to delete. It takes from 2 to 7 minutes per thousand to delete from this table this way. Just wondering, is there something I am overlooking to make this go faster. I'm thinking of telling them to set pdqpriority in their session, also I'm thinking I want to know why the regular delete without the cursors was giving problems. The database is unlogged, so I doubt a long tx was happening. I guess it could have been using temp files and running out of temp space. Any ideas at all would be helpful. Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/