Slow execute stored procedure / .prepareCall

[Miran Cvenkel]

MySql.
See comments, how to avoid 700 ms in process ?
Also the 300 ms should be like 1/2 or less of 300, testing with HeidiSQL
Connection is opened and does not influence a thing.

 public int executeStoredProcedure(String procedureName, 
                                    String[] inParams,
                                    int [] outParam)
  {
 
    try
    {
 
      CallableStatement cs;
      
      String tmpStr = "";
      for (int i = 0; i < inParams.length+1; i++)
      {
        tmpStr += "?,";
      }
      tmpStr = tmpStr.substring(0,tmpStr.lastIndexOf(","));
      tmpStr = "(" + tmpStr + ")}";

      Calendar cal = Calendar.getInstance();
      long startTime = cal.getTimeInMillis();  

      cs = this.con.prepareCall("{call " + procedureName + tmpStr);  //takes like 700 ms

      cal = Calendar.getInstance();
      long endTime = cal.getTimeInMillis();
      System.out.println("prepare " + String.valueOf(endTime - startTime)); 

      cs.registerOutParameter(1, Types.INTEGER);
      
      
      for (int j = 0; j < inParams.length; j++)
      {
        cs.setString(j+2, inParams[j]);
      }
      
      cal = Calendar.getInstance();
      startTime = cal.getTimeInMillis();  

      cs.execute();//takes like 300 ms


      cal = Calendar.getInstance();
      endTime = cal.getTimeInMillis();
      System.out.println("execute " + String.valueOf(endTime - startTime)); 
      
      outParam[0] = cs.getInt(1);

      
    }
    catch (SQLException ex)
    {
      outParam[0] = -100;
    }
    catch (Exception ex)
    {
      outParam[0] = -100;
    }
    finally
    {
    
       
      return  result;
      
    }        

    
  }

[Scott Selikoff]

If you're asking how to improve JDBC driver methods, the short answer is you can't. You can try other drivers or databases, but likely this is a database specific issue.