best jdbc 1.0 centric driver for sql server

[bogachkov]

Hello Joe

Over the past several years, I have found your responses to jdbc
usage/driver related issues to be extremely helpful. I am sure that
you're very busy so I will make my question as brief as possible - the
reason I didn't post this to a newsgroup is that I don't want to hear
any more hype or links to benchmarks (since the vast majority of the
benchmarks don't test concurent/mixed-transaction environments). (I am
also posting a modified version of this message to some newsgroups, but
based on past responses - don't have too much hope that these will lead
to a resolution.) If the only way to answer this question would be to
have a phone conversation, my company would be more than happy to pay a
consulting-service fee for your time.

My situation:
My company has a enterprise level web-app that targets SQL Server. We
don't use J2EE - so we stick to a simple to administer web containers
(JRun,Tomcat). Recently, we have been getting worse and worse
performance from our jdbc driver (we use the free MS jdbc driver) -
things like strange transaction resource handling, chopy overall
performance, etc.. I have spent significant time tweaking it (following
various advice - many times yours - on newgroups... I can go into
details, but I don't want to take up your time). As the project's
architect, I need to do something about this problem - but various
high-level tunnings that I have done to the way we use connections with
this driver haven't significantly improved perfomance under normal
everyday load. (Our queries often span tables with millions of records,
and are relatively dynamic. We use a seperate pool of autocommit-off
connections for writes, and autocommit-on connecions for reads)

So, then, my question - in your expert opinion - what is the BEST
driver from SQL Server 2000 given the following needs:

Things I need it to have are solid jdbc 1.0, solid transaction
handling, decent concurrent load handling, stable implementation of
resource handling (i.e. auto closing result sets when parent statements
close, closing of result sets & statements when connections close) and
support for multiple open statements/result sets per connection.

Things I do not have a direct need for:
Connection Pooling (I keep my own pool of open connections), DataSource
support, distributed transactions or 2-phase commit support, RowSet
variants, jdbc 3.0 autokey generation, J2EE compliance. We do make use
of updatable result sets - but I don't care if it isn't supported since
I can still use MS driver for those, as they are mainly done it batch
jobs).

Things that are nice:
It would also be good to have a driver that can handle prep statement
caching on a driver level (vs connection level) although this isn't a
necessity, and if it is available - it would need to have a
configurable caching strategy (or have a way to be turned off :)

I very much appreciate your help in advance
Thanks
Gary Bogachkov
System Architect
Stericycle Direct Return

[alin]

Gary,

I know this is exactly what you were _not_ looking for, but I have some
marketing garbage coming your way. ;o)

First of all, a disclaimer: I'm a developer of the jTDS JDBC driver, so
at least in theory I can't be totally impartial.

Although jTDS is a JDBC 3.0 implementation with all the implied bells
and whistles its JDBC 1.0 functionality is quite solid. If you'll check
the jTDS Help forum on SourceForge you'll see that most of the requests
we had since the release of 0.9.1 were either caused by the user's lack
of knowledge or bugs in earlier versions. It really sounds like sales
speak, I apologize. jTDS actually has all the features you require,
plus the updateable ResultSets (which are paged, thus much faster than
with the MS driver).

To the benchmarks now: we have a very old benchmark result on our site
( http://jtds.sourceforge.net ), comparing jTDS to some of the other
JDBC drivers for SQL Server. Some companies such as JNetDirect and
DataDirect do not allow benchmark results of their drivers to be made
public, so they are not in that benchmark. If you want, I can provide
you with some more recent results with pretty much all of the available
drivers via email or even better, with some instructions on how to run
the benchmarks yourself.

What I noticed from the benchmarks: jTDS and the DataDirect drivers
were the only drivers that passed all tests (although they were
supposed to be performance tests, the other drivers failed some tests).
The DataDirect drivers are the direct ancestor of the MS driver and
still have the same selectMode=cursor/direct implementation, which is
really counterproductive. In cursor mode (the only one that supports
transactions, as you probably know) _all_ selects create server-side
cursors, using up a lot of resources on the server. The MS driver, as
well as some of the other DataDirect-derived drivers (there are a lot
of them) are only able to fetch one row per request, and that means a
huge number of roundtrips only to retrieve a ResultSet; the DataDirect
driver seems to be able to fetch rows in pages, however, greatly
improving performance; it's still slower than a direct select, which
jTDS uses, but it's ok. The JNetDirect and i-net Software drivers
(which would be the ones I would recommend from the whole bunch) are
close to jTDS in performance but they lack a number of functionalities
(that might not affect you, however).

The bottom line is this: the most stable driver seems to be the
DataDirect one; however it's quite slow, around the same areas as the
MS driver; their stability comes from the intensive testing they do and
the fact that they are probably the most widely used of all drivers.
The JNetDirect and i-net Software drivers are both fast and probably
have all the features you need, but they cost a lot of bucks ($1700 for
the JNetDirect driver seems a lot for 200kb of code). According to my
(notice, "my") testing jTDS is the fastest of the bunch and seems to be
more stable (although not on par with the DataDirect driver).

jTDS is also the recommended choice for SQL Server by a number of open
source projects and companies: Hibernate, JBoss, Atlassian Confluence,
Db-Visualizer are only a few of these. Also, for the moment we're not
making any money from jTDS, so except for our own pride there's nothing
to push us to make statements we can't back up with facts.

I apologize if this is not what you were looking for, but I honestly
hope that's not the case.

Regards,
Alin,
The jTDS Project.