REST WS connecting to a database

Question

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.getWatcherUsername());
            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")
    public Response addSubscription(ActiveWatchers activeWatchers) {
        long start = System.currentTimeMillis();
        logger.debug("addSubscription start time {}", start);
        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.

Answer 1

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.