Slow Cursor
Posted in 1998
Topics: General Discussion
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.