CODEWITHSUNDEEP
javasql-serverlinuxjdbcjtds
Iman Akbari
Iman Akbari

Reputation: 2206

JDBC - Connection extremely slow on Windows, Fine on Linux

Using JDBC to connect to SQL Server on a Windows Server 2008 computer, I've ran into an extremely confusing problem.

While it works great on my Linux laptop both using Microsoft's JDBC driver and jTDS, It works extremely slow when I move the application to a Windows running device a single SQL command would take 4 to 10 seconds to execute!

I've tried all of the following techniques to connect to the database server, almost all of them are fast on Linux and very slow on Windows. Using jTDS data source, I've learned that it works fine on Windows 8 but ALWAYS slow when I move the code on to the computer running the database itself (windows server).

// =============== jTDS

    JtdsDataSource ds = new JtdsDataSource();

    ds.setUser(DB_USERNAME);
    ds.setPassword(DB_PASSWORD);
    ds.setServerName(SERVER_ADDRESS);
    ds.setPortNumber(SERVER_PORT);
    ds.setDatabaseName(DATABASE_NAME);
    ds.setLoginTimeout(server.SQL_LOGIN_TIMEOUT);

    try {
        _conn_ = ds.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }


    // =============== Microsoft

    try {

        String connectionUrl =
                String.format("jdbc:sqlserver://%s:%d;" +
                "databaseName=%s;" +
                "ssl=require", SERVER_ADDRESS, SERVER_PORT, DATABASE_NAME);

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        this._conn_ = DriverManager.getConnection(connectionUrl, DB_USERNAME, DB_PASSWORD);

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        throw new RuntimeException("JDBC not loaded");
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }

    // =============== Apache

    String connectionUrl = "jdbc:sqlserver://"+SERVER_ADDRESS+";database="+DATABASE_NAME+";integratedSecurity=false;";
    String jtdsConnectionUrl = "jdbc:jtds:sqlserver://"+SERVER_ADDRESS+":"+SERVER_PORT+"/"+DATABASE_NAME+"";
    ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(jtdsConnectionUrl, DB_USERNAME, DB_PASSWORD);

    PoolableConnectionFactory poolableConnectionFactory
            = new PoolableConnectionFactory(connectionFactory, null);
    poolableConnectionFactory.setDefaultAutoCommit(true);
    poolableConnectionFactory.setDefaultReadOnly(false);

    GenericObjectPool connectionPool = new GenericObjectPool(poolableConnectionFactory);
    poolableConnectionFactory.setPool(connectionPool);


    this.pooledDataSource = new PoolingDataSource(connectionPool);

It's already a week I'm stuck on this, any kind of help is appreciated.

Upvotes: 1

Views: 2715

Answers (2)

Iman Akbari
Iman Akbari

Reputation: 2206

Well this is not really a true solution, but works. After hours of working on this and trying almost everything (from writing a custom connection pool to using Apache DBCP) I came to realize that the problem starts to exist, AFTER a TCP connection is made to my server.

I came to realize if I reconnect to SQL database with each connection made to the server, the extremely slow behavior simply degrades to not so fast, that is a transaction taking about 50ms instead of 5 seconds which is almost good enough.

I still have no idea why this happens and my bet is on a Windows Server or VMWare network bug since this behavior changes by changing platforms. I hope this helps if anyone else is experiencing same problem.

Upvotes: 1

Szarpul
Szarpul

Reputation: 1571

I dont know if this will help, but as I can see, you are not referring to JTDS driver but to Microsoft JDBC Driver for SQL Server. Try changing 

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

to:

Class.forName("net.sourceforge.jtds.jdbc.Driver");

Upvotes: 0

Related Questions