jTDS driver

Discussion in 'Java' started by JScoobyCed, Jun 6, 2005.

  1. JScoobyCed

    JScoobyCed Guest

    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,

    --
    JSC
     
    JScoobyCed, Jun 6, 2005
    #1
    1. Advertising

  2. wrote in comp.lang.java.programmer:
    > 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?

    --
    Antti S. Brax Rullalautailu pitää lapset poissa ladulta
    http://www.iki.fi/asb/ http://www.cs.helsinki.fi/u/abrax/hlb/

    [1385 messages expunged from folder "Spam"]
     
    Antti S. Brax, Jun 6, 2005
    #2
    1. Advertising

  3. On 06.06.2005 12:38 Antti S. Brax wrote:

    > wrote in comp.lang.java.programmer:
    >
    >>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?
    >


    Or poorly designed statements?
     
    Thomas Kellerer, Jun 6, 2005
    #3
  4. JScoobyCed

    JScoobyCed Guest

    Antti S. Brax wrote:
    > wrote in comp.lang.java.programmer:
    >
    >>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?
    >


    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?

    --
    JSC
     
    JScoobyCed, Jun 6, 2005
    #4
  5. JScoobyCed

    JScoobyCed Guest

    Thomas Kellerer wrote:
    > On 06.06.2005 12:38 Antti S. Brax wrote:
    >
    >
    >> wrote in comp.lang.java.programmer:
    >>
    >>
    >>>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?
    >>

    >
    >
    > 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.

    --
    JSC
     
    JScoobyCed, Jun 6, 2005
    #5
  6. On 06.06.2005 13:17 JScoobyCed wrote:

    > 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
     
    Thomas Kellerer, Jun 6, 2005
    #6
  7. JScoobyCed

    Bryce Guest

    On Mon, 06 Jun 2005 17:24:31 +0700, JScoobyCed <> wrote:

    >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.

    --
    now with more cowbell
     
    Bryce, Jun 6, 2005
    #7
  8. JScoobyCed

    Bryce Guest

    On Mon, 06 Jun 2005 18:17:04 +0700, JScoobyCed <> wrote:

    >Antti S. Brax wrote:
    >> wrote in comp.lang.java.programmer:
    >>
    >>>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?
    >>

    >
    >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?

    --
    now with more cowbell
     
    Bryce, Jun 6, 2005
    #8
  9. JScoobyCed wrote:
    >
    > 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.

    --
    Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
    ==============================================================
    * The Ultimate DBMS is here!
    * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
     
    Lee Fesperman, Jun 6, 2005
    #9
  10. JScoobyCed

    JScoobyCed Guest

    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.

    --
    JSC
     
    JScoobyCed, Jun 7, 2005
    #10
  11. JScoobyCed

    Guest

    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.
     
    , Jun 7, 2005
    #11
  12. 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)
     
    Lee Fesperman, Jun 7, 2005
    #12
  13. JScoobyCed

    JScoobyCed Guest

    [LONG] Re: jTDS driver

    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
     
    JScoobyCed, Jun 8, 2005
    #13
  14. JScoobyCed

    Guest

    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. :eek:)

    Alin.
     
    , Jun 8, 2005
    #14
  15. JScoobyCed

    JScoobyCed Guest

    wrote:
    >
    > Hope this helps more than confuses. :eek:)
    >
    > 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
     
    JScoobyCed, Jun 9, 2005
    #15
  16. JScoobyCed

    JScoobyCed Guest

    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
     
    JScoobyCed, Jun 21, 2005
    #16
  17. JScoobyCed

    Guest

    That's great news! :eek:)

    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.
     
    , Jun 21, 2005
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ralph Friedrich
    Replies:
    1
    Views:
    491
    Jim Lewis
    Nov 7, 2003
  2. Lloyd Dupont
    Replies:
    4
    Views:
    1,982
    Dave Young
    Aug 7, 2003
  3. Rico
    Replies:
    8
    Views:
    2,776
    Alin Sinpalean via JavaKB.com
    Dec 31, 2004
  4. XunLing
    Replies:
    0
    Views:
    1,029
    XunLing
    Mar 10, 2005
  5. stef
    Replies:
    1
    Views:
    1,404
    mdtsdca
    Oct 30, 2007
Loading...

Share This Page