Long running ResultSet.close() when using selectMethod=direct

Question

Hello together

I recognized following behaviour:

When executing a statement, that for example returns 1000 rows and I just execute one next on the result set, the close of the result set reads all teh rest of the data.

Here is some information:

- OS is Windows 7

- Sun JDK 1.7.0_04

- Connection URL: jdbc:sqlserver://localhost:1433;instance=;databaseName=demo;selectMethod=direct, Microsoft jdbc driver 4.0.2206.100

- Client is on the same machine

- SqlServer 2012 (11.0.2100.60) on local system accout, no firewall

Sample code:

String sql = "select * from TESTTABLE"; //TESTTABLE with lots of data
    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
      ResultSet rs = stmt.executeQuery();
      try {
        long startNext = System.currentTimeMillis();
        rs.next();
        System.out.println("Closing result set had [" + (System.currentTimeMillis() - startNext) + "]ms");
      } finally {
        if (rs != null && !rs.isClosed()) {
          long startClose = System.currentTimeMillis();
          rs.close();
          System.out.println("Closing result set had [" + (System.currentTimeMillis() - startClose) + "]ms");
        }
      }
    }

I can see following behaviours:

- selectMethod=cursor: Duration of (first) next is quite long, as the management of cursor is quite expensive, sounds
 reasonable for me

- selectMethod=direct: Duration of colsing result set is long, when having a lot of not yet read records. This is the behaviour that disturbs me. Of course the best way would be to change the statement and to use all data, but this os not always that easy.

I know and read the information here: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/7ceee9a8-09d8-4fbd-9918-0ca065fa182e already and I understand what is going on... but I still don't like it :-), as responseBuffering=full is not really a good idea in terms of client memory and selectMethod=cursor is simply too slow.

My question now: Am I the only one that doesn't like this behaviour? Are there plans to change/ improve that?

Thank you in advance

Patrick

Answer 1

Hello,

Are you sure that your  JDK 1.7.0_04 is coming from SUN ? I was thinking it was an ORACLE product ( but I may go wrong as I have given up ORACLE since 10 years )

http://www.oracle.com/technetwork/java/javase/7u4-relnotes-1575007.html

According to this link , the current version is 1.7.0_04-b22 ( just to be sure that we are writing about the same version )

My own SQL Server 2012 has a version 11.0.3128.0  . I suppose that you are in version RTM and not in SP1 ( you should think to install the SP1 )

http://www.microsoft.com/en-us/download/details.aspx?id=35580

http://www.microsoft.com/en-us/download/details.aspx?id=35575

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Hi Papy

Of course your right, JDK 1.7.0_04 is from Oracle now, since Oracle bought Sun, so it's just a matter of name.

My installation of SqlServer is just a local test installation and it is not important for this behaviour, as it comes from the implementation of the jdbc driver. But yes, you're right, I should update the server :-)

Regards

Patrick