Performance difference in prepared statements compared to jTDS due to differing execution plan

[Chrriis]

Driver version

7.4.1.jre12

SQL Server version

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 18362: )

Client Operating System

Windows 10 Pro (1909)

JAVA/JVM version

OpenJDK 12.0.2+10

Table schema

A simple table with a few columns and one index

Problem description

We would like to switch from JTDS to MS SQL JDBC driver. The problem is that during long data crunching, the MS SQL driver is much slower. Our data crunching, which includes various other things, takes 77 minutes with JTDS and 104 minutes with MS SQL.

1. Expected behaviour: comparable or better performance than JTDS for similar statements.
2. Actual behaviour: some statements in MS SQL JDBC driver are much slower. Our simple test case shows 3x-4x slower.
3. Any other details that can be helpful: micro benchmarks do not work. It is the state of the database as it evolves during the data crunching that exhibits this behavior. Make any change to the database (drop a table, change an index, remove and re-add a constraint, etc.) and the test case disappears. It may have to do with the computed statistics that JTDS is able to use and not MS SQL. Of course, it may also be a misuse or misconfiguration of the MS SQL JDBC driver...

Reproduction code

I narrowed down the test case to a relatively small backup (14MB, 170MB unzipped) and a Java class.

MSvsJTDS_bak.zip
SqlServerJdbcDriverSlowdownTest.zip

• Load the backup to a SQL Server 2017 instance.
• Set the credentials and server information at the top of the Java file, add JTDS JAR file and MS SQL JDBC JAR file to classpath, and run it.

We got these results:
1st Try:
Total time (ms): 2879 (MS)
Total time (ms): 929 (JTDS)

2nd Try:
Total time (ms): 2885 (MS)
Total time (ms): 888 (JTDS)

3rd Try:
Total time (ms): 2798 (MS)
Total time (ms): 993 (JTDS)

4th Try:
Total time (ms): 3057 (MS)
Total time (ms): 947 (JTDS)

On a different client and host machine, we got:
Total time (ms): 5681 (MS)
Total time (ms): 1516 (JTDS)

[ulvii]

Hi @Chrriis,
Thanks for creating the issue. We will investigate and get back to you.

[ulvii]

Hi @Chrriis,
I wanted to share a few details in case they are helpful for your investigations. My initial testing shows that the behavior is query specific. I ran the exact same test with a simpler update query on numeric columns (update numerictable set column2=? where column1 = ?) and both drivers took almost exactly same amount of time to execute.

I also modified your test code a bit and seeing some strange behavior. When I hardcode the ID in the query

UPDATE T_DbPerfTest SET Col29 = x.Col29 FROM (   SELECT Col1, LEAD(Col28) OVER (PARTITION BY Col5, Col2, Col4, Col3 ORDER BY Col28, Col1) AS Col29   FROM T_DbPerfTest   WHERE Col29 IS NULL   AND (Col27 = 1 OR Col28 = ?)   AND Col2 = 240254 ) x WHERE T_DbPerfTest.Col1 = x.Col1 AND T_DbPerfTest.Col29 IS NULL AND x.Col29 = ? AND T_DbPerfTest.Col27 = 1

