#464 JT400 SQL Query Latency on Windows Server 2019 or 2022

[Alex Furner]

Hi

We have a number of Java apps that run on Windows servers. When we upgraded them to Windows 2019 or 2022 we noticed a big increase in response times. Jumping from sub second to several seconds.
We looked into this in some detail and this issue seems to be a big jump in response times to SQL queries when using the JT400 driver.

I created a simple class to reproduce the issue. (Code sample below)
This code performs a simple query against a small table in a loop to get some good sample data.

    public static void main(String[] args) throws Exception {

        try (Connection con = DriverManager.getConnection(getJdbcUrl())) {
            long start = new Date().getTime();
            for (int i = 0; i < 100; i++) {

                try (Statement stmt = con.createStatement()) {
                    try (ResultSet rs = stmt.executeQuery("Select OKCUNO from m3fdbdev.ocusma where okcono = 100 and okcuno = '" + i + "'")) {
                        if (rs.next()) {
                            String customer = rs.getString("OKCUNO");

                        }
                         System.out.println("Run " + i);
                    }
                }

            }
            long end = new Date().getTime();
            long diff = end - start;
            System.out.println("Took " + diff + " ms");
        }

    }

When I ran a profiler against it we found that the stmt.executeQuery method was the culprit.

On Windows Server 2012 or 2016 the average time was around 1ms
On Windows Server 2019 or 2022 the average response time was around 40ms

This broadly falls in line with the impact we saw in production, particularly around calls with lots of small SQL queries.

I've tried lots of versions of the JT400 JDBC driver up to the latest, all show the same response times.
I've tried running it on old and the latest versions of Java 8 and Java 11.
We've run the same test against a SQL Server database, the latency did not increase across windows versions.

For information we are running DB2 on a IBM Power 8, OS version V7R3M000

I'd really appreciate any help you can provide with this.
I'm more than happy to run any test of debugging (we've setup 4 fresh virtual machines with Windows Server 2012, 2016, 2019 and 2022)
Also if I can contribute to a fix in any way I'd be more than happy to.

I'm a little stuck on where to go next

Thanks

[John Eberhard]

I suspect the problem is that windows uses the nagle algorithm to reduce network load. This causes TCP/IP requests to be delayed -- hoping for a buffer to fill up. See bug 454 for more information.

[Alex Furner]

Hi John

Thanks for your speedy response. I've tested the suggestion in bug 454....

"This can be deactivated via com.ibm.as400.access.AS400JDBCDataSource if you call the method setTcpNoDelay (true)"

This did the trick in our sample app.

Our framework pools the connections and doesn't allow easy access to the underlying implementation so we'll wait for your upcoming release with new JDBC connection property

Thanks