none
MS JDBC Driver Performance Observations (versus JTDS). RRS feed

  • General discussion

  • I've been using the JTDS JDBC Driver for years as it has excellent performance.  However it does not support datetime2 (improved precision) fully - it gets treated as datetime.  So I thought I would try out the MS JDBC Driver again to see how the latest version performs.  The following briefly describes my specific usage and performance observations.

    I'm performing single row INSERTs via a PreparedStatement invoking a Stored Procedure with IN parameters only (no OUT or returned value).  This is running inside an Application Server with connection pooling and prepared statement caching enabled in the connection pool configuration.  Each INSERT is a separate transaction (this is an OLTP application).

    Tests were performed with identical setup and load, the only difference being the JDBC Driver.

    Compared to using JTDS, the MS Driver is:

    • allocating 7 times the memory,
    • consuming 3 times the CPU and
    • generating 3.5 times the network traffic.

    Examining the call stacks there are a number of things that look suspicious:

    1. It looks like the Prepared Statements are not being cached and reused.  Things like 'buildPreparedStrings', 'replaceParameterMarkers' etc don't sound like the sort of thing that should be needed on each call.  Perhaps the Driver doesn't support statement pooling at all.  This is also implied by the fact that the connection setting 'disableStatementPooling' is only allowed to be 'true'.
    2. A lot of memory is allocated in 'startResponse'  which is odd given that these calls have no result (other than success).
    3. Memory is allocated on every call to obtain the identity of the connection (SQLServerConnection.toString) which I have a strong suspicion is a constant for the life of the connection and need only be generated once.

    I've also experimented with 'sendStringParametersAsUnicode=false' but this has no impact on the network traffic (it does with JTDS and is an option on both Drivers).

    If any of the MS engineers are reading this, I'd be happy to provide further details of the above observations and work together on solutions.  I might be way off, but there appears to be some low hanging fruit here.  If I am way off, I'd happily take any advice on how I might get better results (but please don't suggest batching etc - this is OLTP).

    I can supply images of the aforementioned call stacks, memory, CPU and network load once my msn account privileges allow.


    • Edited by jim_b_o Friday, January 23, 2015 5:31 PM formatting
    Friday, January 23, 2015 5:30 PM

All replies

  • Hello,

    Thanks for taking the time to evaluate the performance of the Microsoft Driver.  Can you share with us the call stacks and details about the test environment, like versions of SQL Server, Windows, client application details and client OS?

    Thanks


    Lfsantos
    Program Manager

    Thursday, July 9, 2015 9:53 PM
    Moderator
  • I can supply images of the aforementioned call stacks, memory, CPU and network load once my msn account privileges allow.

    Hi,

    can you supply images of the aforementioned call stacks, memory, CPU and network load, please.

    Thanks,
    Chris

    Thursday, October 8, 2015 7:29 AM