and update the JAVA code accordingly

    public static void executePreparedStatements(Connection connection, PreparedStatement preparedStatement) throws Exception {
        long[] ids = {
                240254,
                684796,
                684786,
                240102,
                240274,
                240303,
                240332,
                240244,
                685302,
                685331,
                685196,
                685389,
                685228,
                685360,
        };
        for(long id: ids) {
            preparedStatement.setLong(1, 27586214);
            preparedStatement.setLong(2, 27586214);
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
    }

jTDS driver becomes slower too, both drivers perform the same. So parametrizing IDs makes jTDS faster and I am currently not sure why. The only difference between drivers is jTDS calls sp_prepare whereas Microsoft JDBC driver calls sp_prepexec for PreparedStatemets. I will try to analyze this behavior sometime next week, please share your findings too.

[Chrriis]

Thank you very much for the update!

Maybe I should tell more about this schema. This schema contains a primary key (Col1), a conceptual key (Col2, Col3, Col4, Col5) with a versioning column (Col28). A version can contain many rows of different conceptual keys.
Primary keys are (generally) an increasing number, and version column is an increasing number. Rows were added in the order of the versioning column, using statements that declare the keys in the same order.
The purpose of the test statement is to set the value of Col29 to the value of Col28 of the next version (per its conceptual key).

I am not a specialist in statistics computing in databases, but it is possible that the layout after insertion helps running the update statement (only the most recent versions are of interest, so last inserted rows per conceptual keys / PK) as done by JTDS.

You say that the only difference is in using sp_prepexec instead of sp_prepare. But in the test case, we clearly prepare the statement before executing it multiple times. Do you mean that sp_prepexec is invoked at the first batch execution (and sp_execute after that) or used at each batch execution (what would it mean to have a prepare step everytime)? Have you tried modifying the MS code to use sp_prepare like JTDS to see if you get the same result than JTDS (so we are sure there is nothing else at play here, like a subtle difference in the use of the TDS protocol, data types, etc.)?

I will do a few experiments tomorrow, I will let you know if I find anything of interest.

[Chrriis]

I tried another variation of the statement which is less performant but more basic SQL (no window function).
UPDATE T_DbPerfTest SET Col29 = ? WHERE Col2 = ? AND Col27 = 1 AND Col29 IS NULL AND EXISTS ( SELECT 1 FROM T_DbPerfTest b WHERE T_DbPerfTest.Col5 = b.Col5 AND T_DbPerfTest.Col2 = b.Col2 AND T_DbPerfTest.Col4 = b.Col4 AND T_DbPerfTest.Col3 = b.Col3 AND ( T_DbPerfTest.Col28 < b.Col28 OR T_DbPerfTest.Col28 = b.Col28 AND T_DbPerfTest.Col1 < b.Col1 ))
You can try it yourself, just replace the statement in the test case, and comment the "setLong(3, xxx)" line.
Instead of times like "6020 (MS) vs 1512 (JTDS)", I get "14013 (MS) vs 5667 (JTDS)". Indeed a slower statement, but still similar slowdown ratio between MS and JTDS.

[Chrriis]

I tried changing various settings in the connection string of SQL Server, but no improvement:

responseBuffering=full
enablePrepareOnFirstPreparedStatementCall=true
sendTimeAsDatetime=false
disableStatementPooling=false
statementPoolingCacheSize=10
serverPreparedStatementDiscardThreshold=0

I also tried to change JTDS parameters to see how the driver behaves. Still fast when changing:

TDS=7.0
maxStatements=0

JTDS became as slow as the MS driver when changing:
prepareSQL=2

I was not surprised by that last change result. The JTDS documentation says:

2: sp_executesql is used (fast)
3: sp_prepare and sp_cursorprepare are used in conjunction with sp_execute and sp_cursorexecute (faster, SQL Server only)

[Chrriis]

The test case uses a batch but I found this is irrelevant. If you replace preparedStatement.addBatch(); with preparedStatement.execute(); and comment preparedStatement.executeBatch(); you get the same speed problem.

[Chrriis]

So parametrizing IDs makes jTDS faster and I am currently not sure why.

The funny thing is that if you hardcode the 1st and 3rd parameters in the query (only leaving the ID parameterized), then the query is still fast for JTDS and slow for MS.

[ulvii]

Hi @Chrriis ,

Do you mean that sp_prepexec is invoked at the first batch execution (and sp_execute after that) or used at each batch execution.

sp_prepexe is executed only once when statementPoolingCacheSize=10;disableStatementPooling=false;enablePrepareOnFirstPreparedStatementCall=true;. See SQL Profiler outputs below(I am hardcoding 1st and 3rd parameters):

MS JDBC

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 bigint',N'UPDATE T_DbPerfTest SET Col29 = x.Col29 FROM (   SELECT Col1, LEAD(Col28) OVER (PARTITION BY Col5, Col2, Col4, Col3 ORDER BY Col28, Col1) AS Col29   FROM T_DbPerfTest   WHERE Col29 IS NULL   AND (Col27 = 1 OR Col28 = 27586214)   AND Col2 = @P0 ) x WHERE T_DbPerfTest.Col1 = x.Col1 AND T_DbPerfTest.Col29 IS NULL AND x.Col29 = 27586214 AND T_DbPerfTest.Col27 = 1        ',240254
select @p1

exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360

jTDS

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P0 bigint',N'UPDATE T_DbPerfTest SET Col29 = x.Col29 FROM (   SELECT Col1, LEAD(Col28) OVER (PARTITION BY Col5, Col2, Col4, Col3 ORDER BY Col28, Col1) AS Col29   FROM T_DbPerfTest   WHERE Col29 IS NULL   AND (Col27 = 1 OR Col28 = 27586214)   AND Col2 =  @P0  ) x WHERE T_DbPerfTest.Col1 = x.Col1 AND T_DbPerfTest.Col29 IS NULL AND x.Col29 = 27586214 AND T_DbPerfTest.Col27 = 1',1
select @p1

exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360
exec sp_execute 1,240254
exec sp_execute 1,684796
exec sp_execute 1,684786
exec sp_execute 1,240102
exec sp_execute 1,240274
exec sp_execute 1,240303
exec sp_execute 1,240332
exec sp_execute 1,240244
exec sp_execute 1,685302
exec sp_execute 1,685331
exec sp_execute 1,685196
exec sp_execute 1,685389
exec sp_execute 1,685228
exec sp_execute 1,685360

[Chrriis]

@ulvii I looked at the code and figured that out :)

