9

Would there be a difference in performance when i use index to get data vs column name and I am talking about doing this operation a millions of times a day in the server.

rs.getString(1) vs rs.getString("columnname");

EDIT: JDBC version Oracle JDBC driver 10.2.0.4.0

Cœur
  • 32,421
  • 21
  • 173
  • 232
Srujan Kumar Gulla
  • 5,400
  • 9
  • 42
  • 75
  • Would it depend on the exact version of the JDBC driver? You'll probably need to make your own benchmark. – maerics Dec 31 '12 at 16:53
  • 3
    I think by column name will have little (negligible) overhead of calculating index, interesting question. Let us see what experts comes with. – kosa Dec 31 '12 at 16:53
  • 1
    I'm trying to imagine a database driver where the cost of looking up a column-index by name, or vice versa, can come anywhere near the cost of retrieving the data from the database. Would it be because they designed the database incredibly well, or the driver incredibly poorly? – ruakh Dec 31 '12 at 16:55

2 Answers2

11

The rs.getString(n); will perform slightly faster, because it's retrieving directly from a collection, rather than searching.

Hundreds of future readers of your code will appreciate the rs.getString("columnname"); rather than having to look up the SQL to see what the index n refers to.

Gilbert Le Blanc
  • 45,374
  • 5
  • 61
  • 107
4

It doesn't really matter. The hit to the database will be many many times slower than accessing the column values.

rs.getString(n) will be negligibly faster. However, it's going to depend on the driver implementation and number of columns in the result. Most implementations will likely use a HashMap to map column names to an index, but not necessarily. Also, some drivers may build the HashMap lazily which means the first row will be the slowest to access by column name. JTDS, as an example, does a linear search for columns not yet in its HashMap.

EDIT: minor edits and rearranged. No content change.

mikeslattery
  • 3,869
  • 1
  • 17
  • 13