Reduce database I/O ops.

Question

Am I correct in thinking that for a database with n USERS, the following code results in at least n interations with the database?

Statement stmt = con.createStatement();
ResultSet srs = stmt.executeQuery(
    "SELECT FAVOURITE_COLOR FROM USERS");
while (srs.next()) {
        //performSomeAction on srs.getString("FAVOURITE_COLOR");
}

Would it then also be possible to extract the FAVOURITE_COLOR from all users in 1 I/O interaction with the database? And if so would that posssibly cause an memory overflow in the program if there are too many users?

Answer 1

Why can't you just get all the favorite color records at once?

ResultSet srs = stmt.executeQuery(
    "SELECT USERID, FAVOURITE_COLOR FROM USERS");

Actually after further thought this depends on the platform and or DBMS you are talking about. You may need to give more information for an intelligent answer. Some Platforms will get all the rows at once and let you cycle through them, some will only pull rows as you move to them.

Answer 2

Your sample code does a single roundtrip to the database.

In a simple implementation, executeQuery will wait for the entire dataset to be retrieved from the database. Then each call to next will move immediately to the next row.