MSSQL JDBC Driver behavior

By Pramod Sadalage on April 22, 2012 11:57 AM

My latest project involves talking to MS-SQL Server using the JDBC driver and Java. While doing this we setup the database connection and had a simple SQL to get the firstname and lastname for a unique userid from the applicationuser table in the database.

SELECT first_name,last_name 
FROM application_user 
WHERE user_id = ?

Given the above SQL, we did not think too much about performance as the user_id was indexed. The java code as below was used to run the SQL.

Connection conn = db.conn(DATABASE_URL);
try {
    PreparedStatement stmt = prepare(conn,
            "SELECT first_name, last_name " +
            "FROM application_user " +
            "WHERE user_id = ?");
    stmt.setString(1, username);
    ResultSet resultSet = stmt.executeQuery();
    return extractResults(resultSet);
} catch (SQLException e) {
    e.printStackTrace();
}
return null;

When writing integration tests we started noticing that the SQL was taking about 6 seconds to execute. The same SQL would execute inside 100 milliseconds on the MSSQL query analyzer. The friendly DBA’s on our team pointed out that the SQL was doing some data type conversion as the user_id field was of the type VARCHAR but the SQL sent by the the JDBC driver set the data type to NVARCHAR because of this the index was not being used and the SQL took more than 6 seconds to execute. Researching this topic further we decided to cast the variable to VARCHAR as shown below.

Connection conn = db.conn(DATABASE_URL);
try {
    PreparedStatement stmt = prepare(conn,
            "SELECT first_name, last_name " +
            "FROM application_user " +
            "WHERE user_id = cast(? AS VARCHAR");
    stmt.setString(1, username);
    ResultSet resultSet = stmt.executeQuery();
    return extractResults(resultSet);
} catch (SQLException e) {
    e.printStackTrace();
}
return null;

The above code executed under 100milliseconds and showed us that the data types being used did not match the datatype in the database. We later found out that the MS-SQL JDBC driver does this to properly deal with unicode characters. This behavior can be turned off using the sendStringParametersAsUnicode flag on the database connection. Once this flag is set to false on the connection, then all the SQL we issue do not need the cast

Connection conn = db.conn(DATABASE_URL
                + ";sendStringParametersAsUnicode=false");
try {
    PreparedStatement stmt = prepare(conn,
            "SELECT first_name, last_name " +
            "FROM application_user " +
            "WHERE user_id = ? ");
    stmt.setString(1, username);
    ResultSet resultSet = stmt.executeQuery();
    return extractResults(resultSet);
} catch (SQLException e) {
    e.printStackTrace();
}
return null;

Off course this only works if there is no unicode data in your database if there is any unicode data in the database, we will have to revert to casting individual SQL statements.

Categories:

  • JDBC

Tags:

  • development,
  • jdbc
  •  Atom feed

My Books

Reading list

Switch: How to Change Things When Change Is Hard
The Power of Now
A New Earth
SQL Anti patterns
The 4-Hour Workweek
Loving Our Kids On Purpose
Tuesdays with Morrie
The Secret
Presentation Zen
Programming Ruby 1.9
Release It!
The Toyota Way
Agile Data Warehousing
Outliers: The Story of Success
India After Gandhi
Why Does Software Cost So Much?:
It Happened in India
Oracle Tuning: The Definitive Reference
Ship it!
Service-Oriented Architecture
My Job Went to India
Let My People Go Surfing
Practices of an Agile Developer
Rails Recipes
Programming Ruby
The Art of SQL
Execution: The Discipline of Getting Things Done
The Monk Who Sold His Ferrari
The Greatness Guide
Who Will Cry When You Die?
Zen and the Art of Motorcycle Maintenance
Freakonomics
Head First Design Patterns (Head First)
Agile Database Techniques
Hibernate in Action (In Action series)
The World Is Flat
Guns, Germs, and Steel

Search

Tag Cloud

  • design
  • mongodb
  • agile dba
  • BI
  • database
  • nosql
  • oracle
  • admin
  • Agile Data
  • automation
  • books
  • constraints
  • Data
  • data conversion
  • database link
  • dba
  • development
  • documentdb
  • EDW
  • failover
Tweets by @pramodsadalage

Categories

  • Agile DBA (7)
    • Best Practices (12)
    • Improving Design (6)
  • BI (2)
  • Broadcast (13)
  • JDBC (1)
  • Learning (11)
    • Mistakes I Learnt from (2)
  • MongoDB (5)
  • NoSQL (3)
  • Open Source (2)
  • Oracle (6)
  • Ruby (2)

All Content Copyright © 2012 Pramod Sadalage. All Rights Reserved.
gipoco.com is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.