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 procedureSELECT 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();        }    }}
Optimize MySQL in a stored procedure
java;performance;mysql;sql
After three days for research, I discovered that CallableStatements are much slower than prepared statements because there is overhead when setting up the stored procedure. That's why my stored proc takes 300ms+ vs the prepared statement.This explains the issue:As you may recall, CallableStatement objects are used to execute database stored procedures. I've saved CallableStatement objects until last, because they are the slowest performers of all the JDBC SQL execution interfaces. This may sound counterintuitive, because it's commonly believed that calling stored procedures is faster than using SQL, but that's simply not true. Given a simple SQL statement, and a stored procedure call that accomplishes the same task, the simple SQL statement will always execute faster. Why? Because with the stored procedure, you not only have the time needed to execute the SQL statement but also the time needed to deal with the overhead of the procedure call itself.