jTDS driver

[JScoobyCed]

Hi,

I am working on a multi-user web-application (about 100 concurrent
users). We use Tomcat 4.1.30, J2SE 1.4.2, MS SQL 2000 (JNDI pooled
connection).
We are meeting some performances problem when a lot of users are
connected. We have profiled and "jmetered" our application, and one of
the result show we have some slow access to DB, even with JNDI and
connection pool.
I have been suggested to try JTDS JDBC driver instead of MS SQL drivers.
Our QA dept. has tested and we don't find much differences in the result.
Is there any specific optimization we could do (in term of the way we
build query, batch, settings, ... anything) to make full benefit of this
driver.
Anybody has experience in this driver to replace MS driver?

Thank you,

 

[Antti S. Brax]

connected. We have profiled and "jmetered" our application, and one of
the result show we have some slow access to DB, even with JNDI and
connection pool.

Are you really sure your bottleneck is the database _driver_
instead of the _database_ itself?

 

[Thomas Kellerer]

Are you really sure your bottleneck is the database _driver_
instead of the _database_ itself?

Or poorly designed statements?

 

[JScoobyCed]

Are you really sure your bottleneck is the database _driver_
instead of the _database_ itself?

We do have also some DB design issue that we are correcting. This to
prevent (N+1) queries when 1 query could be done. This is a part of the
work.
But I have been suggested another driver that is supposely faster. This
is the question I have for you guys Is there a way/config that jTDS
is better for or not?

 

[JScoobyCed]

Or poorly designed statements?

Yes, we do have some (many) "poorly designed statements". We are almost
done in the refactoring of our DB layer/queries and schema optimization.
But that would be gravy if the driver itself could spare us some
precious hundreds (even tens) of milliseconds per query. If this driver
offers no advantage in this way, then we'll stick to the MS driver.

 

[Thomas Kellerer]

We do have also some DB design issue that we are correcting. This to
prevent (N+1) queries when 1 query could be done. This is a part of the
work.
But I have been suggested another driver that is supposely faster. This
is the question I have for you guys Is there a way/config that jTDS
is better for or not?

The point is: if you have serious problems with your queries (missing indexes,
wrong where clause etc) then even a faster driver won't help you. The driver
will only speed up data transfer between the server and the client, but not the
processing *on* the server.

Taking your comments into account (without knowing the details though) I would
very much doubt that you will see a big difference. But then, why not simply
test it? Apart from changing the URL and driver class, there shouldn't be any
further changes to be made.

Thomas

 

[Bryce]

I have been suggested to try JTDS JDBC driver instead of MS SQL drivers.
Our QA dept. has tested and we don't find much differences in the result.
Is there any specific optimization we could do (in term of the way we
build query, batch, settings, ... anything) to make full benefit of this
driver.
Anybody has experience in this driver to replace MS driver?

I use the jTDS driver exclusivly. Never used the MS SQL drivers.

 

[Bryce]

We do have also some DB design issue that we are correcting. This to
prevent (N+1) queries when 1 query could be done. This is a part of the
work.
But I have been suggested another driver that is supposely faster. This
is the question I have for you guys Is there a way/config that jTDS
is better for or not?

Are you pooling connections/statements?

 

[Lee Fesperman]

I am working on a multi-user web-application (about 100 concurrent
users). We use Tomcat 4.1.30, J2SE 1.4.2, MS SQL 2000 (JNDI pooled
connection).
We are meeting some performances problem when a lot of users are
connected. We have profiled and "jmetered" our application, and one of
the result show we have some slow access to DB, even with JNDI and
connection pool.
I have been suggested to try JTDS JDBC driver instead of MS SQL drivers.
Our QA dept. has tested and we don't find much differences in the result.
Is there any specific optimization we could do (in term of the way we
build query, batch, settings, ... anything) to make full benefit of this
driver.
Anybody has experience in this driver to replace MS driver?

Post your query on comp.lang.java.databases. A developer on JTDS occasionally drops by
and should be able to give you additional info.

 

[JScoobyCed]

Thanks all for your advices.
Again, I know that a good DB layer on the application and a good DB
design are the major factors to efficient DB access. We are working on
this. We did already pool the connections, we had also our transactions
as short as possible to prevent blocking other users. Still we have some
design issue, indexes to add, queries to re-write. We are almost done.
I thought my question was simple: is there any settings that would make
a difference between MS SQL drivers and jTDS (that means the processing
time spent in the driver classes, provided our application
refactoring/redesign is completed and optimal) ?
From Thomas answer, it looks like I have been misleaded to this driver,
as no real difference seems visible.
We did benchmark both drivers in the same environment/conditions,
without significant results (not yet with our new architecture, it's
coming ). Thus my question of any specific settings/design to really
use the power of jTDS.
Now, I guess I shouldn't spend too much time on this as it appears that
this driver won't help us much.
I will come back to the suggested database newsgroup when we are ready
to benchmark again.
Thanks again.

 