That being said, I disagree when you say that it is the only thing that differs. I simplified the test case and added logs of the bytes that are sent for a given execution of the statement. I see less bytes sent with JTDS although we should be under the same conditions in both cases: statement was prepared long time ago, there were the same number of executions, etc.

Of course it is possible that I made a mistake in my test case, so please do cross-check my findings!

The zip
MSvsJTDS2.zip
contains:

• An Excel file showing the differences at the byte level for one statement execution (about 58 bytes vs 36 bytes).
• The simplified test case. Note the system prints showing my section where I logged the bytes.
• My modified MS IOBuffer class with system prints to log the bytes.
• My modified JTDS SharedSocket class with system prints to log the bytes.

It seems that the JTDS prepared statement executions do not have the header (size 22). Maybe only the preparation packet needs it and adding it to executions creates problems?

Thanks a lot for your help!

[Chrriis]

I think I succeeded modifying the code so that the sp_prepexec call is just a sp_prepare instead. That way we can compare the same scenario between MS and JTDS. Well, unless I made a mistake, total time has not changed: MS is still 3x slower...
So, if the above is correct (please confirm!), then it means the bad performance could be due to the way the connection is established (maybe a different setting?) or the version of the protocol. @ulvii What is your take?

[cogman]

I've done some profiling that may or may not be interesting for this case (I'm attaching it.)

Here's what I see.

It looks like the number of requests are roughly the same between the two drivers, yet the MS driver requests appear to take a much longer time.

The MS driver is spending all of it's time here

void SqlServerJdbcDriverSlowdownTest.main(String[]):52	1
void SqlServerJdbcDriverSlowdownTest.executePreparedStatements(Connection, PreparedStatement):102	1
int[] com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch():2076	1
void com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(TDSCommand):223	1
void com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(TDSCommand):248	1
boolean com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(TDSCommand):2979	1
boolean com.microsoft.sqlserver.jdbc.TDSCommand.execute(TDSWriter, TDSReader):7194	1
boolean com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute():2675	1
void com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement$PrepStmtBatchExecCmd):2786	1
TDSReader com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(boolean):7579	1
boolean com.microsoft.sqlserver.jdbc.TDSReader.readPacket():6418	1
int com.microsoft.sqlserver.jdbc.TDSChannel.read(byte[], int, int):2023	1
int java.net.Socket$SocketInputStream.read(byte[], int, int):68	1

