Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > jTDS driver

Reply
Thread Tools

jTDS driver

 
 
alin@earthling.net
Guest
Posts: n/a
 
      06-07-2005
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.

 
Reply With Quote
 
 
 
 
Lee Fesperman
Guest
Posts: n/a
 
      06-07-2005
http://www.velocityreviews.com/forums/(E-Mail Removed) wrote:
>
> 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.

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
================================================== ============
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
 
Reply With Quote
 
 
 
 
JScoobyCed
Guest
Posts: n/a
 
      06-08-2005
(E-Mail Removed) wrote:
> 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.

--
JSC
 
Reply With Quote
 
alin@earthling.net
Guest
Posts: n/a
 
      06-08-2005
JScoobyCed wrote:
> 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.

 
Reply With Quote
 
JScoobyCed
Guest
Posts: n/a
 
      06-09-2005
(E-Mail Removed) wrote:
>
> 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.

--
JSC
 
Reply With Quote
 
JScoobyCed
Guest
Posts: n/a
 
      06-21-2005
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.

--
JSC
 
Reply With Quote
 
alin@earthling.net
Guest
Posts: n/a
 
      06-21-2005
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.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
JDBC/JTds getMoreResults trouble stef Java 1 10-30-2007 05:27 PM
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x8fc Thread 0x934 DBC 0x437b94 Jet'. ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr bazzer ASP .Net 1 03-24-2006 04:20 PM
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x8fc Thread 0x934 DBC 0x437b94 Jet'. ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr bazzer ASP .Net 0 03-24-2006 02:22 PM
problems about logging level | log4j jtds jboss sql-server XunLing Java 0 03-10-2005 11:25 AM
Tomcat and jTDS - not that stable... Rico Java 8 12-31-2004 11:36 AM



Advertisments