We are upgrading our servers to SQL Server 2005 from SQL Server 2000. We currently use the jtds drivers.
I'm interested to know what peoples opinions are of the different jdbc drivers available (in particular the latest Microsoft driver), how they perform with SQL Server 2005 and any other lessons from your collective experience.
Thanks.
I have used the MS provided driver, the jtds driver, as well as the driver from jnetdirect.
Unfortunately none of them is perfect, and all have their own bugs (including of course Microsofts own). The MS provided drivers of course have support for the newest features before the other vendors, but I must say that I have rarely seen significant performance differences between drivers.
My advice would be to make sure that your application reads the driver name and connection URL from a properties file somewhere so that you can easily switch between drivers if you run into problems with whichever one you choose. You'll be glad you did later.
We tried using Microsoft JDBC Driver 2.0 with SQL Server 2005. The problem we were running into was - when calling a stored procedure, the driver was generating the following sql statements -
declare @P1 int
set @P1=1
exec sp_prepexec @P1 output, N'@P0 int', N'EXEC getEmployeeManagers @P0', 50
select @P1
So stored procedures run in the sp_prepexec statements. And later when you close the statement, the sp_unprepare gets called. This seems to be the default behavior of the MS driver. The problem with this is, the overhead to generate a prepared statement and then to close it has performance impact. Why can't the driver just do this -
exec getEmployeeManagers @P0=50
We are now using the jTDS driver and it seems to be performing great.