Re: Slow Cursor

On Mon, 28 Sep 1998 00:41:20 GMT, justabill@school.house.rocks (justabill) wrote: >I have a program that does the following > > >reads in values from a flat file then selects the same record from a >table. If it can't find the record it does an insert, if it finds it, >it will check to see if the row has changed and do an update if >necessary. It then inserts the primary key into a temp table. > >Then it creates an index on the temp table and does an update stats on >the indexed columns (both original table and temp table). >All of this runs fine, it processes all the records in a reasonable >amount of time. > >Then I open a scroll cursor on the table selecting the primary key >values. I take these values and run through the cursor making sure >that they all exist in the temporary table. If they are not in the >temporary table I delete them. When I start to do this the program >comes to a screaming hault. All of a sudden I go from processing over >100 rows per second to less than 100 rows per minute. I've let the >program run overnight and it still wasn't finished. >The tables both have about 323,000 rows in them and I have verified >that all other users are locked out when the program is running. I am >running informix 7.23 on windows NT. > >Any help or suggestions would be greatly appreciated. It's well known that delete is much slower than insert and update. The engine is tuned for the latter at the expense of deletes wich is often ok. It still sounds very slow though. First the deletes will take much longer if you have many indexes on the table you delete from. This may be part of the problem. Second it sounds very much like you could do much of this in a substantially simpler way. As I understand it at the end you want to delete all rows from your main table where the primary key is not in the temporary table. To find these rows you could simply do as follows: select maintable.primkey as mainprimkey, temptable.primkey as tempprimkey from maintable, outer temptable where maintable.primkey = temptable.primkey Rows returned from this where temptable.primkey is null does not exist in the temporary table. (Don't fall into the trap of including a test on this in the above select. No rows will be returned then.) If you add: into tempt tt_1 to the above select you can do a delete from the maintable as follows: delete from maintable where maintable.primkey in (select tt_1.mainprimkey from tt_1 where tt_1.tempprimkey is null) Now the whole delete will be done in two statements and run much faster. There are two problems with this: a. If the primary key is more than one field it will not work and there is no easy way to make it work, at least not in a general way. b. The whole delete will now be done in one transaction. That may lead to a long transaction problem. If that is an issue I would try to increase log space or optionally run this without transactions if that is possible. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)