Re: Slow Cursor

First of all I can't thank you enough for your replies. I really hadn't thought about having too many indexes on the table. I will definately keep that in mind. I worked on this problem today and this is what I came up with. If you have a second to give me some feedback I would greatly appreciate it. I dropped the whole cursor idea all together and did it with the following sql delete from FirstTable whereFirstTable.PK1 || FirstTable.PK2 not in (select secondtable.PK1 || secondtable.PK2) This is a lot faster, I'm not convinced that it is the fastest way of doing it which is what I usually strive for. One pretty important thing I forgot to mention, and I apologize in advance, is that our database has logging turned off. One other problem I found and corrected is that in the first table the primary key values where decimals and in the other table they where strings. I changed it so both tables where both decimals. Thanks for all your help!!! On Mon, 28 Sep 1998 15:26:26 GMT, Nils.Myklebust@nmdata.com (Nils Myklebust) wrote: >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".)