returning a Resultset slows down program

Question

So I wrote a method to just get a database table and it returns the resultset. But, it seems to be hanging on this method call. I THOUGHT it was returning just a pointer but I'm still new to programming. Is this call returning a pointer of Resultset or is it passing the whole thing. My main question is, why would this slow down the program? The table isn't that big.

public ResultSet getEmployeeTable() throws SQLException
{
    ResultSet rsEmpl;
    Connection con = getDBConnection();
    PreparedStatement pstmt;
    String query;

    query = "Select * from PTO_Employee where enabled = ?";
    pstmt = con.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    pstmt.setInt(1, 1);
    rsEmpl = pstmt.executeQuery();      

    return rsEmpl;
}

how "slow" is that method? What/how much data is in the table PTO_Employee? Is there an index on the column enabled? How many rows are returned? Maybe the statement is just time consuming. — david, Mar 28 '12 at 20:42
this is very nasty. how is the connection going to get closed? part of your problem could be stranded database connections. — Nathan Hughes, Mar 28 '12 at 20:43
I'm almost done with the project and getting sloppy. I wrote these methods quickly and forgot to do the try/catch/finally blocks. — cphilpot, Mar 28 '12 at 20:45
@David It's only 15 rows of data. When this is placed directly into the program, it runs fine. It's only when placed within a method it "hangs" — cphilpot, Mar 28 '12 at 20:45
if you add a finally to close the connections then returning the resultset won't work, the resultset relies on a live connection to retrieve stuff from the database. copy the data from the resultset into some collection and return the collection instead. — Nathan Hughes, Mar 28 '12 at 20:47

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

It's connecting the DB which takes the most time. This can last in range of 100ms up to 1 second. That's also why most webapps are using a so-called connection pool. A connection pool contains a bunch of already-opened connections which the webapp can then just reuse. You'll only need to rewrite your JDBC code to adhere the JDBC idiom of opening-and-closing in a try-finally block, otherwise you would later still have performance problems because you never return the connection to the pool and the pool would run out of them.

Further, at the moment you're returning the ResultSet, it does not contain any records in the memory. This will usually only be retrieved from the DB and filled in Java's memory when you call next() for the first time. However, passing the ResultSet around outside the context where the SQL query is been invoked is a very bad practice. It should normally be mapped to a collection of entities.

If I passed an already existing database connection, would that speed this up much? — cphilpot, Mar 28 '12 at 20:40
This is what a connection pool is supposed to do, yes. You should however absolutely not try to manage it yourself. I'd say, go through the given links to learn the one and other. — BalusC, Mar 28 '12 at 20:42

score 0 · Answer 2 · answered Mar 28 '12 at 20:34

0

Your method returns a reference. But this reference points to a place at the heap where the content of the result set is stored in memory.

So even if you are passing around the reference, you have the whole result somewhere in your memory.

Your method is "hanging" because it processes all the necessary actions to get the results from the database. (creating a connection, authenticate to the database, executing the statement, fetch the results...)

answered Mar 28 '12 at 20:34

david

852
1
7
22

I realize that, but if I just slap this into the main program, it blows by it like it's nothing. Is there any way to make this "faster" or am I going to have to just copy past the code into the main method? – cphilpot Mar 28 '12 at 20:36

returning a Resultset slows down program

2 Answers2

See also: