Adding result set into ArrayList take so much of time

Question

Here is my code where i am running my query and adding result set into ArrayList . Modified code

private ArrayList<String> getEventsFromShares() throws SQLException {
    Statement eventStmt = null;
    ResultSet rs = null;
    ArrayList<String> eventsList = new ArrayList<String>(10000);
    try {
        eventStmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        conn.setAutoCommit(false);
        System.out.println("Getting data from shares table");
        rs = eventStmt.executeQuery(
                "select s.event_id from tso_shares s join ife.entityids i on s.event_id = i.entityid where to_char(i.datemodified,'YYYY') = "
                        + year + "");
        eventStmt.setFetchSize(500);
        while (rs.next()) {
            eventsList.add(rs.getString(1));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        rs.close();
        eventStmt.close();
    }
    System.out.println("eventsMap" + eventsList.size());
    return eventsList;
}

query comes out in few seconds only but addog into ArrayList takes almost 15 minutes of time .

Can some one help me identify the issue or how can i improve my code .

How big is your data? Also, is it really adding the Strings to your ArrayList or getting the Strings out of the ResultSet? — jalako, Mar 14 '19 at 07:21
Print some timestamps before the execution of the query, after it and after the `while` loop. — deHaar, Mar 14 '19 at 07:25
@jalako total size is 70k only second part i have to confirm — Shailendra, Mar 14 '19 at 07:26
I wonder if making your result set scrollable has a performance cost? Why not use [`TYPE_FORWARD_ONLY`](https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/ResultSet.html#TYPE_FORWARD_ONLY)? In which case you’d be using defaults only and could omit both arguments. — Basil Bourque, Mar 14 '19 at 07:31
@Shailendra Define “slowness”, give specific numbers. Explain your hardware, OS, database, and JDBC driver. — Basil Bourque, Mar 14 '19 at 21:22

score 3 · Answer 1 · answered Mar 14 '19 at 07:22

3

It looks like you only want column event_id from table s so change your SQL statement to select s.event_id instead of select s.*. Then you can extract the value from the ResultSet using rs.getString(1) rather than rs.getString("event_id")

Also you can give your ArrayList an initial capacity if you know approximately how many rows your SQL query will return, e.g.

ArrayList<String> list = new ArrayList<>(500)

answered Mar 14 '19 at 07:22

Abra

19,142
7
29
41

Its not improving for me even after all the changes – Shailendra Mar 14 '19 at 09:01
1

Then I think you need to profile it and see what amount of time each part of your code is taking. Perhaps setting the fetch size to 500 is your problem? I'm only guessing. Profile your code first. I don't know if it will make a difference, but have you tried using `java.sql.PreparedStatement` instead of `java.sql.Statement`? – Abra Mar 14 '19 at 13:51
@Abra I suggest you put those suggestions from your Comment into your Answer. – Basil Bourque Mar 14 '19 at 21:28

Adding result set into ArrayList take so much of time

1 Answers1