# REST WS connecting to a database

I have a REST WS in Java using Jersey that connects to a database. I don't know what should be the ideal time for execution but I feel the time it takes is too much.

The actual call to DB completes in range of 0-3 milliseconds but the overall time to complete the REST request takes >9 milliseconds.

Here is one of the methods:

connection // declared as instance variable
preparedStatement //declared as instance variable
public int insertSubscription(ActiveWatchers activeWatchers) throws SQLException {

int index = 0;

try {
connection = DAOConnectionFactory.getConnection();
preparedStatement = connection.prepareStatement(INSERT_SUBS);
preparedStatement.setObject(++index, activeWatchers.getPresentityURI());
preparedStatement.setObject(++index, activeWatchers.getCallId());
preparedStatement.setObject(++index, activeWatchers.getToTag());
preparedStatement.setObject(++index, activeWatchers.getFromTag());
preparedStatement.setObject(++index, activeWatchers.getToUser());
preparedStatement.setObject(++index, activeWatchers.getToDomain());
preparedStatement.setObject(++index, activeWatchers.getWatcherDomain());
preparedStatement.setObject(++index, activeWatchers.getEvent());
preparedStatement.setObject(++index, activeWatchers.getEventId());
preparedStatement.setObject(++index, activeWatchers.getLocalCseq());
preparedStatement.setObject(++index, activeWatchers.getRemoteCseq());
preparedStatement.setObject(++index, activeWatchers.getExpires());
preparedStatement.setObject(++index, activeWatchers.getStatus());
preparedStatement.setObject(++index, activeWatchers.getReason());
preparedStatement.setObject(++index, activeWatchers.getRecordRoute());
preparedStatement.setObject(++index, activeWatchers.getContact());
preparedStatement.setObject(++index, activeWatchers.getLocalContact());
preparedStatement.setObject(++index, activeWatchers.getVersion());
preparedStatement.setObject(++index, activeWatchers.getSocketInfo());

long start = System.currentTimeMillis();
int status = preparedStatement.executeUpdate();
long end = System.currentTimeMillis();
logger.debug("insertSubscription elasped time {}", (end - start));
logger.debug("Insert returned with status {}.", status);
return status;

} catch (SQLException ex) {
logger.error("Error while adding new subscription by {}@{} for {} into database.", activeWatchers.getWatcherUsername(), activeWatchers.getWatcherDomain(), activeWatchers.getPresentityURI(), ex);
throw ex;
} catch (Exception ex) {
logger.error("Error while adding new subscription by {}@{} for {} into database.", activeWatchers.getWatcherUsername(), activeWatchers.getWatcherDomain(), activeWatchers.getPresentityURI(), ex);
throw ex;
} finally {
DAOConnectionFactory.closeConnection(connection, preparedStatement, null);
}

}


The REST part

subscriptionDAO //declared as instance variable
@POST
@Consumes("application/json")
long start = System.currentTimeMillis();
subscriptionDAO = new SubscriptionDAO();
try {
subscriptionDAO.insertSubscription(activeWatchers);
long end = System.currentTimeMillis();
logger.debug("addSubscription elasped time {}", (end - start));
return Response.status(201).build();
} catch (Exception ex) {
logger.error("Error while creating subscription.", ex);
return Response.status(500).entity("Server Error").build();
}
}


I have a lot of other similar functions for different operations and each has similar behavior which is affecting the overall performance of the system.

• Is the ActiveWatchers class trivial? The code you have presented here looks straightforward, and is unlikely to be your bottleneck. – 200_success Feb 18 '16 at 18:41
• @200_success ActiveWatchers class is the bean class. – user3275095 Feb 18 '16 at 20:15

You should try making insertSubscription static. You create a new SubscriptionDAO object with each request, when all you use it for is a a database handler. It doesn't have any internal state it keeps track of, except for the Connection and the PreparedStatement, which are overwritten inside of the method anyway.
You close the connection at the end of the query, and as such, you could easily make insertSubscription static. It'd help with multi-threading as well, if two threads get a hold of the same SubscriptionDAO and both do insertSubscription, they'd interfere pretty badly.
• Thanks for the suggestion. I will implement that way. What about connection and preparedStatement should they be instance\class\local variable. Initially, I made then local but someone said they should be made instance? – user3275095 Feb 19 '16 at 17:45