Q
Problem solve Get help with specific problems with your technologies, process and projects.

Updating rows with a cursor is slow

Published: 07 Jun 2005
I'm extremely new to Oracle. I'm learning on the fly and have to come up to speed fairly quicky. I'm currently working on a 210,000-row database. It seems that updating over 5,000 rows is pretty slow. I would do this in Fortran in a few seconds. Oracle, using PL/SQL takes about four minutes. I have an index on the table, and I'm reading a transaction table into a cursor and fetching each row from the cursor and selecting the appropriate master row to update. This is a very small database for my purposes. It will eventually contain over 100 million rows. There is no way I can survive with that kind of performance. Am I totally missing something?

Tuning your PL/SQL code starts with tuning the SQL statements you use in that code. If you are using a cursor,...

that cursor is based on a query. Have you tuned that SQL statement so that it effeciently pulls the data out of the database?

You also mentioned that you are updating rows in another table in your cursor's loop. Are you committing inside that loop? That can be a performance killer. Take the COMMIT out of the loop and see if that helps your performance.

Finally, are you sure that you cannot perform this as one simple SQL statement rather than updating in a cursor's loop? For instance, I might have the following psuedo code: 

CURSOR c1 IS SELECT valX FROM tableX;
OPEN c1;
LOOP
  FETCH c1 INTO x;
  EXIT WHEN c1%NOTFOUND;
  UPDATE tableY SET colA = colA*1.10 WHERE colX = x;
END LOOP;
CLOSE c1;

UPDATE tableY SET colA = colA*1.1.0
WHERE colX IN (SELECT valX FROM tableX);

Excuse my psuedo code off the top of my head. In the above sample, I am selecting a value from TABLEX. Based on that value, I update another table's column. Instead of this complex looping construct, I can use the following single SQL statement instead: This single SQL statement should run faster than the same work being done in the loop. You might find it more beneficial if you can code your work in a single SQL statement.

Dig Deeper on Oracle database performance problems and tuning

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close