Ok, first -- not all databases are the same. What is a best practice in one is not in the other. The above is definitely not a "best practice" in Oracle...

I myself would have coded that much differently -- it would definitely have been 1 stored procedure, not two chained together by the client over the network.


I definitely would optimize this code as follows (without changing the database and without changing the way it behaves on anyones database):

CallableStatement stmt;
CallableStatement stmt2;

synchronized String getWordb(int ID,int IDLang,Connection conn)
throws SQLException
{
String word="";
if ( stmt = null )
stmt = conn.prepareCall("{ call get.wordb (?,?,?)}");
stmt.setInt(1,ID);
stmt.setInt(2,IDLang);
stmt.registerOutParameter (3, java.sql.Types.VARCHAR);
stmt.execute();
word = stmt.getString (3);
if (word == null)
{
if ( stmt2 = null )
stmt=conn.prepareCall("{ call get.word1_b (?,?)}");

stmt2.setInt(1,ID);
stmt2.registerOutParameter (2,java.sql.Types.VARCHAR);
stmt2.execute();
word = stmt2.getString (2);
}
return word;
}

at a minimum that is what I would do. preparing a statement before each and every call is the very worst thing you can do to us. You are doing what is called a soft parse before each and every call to the database. You might just as well compile each class method before any invocation as well -- the effect is pretty much the same. You wouldn't dream of invoking javac and dynamically loading a class every time to invoke a method -- you are effectively doing that to us by preparing a call each and every time.

even more so -- the above database logic really belonged in the database. You could fix this immediatly by using a single statement that does:

declare
w varchar2(255);
begin
get.wordb( ?,?, w );
if ( w is null ) then
get_wrod1_b( ?, w );
end;
? := w;
end;

or creating a stored procedure really_get_word() that does the above logic.

Just to show you the difference between "compiling a statment and executing" and "compile it once, execute it many" I wrote a small java program. The results are:

> java test
JDBC driver version is 8.1.6.2.0
2001-02-19 09:02:14.46: Going to bad
2001-02-19 09:02:16.932: Going to good
2001-02-19 09:02:17.992: done

2.472 seconds the "bad" way.
1.060 seconds the "good" way.


the test code was:

import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class test
{


public static void showTimeStamp( String msg )
{
System.out.println( (new
Timestamp(System.currentTimeMillis())).toString() +
":" + msg );
}

static CallableStatement cstmt_persistent;

public static void good_way(Connection conn ) throws Exception
{
if ( cstmt_persistent == null )
{
cstmt_persistent =
conn.prepareCall("{ call proc2( ?, ? ) }" );
cstmt_persistent.registerOutParameter
( 2, java.sql.Types.VARCHAR );
}
cstmt_persistent.setInt(1, 1);
cstmt_persistent.execute();
String w = cstmt_persistent.getString(2);
}

public static void bad_way(Connection conn ) throws Exception
{
CallableStatement cstmt =
conn.prepareCall("{ call proc1( ?, ? ) }" );

cstmt.setInt(1, 1);
cstmt.registerOutParameter( 2, java.sql.Types.VARCHAR );

cstmt.execute();
String w = cstmt.getString(2);
cstmt.close();
}

public static void main(String args[])throws Exception
{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn=DriverManager.getConnection
//("jdbc:oracle:thin:@aria-dev:1521:ora816dev"
("jdbc:oracle:oci8:@ora816dev.us.oracle.com"
,"scott","tiger");

DatabaseMetaData meta=conn.getMetaData();
System.out.println
("JDBC driver version is "+meta.getDriverVersion());

conn.setAutoCommit(false);

// stmt.execute( "alter session set sql_trace=true" );

showTimeStamp( "Going to bad" );
for( int i = 0; i < 1000; i++ ) bad_way(conn);

showTimeStamp( "Going to good" );
for( int i = 0; i < 1000; i++ ) good_way(conn);

showTimeStamp( "done" );
}
}

proc1 and proc2 were simply:


scott@DEV816> create or replace procedure proc1( x in int, y out varchar2 )
2 as
3 begin
4 y:='hello';
5 end;
6 /

Procedure created.
scott@DEV816> create or replace procedure proc2( x in int, y out varchar2 )
2 as
3 begin
4 y:='hello';
5 end;
6 /

Procedure created.