On the flip side, the Jtds path looks as follows for where most of it's time is spent

void SqlServerJdbcDriverSlowdownTest.main(String[]):68	8
void SqlServerJdbcDriverSlowdownTest.executePreparedStatements(Connection, PreparedStatement):102	8
int[] net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch():1051	8
SQLException net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeMSBatch(int, int, ArrayList):463	8
void net.sourceforge.jtds.jdbc.TdsCore.executeSQL(String, String, ParamInfo[], boolean, int, int, int, boolean):1086	8
void net.sourceforge.jtds.jdbc.TdsCore.wait(int):4127	8
int net.sourceforge.jtds.jdbc.ResponseStream.peek():99	8
int net.sourceforge.jtds.jdbc.ResponseStream.read():114	8
void net.sourceforge.jtds.jdbc.ResponseStream.getPacket():477	8
byte[] net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket$VirtualSocket, byte[]):731	8
byte[] net.sourceforge.jtds.jdbc.SharedSocket.readPacket(byte[]):850	8
void java.io.DataInputStream.readFully(byte[]):170	8
void java.io.DataInputStream.readFully(byte[], int, int):200	8
int java.net.Socket$SocketInputStream.read(byte[], int, int):68	8

This was using JTDs 1.3.1 and MS 7.4.1

I ran sql server locally in a docker container.

As far as I can tell, it looks like both are doing essentially the same thing. Some protocol or connection setting seems to be the likely culprit here, IMO.

Interestingly enough, in both profiling sessions I did (One with jtds first, one with it second, to make sure that DB caching wasn't messing with things), it looks like the first 2 batches for the MS driver ran faster than JTDs (~80ms). It was the subsequent requests that ended up taking ~1 second.

JTDS consistently spent around 160ms per request.

Is there some "expected batch size" or "look ahead" parameter going on that the MS driver isn't sending?
sql_server_performance.zip

[cogman]

@Chrriis
Given your request information and the TDS docs

https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/b05b006b-3cbf-404b-bcaf-7ec584b54706

It looks to me (and I could be wrong) that the MS driver is setting the "SQLBatch" packet data section and JTDs is not.

[Chrriis]

Thanks @cogman. I analyzed the bytes that were sent vs TDS spec and suspected they were the MARS section stuff, so thank you for the confirmation. I think JTDS uses an older version of the protocol that does not have those.

About profiling, I too found that time was spent reading, which is why I started analyzing the sent messages in the first place, but they seemed to be the same.

So I think you are right saying there is a protocol or connection setting, but I fail to find which could be causing this huge difference. Any help is welcome there! :)

[Chrriis]

@ulvii I found the issue! The root cause is that the statement that is passed to the server is slightly different between MS and JTDS: the MS driver adds spaces at the end of the statement when replacing the ? parameters with @Px ones. As a quick test, you can simply add .trim() at the end of the SQLServerConnection.replaceParameterMarkers(xxx) method and MS becomes as fast as JTDS using my test cases.

Of course, the fix must be more subtle than this: the statement that is passed to the driver should be left untouched when replacing the parameters. What I mean is that

• if I pass: <space><space>UPDATE FOO SET x=? WHERE y=?<space>
• then I should get: <space><space>UPDATE FOO SET x=@P0 WHERE y=@P1<space>
• and not: <space><space>UPDATE FOO SET x=@P0 WHERE y=@P1<space><space><space><space><space><space><space>

With that fix, the MS driver would do as is told (passing command as is) and for us it would become as fast as JTDS (at least for this case).

Please let us know what you think! :)

[Chrriis]

The fix in SQLServerConnection.replaceParameterMarkers(xxx) is simply to:

• Remove the last while loop that adds the spaces.
• return new String(sqlDst, 0, dstBegin);

That way, the statement is untouched, spaces that were part of the original statement are preserved and no new spaces are added.