Re: bind variables from java code

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 26 May 2003 14:29:08 -0700
Message-ID: <92eeeff0.0305261329.48a6cc59_at_posting.google.com>


hayt_at_inwind.it (hayt) wrote in message news:<11f54ca0.0305260322.58a9dfaf_at_posting.google.com>...
> rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0305242134.51d95bca_at_posting.google.com>...
> >
> Yes, you are right. I've done a mistake with the syntax:
> "{ CALL SC_FILL_PRESENCE(" + v_udptype + "," + v_ip + ", ?, ?) }";
> is the right version, of course. Only my fault typing the message.
> Then you say that concatenating variables inside the "callable string"
> is bad? I don't have to call the stored procedure multiple times (only
> once), and with this behaviour I've seen that the second approach is
> faster.
> I include a simple client to verify this (I've tried with Oracle OCI
> drivers):
>
> import java.sql.*;
> public class DBCheckConnection {
> private static Connection dbconn;
> private static final int LOOPS = 400;
> public static void main (String[] args){
> /*
> args = new String[4];
> args[0]="oracle.jdbc.driver.OracleDriver";
> args[1]="jdbc:oracle:thin:_at_217.169.119.169:1521:ORIOTEST";
> args[2]="R3_PR";
> args[3]="R3_PR";
> */
> try{
> //args[0] = driver
> Class.forName(args[0]);
> //args[1] = url, args[2] = username, args[3] = password
> dbconn = DriverManager.getConnection(args[1], args[2],
> args[3]);
> System.out.println("DB available");
>
> dbconn.setAutoCommit(true);
>
> long startSPTime = System.currentTimeMillis();
> for (int i = 0; i<LOOPS; i++){
> callWithParameters();
> }
> System.out.println("sc_fill_presence with params and
> autoCommit execution time: " + (System.currentTimeMillis() -
> startSPTime) + " ms.");
>
> startSPTime = System.currentTimeMillis();
> for (int i = 0; i<LOOPS; i++){
> callWithoutParameters();
> }
> System.out.println("sc_fill_presence without params and
> autoCommit execution time: " + (System.currentTimeMillis() -
> startSPTime) + " ms.");
>
> dbconn.setAutoCommit(false);
>
> startSPTime = System.currentTimeMillis();
> for (int i = 0; i<LOOPS; i++){
> callWithParameters();
> }
> System.out.println("sc_fill_presence with params execution
> time: " + (System.currentTimeMillis() - startSPTime) + " ms.");
>
> startSPTime = System.currentTimeMillis();
> for (int i = 0; i<LOOPS; i++){
> callWithoutParameters();
> }
> System.out.println("sc_fill_presence without params
> execution time: " + (System.currentTimeMillis() - startSPTime) + "
> ms.");
> dbconn.close();
> }catch (SQLException sqle) {
> System.out.println("DB connection not available");
> sqle.printStackTrace();
> }catch (Exception e) {
> System.out.println("DB connection not available");
> e.printStackTrace();
> }
> }
>
> private static void callWithoutParameters() throws SQLException{
> String spCall = "{ CALL SC_FILL_PRESENCE(?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }";
> // Preparing the stored procedure
> CallableStatement cstmt = dbconn.prepareCall(spCall);
> // Binding input varialbes
> cstmt.setString(1, "0002");
> cstmt.setString(2, "62.211.11.121");
> cstmt.setString(3, "151.99.13.24");
> cstmt.setString(4, "3/0/0");
> cstmt.setString(5, "52");
> cstmt.setString(6, "59");
> cstmt.setLong (7, 1053705650420l);
> cstmt.setString(8, "test");
> cstmt.setString(9, "test");
> //Registering SP out parameters
> cstmt.registerOutParameter(10, java.sql.Types.INTEGER);
> cstmt.registerOutParameter(11, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(12, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(13, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(14, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(15, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(16, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(17, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(18, java.sql.Types.VARCHAR);
> //Executing stored procedure
> cstmt.execute();
> cstmt.close();
> }
>
> private static void callWithParameters() throws SQLException{
> String spCall = "{ CALL SC_FILL_PRESENCE('0002',
> '62.211.11.121', '151.99.13.24', '3/0/0', '52', '59', '1053705650420',
> 'test', 'test', ?, ?, ?, ?, ?, ?, ?, ?, ?) }";
> // Preparing the stored procedure
> CallableStatement cstmt = dbconn.prepareCall(spCall);
> //Registering SP out parameters
> cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
> cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(7, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(8, java.sql.Types.VARCHAR);
> cstmt.registerOutParameter(9, java.sql.Types.VARCHAR);
> //Executing stored procedure
> cstmt.execute();
> cstmt.close();
> }
> }
>
> Try by yourself ;-)

Good test. It's always a good idea to verify for yourself before taking someone else's word for it.

HOWEVER, you forgot one thing, you are doing the same thing in both callWithoutParameters() and callWithParameters(). You are making the jdbc driver send the statement to the database to compile each time you call it. Read small example in my first post.

Change your code as following and then try again,

Take CallableStatement cstmt = dbconn.prepareCall(spCall); and cstmt.close(); out of callWithoutParameters() and put it before and after you call callWithoutParameters() in a loop to execute cstmt. Change callWithoutParameters() to accept CallableStatement as parameter.

            startSPTime = System.currentTimeMillis();
            String spCall = "{ CALL SC_FILL_PRESENCE(?, ?, ?, ?, ?, ?,
                               ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }";
            CallableStatement cstmt = dbconn.prepareCall(spCall); 
             for (int i = 0; i<LOOPS; i++){
 	      	    callWithoutParameters(cstmt);
             }
             cstmt.close();

This way, you will have the driver precompile the statement before you enter the loop to execute it... (compile once, execute 400 times). Try it and note the execution time.

Furthermore, replace CallableStatement with PreparedStatement and test. You would notice that callWithoutParameters() will give you even better performance then CallableStatement and callWithParameters() will give you poorer performance then CallableStatement... although difference may not be a lot.

Regards
/Rauf Sarwar Received on Mon May 26 2003 - 23:29:08 CEST

Original text of this message