提高monster数据库方法速度的方法

Question

我正在尝试实现一个服务器端缓存，以避免所有时间都去数据库进行查询。

我已经建立了我的缓存，它可以很好地处理数百个用户，但是当这个数字达到数千个时，它需要很长时间才能建立起来。为了获得一些性能提升，我可以对此做些什么优化。

谢谢

/**
 * 
 * Get a user profile
 * 
 * @param userid
 *            the userid of the profile to get
 * 
 * @param serialized
 *            true if you want the profile string serialized
 * 
 * @return the user profile as a string or an Object
 */
private static ConcurrentMap<String, Profile> getUserProfiles() {

    final String select = "SELECT ua.userid, ua.displayname, ua.major, ua.minor,ua.profileimage,ua.genderpreference,"
            + "ad.devicetoken,pf.optionalImageOne, pf.optionalImageTwo,  pf.optionalImageThree, "
            + "pf.optionalImagefour,pp.created,hon.numhots,hon.numnots "
            + "FROM UserAccount ua "
            + "LEFT OUTER JOIN ApnsDevices ad ON ad.userid = ua.userid "
            + "LEFT OUTER JOIN ProfileImages pf ON pf.userid = ua.userid "
            + "LEFT OUTER JOIN PendingProfile pp ON pp.userid = ua.userid "
            + "LEFT OUTER JOIN HotOrNot hon ON hon.userid = ua.userid";

    final String friendsList = "SELECT * FROM Shortlist WHERE userid = ?";
    String displayName = "";
    String userid = null;
    String genderpreference = null;

    Profile userProfile;
    int majorid = 0;
    int minorid = 0;
    int hots = 0;
    int nots = 0;

    byte[][] images = new byte[4][];
    byte[] image = null;

    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    ResultSet rs2 = null;

    Timestamp onDemandTimeSent = null;
    long origianlStartTime = System.nanoTime();
    ConcurrentMap<String, Profile> results = new ConcurrentHashMap<String, Profile>();

    try {
        conn = source.getConnection();
        st = conn.prepareStatement(select);
        rs = st.executeQuery(select);
        while (rs.next()) {

            byte[] imageOne = null;
            byte[] imageTwo = null;
            byte[] imageThree = null;
            byte[] imageFour = null;

            long startTime = System.nanoTime();
            userid = rs.getString("userid");
            genderpreference = rs.getString("genderpreference");
            majorid = rs.getInt("major");
            minorid = rs.getInt("minor");
            image = rs.getBytes("profileimage");
            displayName = rs.getString("displayname");
            hots = rs.getInt("numhots");
            nots = rs.getInt("numnots");
            onDemandTimeSent = rs.getTimestamp("created");

            if (rs.getBlob("optionalImageOne") != null){
                imageOne = rs.getBlob("optionalImageOne").getBytes(1,(int) rs.getBlob("optionalImageOne").length());
            }
            if (rs.getBlob("optionalImageTwo") != null){
                imageTwo = rs.getBlob("optionalImageTwo").getBytes(1,(int) rs.getBlob("optionalImageTwo").length());
            }
            if (rs.getBlob("optionalImageThree") != null){
                imageThree = rs.getBlob("optionalImageThree").getBytes(1,(int) rs.getBlob("optionalImageThree").length());
            }
            if (rs.getBlob("optionalImageFour") != null){
                imageFour = rs.getBlob("optionalImageFour").getBytes(1,(int) rs.getBlob("optionalImageFour").length());
            }

            images[0] = imageOne;
            images[1] = imageTwo;
            images[2] = imageThree;
            images[3] = imageFour;
            String deviceToken = rs.getString("devicetoken");

            // get friends list
            List<String> friends = new ArrayList<String>();
            st = conn.prepareStatement(friendsList);
            st.setString(1, userid);
            rs2 = st.executeQuery();
            String friend;
            while (rs2.next()) {
                friend = rs2.getString("friend");
                if (friend != null) {
                    friends.add(friend);
                }
            }

            if (onDemandTimeSent != null) {
                userProfile = new Profile(image, onDemandTimeSent.toString(), userid, displayName, hots, nots, majorid, minorid, deviceToken, genderpreference, friends);
            } else {
                userProfile = new Profile(image, null, userid, displayName, hots, nots, majorid, minorid, deviceToken, genderpreference, friends);
            }

            userProfile.setAlternativeImages(images);
            long endTime = System.nanoTime();
            double totalTime = TimeUnit.SECONDS.convert((endTime - startTime), TimeUnit.NANOSECONDS);
            System.out.println("Total time to generate user profile for "+ userid + " was : " + totalTime + " seconds : " + userProfile);
            logger.info("Generated user profile for " + userid);
            results.put(userid, userProfile);
        }
    } catch (Exception e) {
        e.printStackTrace();
        logger.warn("An error occured when getting user profile for "+ userid, e);
    } finally {
        closeConnection(conn);
        closeResultSet(rs);
        closeResultSet(rs2);
        closePreparedStatement(st);
    }
    long endTime = System.nanoTime();
    double totalTime = TimeUnit.SECONDS.convert((endTime - origianlStartTime), TimeUnit.NANOSECONDS);
    System.out.println("Total time to generate all user profiles for " + results.size() + " users was " + totalTime + " seconds");
    return results;
}

Answer 1

我不会重复发明轮子：See memcached