0

I am implementing a GET REST API for faster retrieval of records

My Problem : While iterating through the resultSet consisting of 2500-5500 rows , it takes 1 minute 33 seconds to iterate, set the class objects. I want to decrease that time to under 3 seconds

Below given are my sample Table structure consisting of dummy data

ID    A_ID     FIRST_NAME     LAST_NAME    ROLE_NAME

10    jb007      JAMES         BOND           ASSASIN
10    jb007      JAMES         BOND           SHARP-SHOOTER
10    jb007      JAMES         BOND           DOUBLE-AGENT
23    bs312      BOONIE        SMITH          Account Manager
23    bs312      BOONIE        SMITH          Change Manager
21    bb989      BLUNT         BORIS          DEVELOPER
21    bb989      BLUNT         BORIS          MANAGER
21    bb989      BLUNT         BORIS          SERVICE DELIVERY
34    ch201      CHRIS         HUNT           MANGER DEAL LEAD
34    ch201      CHRIS         HUNT           CEO
34    ch201      CHRIS         HUNT           COO
34    ch201      CHRIS         HUNT           MANAGER

As shown above i am having around 2500-5500 rows consisting of data

here is my actual expected response

{
"UserSummary": [{
        "userId": 10,
        "a_id": “jb007”,
        "firstName": “JAMES”,
        "lastName": “BOND”,
        "roles": “ASSASIN,SHARP-SHOOTER,DOUBLE-AGENT”
    }, {
        "userId": 23,
        "a_id": “bs312”,
        "firstName": “BOONIE”,
        "lastName": “SMITH”,
        "roles": “Account Manager,Change Manager”
    },
    {
        "userId": 21,
        "a_id": “bb989”,
        "firstName": “BLUNT”,
        "lastName": “BORIS”,
        "roles": “DEVELOPER,MANAGER,SERVICE DELIVERY”
    },
    {
        "userId": 34,
        "a_id": “ch201”,
        "firstName": “CHRIS”,
        "lastName": “HUNT”,
        "roles": “MANGER DEAL LEAD,CEO,COO,MANAGER”
    }
]

}

Below given is my POJO class

public class UserSummaryResponse {
    private List<UserSummary> userSummary;
    public List<UserSummary> getSequenceUserSummary() {
        return SequenceUserSummary;
    }
    public void setSequenceUserSummary(List<UserSummary> sequenceUserSummary) {
        SequenceUserSummary = sequenceUserSummary;
    }   
}

public class UserSummary {

private Integer userId;
private String a_id;
private String firstName;
private String lastName;
private String Roles;
public Integer getUserId() {
    return userId;
}
public void setUserId(Integer userId) {
    this.userId = userId;
}
public String getA_id() {
    return a_id;
}
public void setA_id(String a_id) {
    this.a_id = a_id;
}
public String getFirstName() {
    return firstName;
}
public void setFirstName(String firstName) {
    this.firstName = firstName;
}
public String getLastName() {
    return lastName;
}
public void setLastName(String lastName) {
    this.lastName = lastName;
}
public String getRoles() {
    return Roles;
}
public void setRoles(String roles) {
    Roles = roles;
}


}

Below given is code logic that i have wrtten to iterate throught resultSet and to set the class Objects

                UserSummaryResponse resp = new UserSummaryResponse();
                List<UserSummary> usLs = new ArrayList<UserSummary>();
                String temp = null;

                rs = ps.executeQuery();
                Integer olduserId = 0;
                temp=null;
                UserSummary usObj = new UserSummary();
                usObj=null;
                while (rs.next()) {
                            int userid = rs.getInt(1);
                            if (userid != olduserId) {
                                if (usObj != null) {
                                    if(temp != null)
                                        usObj.setRoles(temp);
                                usLs.add(usObj);
                                }
                        olduserId = userid;
                        usObj = new UserSummary();
                        usObj.setUserId(rs.getInt(1));
                        usObj.setA_id(rs.getString(2));
                        usObj.setFirstName(rs.getString(3));
                        usObj.setLastName(rs.getString(4));
                        temp = "";
                        }
                        if (temp.length() > 0) {
                            temp += ",";
                            }
                        temp += rs.getString(5);
                        row_count++;
                        }
                        rs.close();
                        if (usObj != null) {
                                    if(temp != null)
                                        usObj.setRoles(temp);
                                usLs.add(usObj);
                                }
                        resp.setSequenceUserSummary(usLs);
                        return resp;

During Unit Testing , i had found that , its taking 1 minute 30-45 seconds to iterate through resultSet and Setting the class objects. Is there any efficient way to increase the setting performance so that it takes less than 3 seconds and thereby increasing the GET API overall performance.

  • And did you do some profiling? where most of time is consumed? inside DB on execution plan of query? on sending data from DB to JVM? Elsewhere? Start here https://stackoverflow.com/questions/22250303/java-application-profiling – rkosegi Oct 08 '17 at 06:02
  • yes i did.....It takes 1 minute and 33 seconds to during iterating and setting part – Titus Roby K Oct 08 '17 at 06:03
  • what is `setting part` ? inside setting loop what takes most of time? – rkosegi Oct 08 '17 at 06:06
  • the while loop is taking more than 1 minute to finish iterating. Is this common while iterating through more than 4500 -5000 rows ? – Titus Roby K Oct 08 '17 at 06:09
  • What does your query look like? – Alien Oct 08 '17 at 06:10
  • 2
    Can you share details from profiler? otherwise I doubt anyone will help you. – rkosegi Oct 08 '17 at 06:10
  • query is not all the issue , as i verified the query execution time that is around less than 900 milli second – Titus Roby K Oct 08 '17 at 06:11
  • 1
    @user8180066 - Please read up on [profiling](https://en.wikipedia.org/wiki/Profiling_(computer_programming)) and Java profiling tools. Your response to rkosegi indicates that you simply measured elapsed time. That's not what profiling really means. – Stephen C Oct 08 '17 at 06:21
  • *Unrelated:* According to the indentation of your code, you're doing an unconditional `return` inside the `while (rs.next())` loop. If you want our help, you should spend the minute it takes to format the code for human readability. – Andreas Oct 08 '17 at 06:51
  • There is nothing in the shown code that should take more than 1 minute for 5000 rows. Unless you show detailed profiling data showing otherwise, the problem is elsewhere. – Andreas Oct 08 '17 at 06:52

0 Answers0