I have a Java Stored Procedure which I deploy to Oracle 9i database.
In one of the method I am performing the following operations: Quering a table and storing the values in varibales, befrore inserting I am truncating the table and then inserting the values into the table.
Is there any way I can optimise and improve the perfomance?
1) Retrieving through a DB link can slow things down a bit; if possible, use 2 connctions and connect directly to the database instead of using the "@LM.SF.COM" link. 2) Oracle usually benefits from setting a higher fetch size; Oracle's default is 10 rows, 100 rows is often a better first guess. See the setFetchSize method. http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html setFetchSize() is of course inherited by PreparedStatement. Alternately, you could use Oracle's non-standard extension to Connection, setPrefetchSize(). 3) The most obvious performance problem with this is that you aren't anywhere close to using PreparedStatement correctly.