How should I optimize my code for better performance? When I execute the code outside of MySQL stored proc it is 500% faster.
MySQL stored procedure
SELECT bs.business_id, adr.street, bs.`name`, bs.description, adr.latitude, adr.longitude FROM businesses bs INNER JOIN address adr ON bs.address_id = adr.address_id WHERE bs.business_id = inBusinessid;
//code that fetches the data from the database
public static final String SP_GET_BUSINESS_BY_ID = "call small.tbl_business_get_by_id(?)";
public static final String BUSINESS_ID = "inBusinessid";
Business bs = null;
try
{
SqlStoredProc sproc = new SqlStoredProc(StoredProcs.SP_GET_BUSINESS_BY_ID, getConnection());
sproc.addParameter(businessId, ProcParam.BUSINESS_ID);
ResultSet reader = sproc.executeReader();
if (reader.next())
{
bs = setBusinessData(reader);
}
reader.close();
sproc.dispose();
}
Here is SQL wrapper I created.
public class SqlStoredProc
{
private CallableStatement mCallableStatement;
private PreparedStatement mPreparedStatement;
private Connection mConnection;
private boolean mConnectionOpen = false;
private boolean mInitConnectionClosed = true;
public enum SqlType
{
Integer, BigInt, TinyInt, Varchar, Char, Date, TimeStamp, Array, Blob, Boolean, Float, Decimal, Double
}
public SqlStoredProc(String storedProcName, Connection connection)
throws SQLException
{
mConnection = connection;
mCallableStatement = mConnection.prepareCall(storedProcName);
mConnectionOpen = true;
}
public SqlStoredProc(Connection connection) throws SQLException
{
mConnection = connection;
mConnectionOpen = true;
}
/* START OF PREPARED STATEMENT CODE */
public void setPreparedStatement(String preparedQuery) throws SQLException
{
mPreparedStatement = mConnection.prepareStatement(preparedQuery);
}
public void addPreparedParamether(int parameterIndex, String value) throws SQLException
{
mPreparedStatement.setString(parameterIndex, value);
}
public void addPreparedParamether(int parameterIndex, int value) throws SQLException
{
mPreparedStatement.setInt(parameterIndex, value);
}
public void addPreparedParamether(int parameterIndex, float value) throws SQLException
{
mPreparedStatement.setFloat(parameterIndex, value);
}
public void addPreparedParamether(int parameterIndex, double value) throws SQLException
{
mPreparedStatement.setDouble(parameterIndex, value);
}
public ResultSet executePreparedQuery() throws SQLException
{
return mPreparedStatement.executeQuery();
}
/* END OF PREPARED STATEMENT */
/* START OF STORED PROC */
public void setStoredProcName(String storedProcName) throws SQLException
{
mCallableStatement = mConnection.prepareCall(storedProcName);
}
public void addParameter(int value, String parameterName)
throws SQLException
{
mCallableStatement.setInt(parameterName, value);
}
public void addParameter(int value, int parameterIndex)
throws SQLException
{
mCallableStatement.setInt(parameterIndex, value);
}
public void addParameter(String value, String parameterName)
throws SQLException
{
if (value != null) mCallableStatement.setString(parameterName, value);
else mCallableStatement.setNull(parameterName, java.sql.Types.VARCHAR);
}
public void addParameter(String value, int parameterIndex)
throws SQLException
{
if (value != null) mCallableStatement.setString(parameterIndex, value);
else mCallableStatement.setNull(parameterIndex, java.sql.Types.VARCHAR);
}
public void addParameter(Date date, String parameterName)
throws SQLException
{
if (date != null)
{
mCallableStatement.setTimestamp(parameterName,
new java.sql.Timestamp(date.getTime()));
}
else
{
mCallableStatement.setNull(parameterName, java.sql.Types.TIMESTAMP);
}
}
public void addParameter(double value, String parameterName)
throws SQLException
{
mCallableStatement.setDouble(parameterName, value);
}
public void addParameter(float value, String parameterName)
throws SQLException
{
mCallableStatement.setFloat(parameterName, value);
}
public void addParameter(float value, int parameterIndex)
throws SQLException
{
mCallableStatement.setFloat(parameterIndex, value);
}
public int getOutParameterTypeInt(String parameterName) throws SQLException
{
return mCallableStatement.getInt(parameterName);
}
public float getOutParameterTypeFloat(String parameterName) throws SQLException
{
return mCallableStatement.getFloat(parameterName);
}
public double getOutParameterTypeDouble(String parameterName) throws SQLException
{
return mCallableStatement.getDouble(parameterName);
}
public void registerOutParameter(String parameterName, SqlType sqlType)
throws SQLException
{
switch (sqlType)
{
case Date:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.DATE);
break;
case TimeStamp:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.TIMESTAMP);
break;
case Integer:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.INTEGER);
break;
case TinyInt:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.TINYINT);
break;
case Varchar:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.VARCHAR);
break;
case Array:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.ARRAY);
break;
case BigInt:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.BIGINT);
break;
case Blob:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.BLOB);
break;
case Char:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.CHAR);
break;
case Boolean:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.BOOLEAN);
break;
case Float:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.FLOAT);
break;
case Decimal:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.DECIMAL);
break;
case Double:
mCallableStatement.registerOutParameter(parameterName,
java.sql.Types.DOUBLE);
break;
default:
break;
}
}
public int executeNonQuery() throws SQLException
{
int rowsAffected = mCallableStatement.executeUpdate();
return rowsAffected;
}
public void addBatch() throws SQLException
{
mCallableStatement.addBatch();
}
public boolean execute() throws SQLException
{
return mCallableStatement.execute();
}
public int[] executeBatch() throws SQLException
{
return mCallableStatement.executeBatch();
}
public ResultSet getResultSet() throws SQLException
{
return mCallableStatement.getResultSet();
}
public boolean getMoreResults() throws SQLException
{
return mCallableStatement.getMoreResults();
}
public ResultSet executeReader() throws SQLException
{
return mCallableStatement.executeQuery();
}
public CallableStatement getCurrentStatement()
{
return mCallableStatement;
}
public void dispose() throws SQLException
{
closeOpenConnections();
}
private void closeOpenConnections() throws SQLException
{
if (mConnectionOpen && mInitConnectionClosed)
{
if (mCallableStatement != null) mCallableStatement.close();
if (mPreparedStatement != null) mPreparedStatement.close();
mInitConnectionClosed = false;
mConnection.close();
}
}
}