Ramifications for Calling Statement.cancel() on To Forcefully Close Large ResultSet in Java/MySQL

I'm developing a website that will allow registered users to search through anywhere from 10-20 million records, and those records will be cross referenced against a number of in memory caches (in Java). Some of these caches will only be 5000 or so records one of them will be close to 180 million records.

Therefore, I only have one index that I set in each SQL query. Sometimes users don't need the index, so the Java end will get a Integer.MIN_VALUE fetch size of a statement that iterates through every record in the database.

Every 2 seconds the JSP page queries the progress and updates a JavaScript progress bar, etc.

Everything works great, and it's blazing fast, but I offer users to cancel the query in progress, by setting a volatile boolean from another thread, and then in the rs.next() check if it's cancelled and break out of the while loop, and properly close DB resources.

It took nearly 45 seconds to cancel on a ResultSet halfway through 10 mil records.

After doing some testing it is the ResultSet.close() method that is delaying a quick cancel to allow the user to go back to editing their parameters. However if I cancel the statement (Statement.cancel()) before closing the ResultSet it closes and exits the search immediately.

Here's the gist of it:

public void search() throws Exception {
    total = getSize(where); // where clause created dynamically and depending on options user chooses may be null
current = 0;
Connection c = getConnection();

    Statement s = c.createStatement();
s.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = s.executeQuery(queryBuffer.toString()); // potentially a query returning a lot of stuff

    logger.info("Full Query " + queryBuffer.toString());

    long stime = System.currentTimeMillis();

    while (rs.next()) {

        Record rec = helper.deriveLightweightRecord(rs);

        current++;

        if (stack.accept(rec)) { // stack of filters to check records against, one has 180 million records in cache
            valid++;
        }

        if (requestCancel) {
            logger.info("Cancel Detected at Search Thread Breaking Now " + System.currentTimeMillis());
            break;
        }

        if (current % 20000 == 0) {

            long etime = System.currentTimeMillis();
            logger.info("Iterated 20000 Records in " + ((etime - stime) / 1000.0) + " Seconds");
           开发者_JS百科 stime = etime;
        }
    }


    try {
        if (requestCancel)
            s.cancel(); // if i don't call cancel, the result set close takes a LONG time >30secs, calling cancel the ResultSet close takes all of a couple millis
            s.close();
            rs.close();
    } catch (Exception e) {
        logger.error("Ignorable for now", e); // occassionally get a SQLException because of cancel
    } finally {
        returnConnection(c);
}
    logger.info("Closed Connection " + System.currentTimeMillis());

    if (requestCancel) {
        logger.info("Checking Request Cancel Now " + System.currentTimeMillis());
        cancelled = true;
        searching = false;
        cancelNotifier.interrupt();
        resetStatus();
    } else {
        current = total;
        searching = false;
    }

 }

Getting to the question, what are the ramifications of cancelling a Statement, since I've never had to use this function before. The javadocs on Mysql Connector and Java API don't really mention too much about Statement.cancel(), save for the fact that it could throw a unsupported operation exception if the underlying RDMS doesn't support it (which MySQL seems to).

Will this be degrading or error prone on a dedicated webserver? Seems to run fine, besides very occassionally throwing a SQLException about trying to close a ResultSet on a cancelled statement. Is this potentially damaging to the the database when streaming ResultSets?

Thanks in advance.