Which one is performance for database?

Question

Performance wise which one is best in below two cases, for example:

Version 1:

int value1 = rs.getInt(1);
String value2 =  rs.getString(2);

Version 2:

int value1 = rs.getInt("VALUE1");
String value2 = rs.getString("VALUE2");

Which one is best to use? In many places i added the code like Step 1 way. I want to know is it really make sense.

Iirc, your `ResultSet` caches the next (few?) rows locally when calling `rs.next()`, so there shouldn't be a difference in performance. Btw: interesting question! — stuXnet, Oct 29 '14 at 13:34
1) It will depend on the database and the JDBC drivers. 2) The difference in perfomance is likely to be so small that it irrelevant in 99.5% of cases. 3) If you really care, benchmark it. — Stephen C, Oct 29 '14 at 13:36
After a DB call, the performance consideration on how to read the result is totally irrelevant. — Jean Logeart, Oct 29 '14 at 13:37
As Stephen says, the issue almost certainly won't be related to performance, although the driver makes all the difference. There *is* a difference in readability and maintainability. — Dave Newton, Oct 29 '14 at 13:38

score 1 · Accepted Answer · edited May 23 '17 at 12:22

As said in the commments, it depends on how the JDBC driver implements the ResultSet interface. For example, the MySQL jdbc driver implements the two getInt() methods similar to

public int getInt(int columnIndex) throws SQLException {
    ...
}

public int getInt(String columnName) throws SQLException {
    int idx = findColumn(columnName);
    return getInt(idx);
}

Naturally, getInt(int) is faster than getInt(String) since the String variant requires an additional mapping from the column name to the column index.

Does it matter in practice? Most likely not. If unsure, create test data and benchmark it. Other factors like the fetch size will impact your overall performance much more.

I would always prefer the getInt(String) variant, since it is more robust against modifying the SELECT statement (adding/deleting columns from the projection).

Which one is performance for database?

1 Answers1