Migliorare la velocità del metodo mostro Database

voti
0

Sto cercando di implementare una cache lato server per evitare di andare al database tutto il tempo per le query.

Ho costruito la cache, che funziona bene con centinaia di utenti, tuttavia quando il numero ottiene a migliaia ci vogliono secoli per costruire. Cosa ottimizzazioni posso fare per questo al fine di ottenere un certo aumento delle prestazioni.

Grazie

/**
 * 
 * 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;
}
È pubblicato 17/03/2014 alle 18:44
dall'utente
In altre lingue...                            


1 risposte

voti
0

Io non reinventare la ruota: Vedere memcached

Risposto il 17/03/2014 a 19:00
fonte dall'utente

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more