[alin]

JSC,

I am a jTDS developer; maybe I will be able to help you figure out if
jTDS will help you or not.

First of all what kind of benchmark did you run? The MS driver has one
very serious limitation that should be easily visible in any benchmark:
you have to set the selectMethod property to cursor in order to be able
to use transactions; in cursor mode the MS driver will open a server
side cursor for each SELECT and fetch the rows one by one (a network
request is made for each row in part). Running in direct mode
(selectMethod=direct) on the other hand, even in the limited situations
that it works in, will cache the whole server response into memory (no
idea why); a difference in performance should be visible in this case
too.

Let me know if I can be of any help.

Alin.

 

[Lee Fesperman]

I am a jTDS developer; maybe I will be able to help you figure out if
jTDS will help you or not.

Let me know if I can be of any help.

Thanks for joining us over here, Alin.

JSC ... Alin is the jTDS developer I referred to from c.l.j.d.

 

[JScoobyCed]

JSC,

I am a jTDS developer; maybe I will be able to help you figure out if
jTDS will help you or not.

First of all what kind of benchmark did you run? The MS driver has one
very serious limitation that should be easily visible in any benchmark:
you have to set the selectMethod property to cursor in order to be able
...

Alin.

Thanks a lot for coming here (thank you to Lee for having brought you in
).
Now I am not sure we should continue the Thread here as it could be
better in c.l.j.d...
Anyway, as we are going to discuss benchmark and some general questions,
I guess it could be interesting here.
We do set our selectMethod=cursor (a typical URL is:
"jdbc:microsoft:sqlserver://dbsrv:1433;DatabaseName=dbV2;SelectMethod=cursor")
We use JNDI connection, with connection pool provided by Tomcat DBCP.
For our benchmark, we run in two versions of our software. Let's call V1
the older version with all the memory leaks, poorly designed stuff
(code, query, logging, etc, really bad ) and V2 our newly improved
version.
The soft is a web-application, running on Tomcat with IIS as web server,
MS SQL (for the benchmark, but we support Oracle, MySQL (!), WebSphere
and WebLogic).
We have an important set of JMeter scripts (created by proxy capture of
our application). Running those scripts against our application takes 2
days (just start the servers, start the tests and let it run for 2 days
to get results). This is because scripts are ran several times, and with
different number of concurrent Thread (1, 5, 10, 20, 50, 70, 100, 150...
in our V2, we break at 150 users where we start to see Java Exceptions.
It used to be 70 with V1 ). It covers the whole application and
generate a report per Use Case.
From the first benchmark, we have found threethings: OutOfMemory
errors, Java Exceptions at DB levels (some connection closed, some
duplicate PKs, ...), and bad response times.
We have then concentrated our profiling sessions (EclipseProfiler) on
the worse Use Cases (the one with most errors and long execution time).
We solved a lot of things: some excessive/useless loops, some useless
Object instanciation, lots of pb in our DB layers (queries, connection
pool, time consuming transactions, unsynchronized concurrent
access,...), etc
We have fixed a lot and now we have our V2 (candidate). The results are
impressive. And we still have things to fix.

