Optimize MySQL in a stored procedure

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
{
mPreparedState

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.

StackExchange Code Review Q#40285, answer score: 3