Java Resultset.get***(...) quicker by string or int?

When iterating through a Resultset in the standard way

while (rs.next()){
...
}

To retrieve column data (e.g long) is it quicker to use

rs.getLong(String columnLabel)

or

rs.getLong(int columnIndex)

Presumably columnLabel is better to use in many ways for stronger code, but is there a significant operational loss by matching a column to the string every time (we're talking table sizes of ~40m rows)?

I'd wager that compared to the cost of preparing the result set the difference is negligable. Favour the robust code.

You can have the best of both! The speed of using indexes with the maintainability and security of using column names.

First - unless you are looping thru a result set just use column names.

1.Define a set of integer variables, one for each column you will access. The names of the variables can include the name of the column: e.g. iLast_Name.

2.Before the result set loop iterate thru the column metadata and set the value of each integer variable to the column index of the corresponding column name. If the index of the 'Last_Name' column is 3 then set the value of 'iLast_Name' to 3.

3.In the result set loop use the integer variable names in the GET/SET methods. The variable name is a visual clue to the developer/maintainer as to the actual column name being accessed but the value is the column index and will give the best performance.

NOTE: the initial mapping (i.e. column name to index mapping) is only done once before the loop rather than for every record and column in the loop.

I'd suggest that you think more about what's clearer than what's quicker.

Here's an example: Say you have a query, select * from .... and when you iterate over the ResultSet, you're calling String a = resultSet.getString(1), int i = resultSet.getInt(2) etc etc

Then you change your table definition so that you have some more columns... all of a sudden all your gets are going to break because you referenced everything by int. If you'd referenced by column name, then there would be no break.

To me this seems a lot clearer than referencing by column number. You'll also save a lot of time later on when trying to debug your old code to figure out why it's breaking.

Depends entirely on the internal structure. If they operate any sort of cache, it won't be a big deal. If they use a hashmap, it's unlikely to be substantial. A system engineered to support this won't have too much trouble coping. However, you should really think about what makes more sense in the context of your code, and come back to this if a profile demonstrates performance problems.