Now, the reason why I came to jTDS is that somebody suggested to have a
look at jTDS.
So we ran our JMeter scripts, and we didn't see any significant
differences (that was on V1, we didn't test on V2 yet). That is V1 with
MS driver and V1 with jTDS driver gave similar results (in term of
response time, we didn't look at errors for this test).

In jTDS, what we are looking for is at first: speed. I am not sure that
this driver is design for this purpose, and even then, I don't know if
the gain will be significant. There are many factors that will achieve
speed: well design DB schema, properly architectured DB access, properly
designed (sequences of) query.
We have already improved some of this, but to do more, maybe we should
follow some pattern that will make full use of jTDS power. We are now
modifying some time consuming transactions to batch queries (whenever
possible).

In short (finally ), what we'd like to know is what settings, driver
configuration, mode in which jTDS is unbeatable?

Thank you for your time.

 

[alin]

Now, the reason why I came to jTDS is that somebody suggested to have a
look at jTDS.
So we ran our JMeter scripts, and we didn't see any significant
differences (that was on V1, we didn't test on V2 yet). That is V1 with
MS driver and V1 with jTDS driver gave similar results (in term of
response time, we didn't look at errors for this test).

In jTDS, what we are looking for is at first: speed. I am not sure that
this driver is design for this purpose, and even then, I don't know if
the gain will be significant. There are many factors that will achieve
speed: well design DB schema, properly architectured DB access, properly
designed (sequences of) query.
We have already improved some of this, but to do more, maybe we should
follow some pattern that will make full use of jTDS power. We are now
modifying some time consuming transactions to batch queries (whenever
possible).

jTDS is faster in most situations than other JDBC drivers for SQL
Server and yes it was designed and refactored and tested for that. It
is also becoming at least as stable as the top commercial drivers;
maybe you should also test for errors. However the fact of the matter
is that unless you're transferring large amounts of data to and from
the server the major factor affecting performance is network latency,
i.e. even if jTDS processes some response twice as fast as the MS
driver, the time it will take to send the request and receive the
answer from SQL Server is orders of magnitude greater than the
processing time so it will make no difference whatsoever. This is not
taking into account the fact that (AFAI understand) your application is
a web app and there's an extra layer of network communication there.

In short (finally ), what we'd like to know is what settings, driver
configuration, mode in which jTDS is unbeatable?

This might be stretching it a little; jTDS isn't unbeatable. It's just
pretty fast; there will always be some situations in which other
drivers will be marginally faster, mostly because jTDS maintains
compatibility with _very_old_ SQL Server and Sybase versions and is
both a JDBC 2.0 and 3.0 implementation (no other driver AFAIK will work
_with_the_same_binaries_ on both Java 1.3 and 1.4 or later).

But this is compared to the commercial drivers; the MS driver is a
particularly bad implementation (probably so in order to convince Java
programmers that Java is slow and more unstable than .Net or whatever).
There's the selectMethod problem I explained in my previous post,
there's the fact that prepared statements aren't actually prepared but
sent again and again on each execution, there's the fact that it does
pretty complex SQL parsing compared to jTDS, there's no statement
caching, no real batching support and there's the undeniable fact that
MS has only fixed a few dozen minor bugs in about 4 years (!) although
bug reports and feature requests abound (jTDS has had a few hundred
bugfixes in the same time period and a lot of new features added).

But let's get over trashing the MS driver. There are a few things you
should look at when setting up jTDS; all of these are at least touched
in the jTDS FAQ ( http://jtds.sourceforge.net/faq.html ). In the
current (1.0.3) version you should take a look at the
sendStringParametersAsUnicode property (also important with other SQL
Server drivers), prepareSQL (which controls how and if statements are
prepared), maxStatement (the size of the prepared statement cache) and
namedPipe (which could make a huge difference if SQL Server and the JVM
are running on the same server). In the current development version
(and the upcoming 1.1 release) there are properties to control how much
data is cached to memory before dumping to disk in the case of
concurrent statement execution, using fast forward only cursors instead
of direct, firehose cursors (this should help with concurrency, but it
will affect direct measured performance), newly implemented support for
prepareSQL=3 (the new default, it isn't affected by transaction
rollbacks) and meta data caching for prepared statements (improves
performance in some extreme prepare-execute-close cases with up to 50%
-- e.g. with Hibernate and rather complex result sets).

As you can see, there are a lot of things you should/could look at and
there's no single answer to how jTDS should be set up. We have tried to
come up with some sensible defaults, that should provide good
performance in most cases (and stability -- e.g. the metadata caching
can break things if the schema changes so it is disabled by default).

The two major things you can do are (i) enable named pipes if running
on the same server (this will render timeouts useless) and/or (ii) get
the current develiopment version (which is pretty stable, we're close
to releasing it) and try the new changes (they are already documented
in the HTML documentation).

Hope this helps more than confuses. )

Alin.

 

[JScoobyCed]

Hope this helps more than confuses. )

Alin.

Thanks a lot Alin. We are setting up a new benchmark. Unfortunately, our
live environment has two separate servers (hardware) for Tomcat and MS SQL.
I will post here when we got some results.

 

[JScoobyCed]

Hi again,

We have finished benchmarking our application, with MS driver and jTDS
driver comparison. Wow ! It's impressive.
After finding the best settings (in term of memory, threads, tomcat
settings, timeouts, etc), we have mostly 40% better results in our Use
Cases with te jTDS driver.
Thank you for your concerns Alin.

 

[alin]

That's great news! )

In case you have any other problems with jTDS (including performance
issues) don't hesitate to let us know on the jTDS forums on
SourceForge.

Alin,
The jTDS Project.