#543 jTDS slower than Una

[Thomas Boerkel]

I put together a simple example that basically does the same thing as our application when talking to the database. Of course, this is no "real benchmark", but our app is. And the percentage time differences between Una and jTDS are very similar in this test and our app.

Using jdk 1.6.0u2, SQL Server 2005 SP2 and AdventureWorks sample database from Microsoft.

Drivers are i-net Una 6.07 and jTDS 1.2.2.

This is the output on my machine (runs each test twice because of caching and JVM hotspot optimizations):

Running Una CursorTest... Time: 4310
Running Una CursorTest... Time: 4118
Running jTDS CursorTest... Time: 5207
Running jTDS CursorTest... Time: 4917
Running Una SingleSelectTest... Time: 5405
Running Una SingleSelectTest... Time: 5333
Running jTDS SingleSelectTest... Time: 6259
Running jTDS SingleSelectTest... Time: 6195

Attaching the test source.

[Thomas Boerkel]

Test source

[m.hilpert]

Logged In: YES
user_id=667728
Originator: NO

I just tried to test i-net UNA, but it fails during connection and getting meta data:

java.lang.AbstractMethodError: com.inet.tds.ag.getDatabaseMajorVersion()

[Thomas Boerkel]

Logged In: YES
user_id=557385
Originator: YES

That is really strange. I never had an issue with that. But the metadata is not actually needed, so you could comment that line.

[momo]

Currently I cannot reproduce this, maybe it's outdated? In my (limited) testing, Una is about as fast as jTDS.

[Thomas Boerkel]

I ran the same test on my new (faster) machine, this time with SQL 2008 x64 (but still with the SQL 2005 AdventureWorks DB). Using Una 6.07, jTDS 1.2.2.

Running Una CursorTest... Time: 2177
Running Una CursorTest... Time: 2253
Running jTDS CursorTest... Time: 2801
Running jTDS CursorTest... Time: 2720
Running Una SingleSelectTest... Time: 2415
Running Una SingleSelectTest... Time: 2431
Running jTDS SingleSelectTest... Time: 2566
Running jTDS SingleSelectTest... Time: 2629

[momo]

I'm not sure if your numbers really indicate something is wrong with jTDS. I re-run the test a few dozen times with all different memory settings and also tried some changes in your code, but the differences between both drivers (jTDS 1.2.2/Una 7.05) are always marginal and look more like error in measurement. See a few results below.

I'd assume the differences may be caused by memory settings or the GC kicking in at some point or something like that. All in all it seems close to impossible that the database code of jTDS is stupid/wrong here, so I'm closing the bug. However, fine tuning the jTDS code is of course still on my to-do list and may also wipe out the differences you currently see in your environment.

Thanks for your support!

Cheers,
momo

---

Running Una CursorTest... Time: 5001
Running Una CursorTest... Time: 4110
Running jTDS CursorTest... Time: 4715
Running jTDS CursorTest... Time: 4116
Running Una SingleSelectTest... Time: 7113
Running Una SingleSelectTest... Time: 6449
Running jTDS SingleSelectTest... Time: 7069
Running jTDS SingleSelectTest... Time: 6741

Running Una CursorTest... Time: 4493
Running Una CursorTest... Time: 4361
Running jTDS CursorTest... Time: 4520
Running jTDS CursorTest... Time: 4304
Running Una SingleSelectTest... Time: 6773
Running Una SingleSelectTest... Time: 6661
Running jTDS SingleSelectTest... Time: 6584
Running jTDS SingleSelectTest... Time: 6511

Running Una CursorTest... Time: 4651
Running Una CursorTest... Time: 4289
Running jTDS CursorTest... Time: 4505
Running jTDS CursorTest... Time: 4194
Running Una SingleSelectTest... Time: 6989
Running Una SingleSelectTest... Time: 6651
Running jTDS SingleSelectTest... Time: 6691
Running jTDS SingleSelectTest... Time: 6556

number of iterations increased tenfold:

Running Una CursorTest... Time: 52013
Running Una CursorTest... Time: 51710
Running jTDS CursorTest... Time: 49893
Running jTDS CursorTest... Time: 51301
Running Una SingleSelectTest... Time: 75177
Running Una SingleSelectTest... Time: 73643
Running jTDS SingleSelectTest... Time: 74280
Running jTDS SingleSelectTest... Time: 75041

[Thomas Boerkel]

I see, that your numbers are similiar. That's strange, because on my old and on my new machine, Una is faster.

And the same happens with our JUnit tests that run about 3 hours. With jTDS, they are 10% slower.

[momo]

Well, strange! Reproducible 10% less performance measured in a 3 hour test really doesn't look like a measurement error. Think that is worth further investigation, even if that amount isn't necessarily a show-stopper. Do you have the possibility to re-run the tests with the current Una 7.05, just to rule out that Una suffered a performance degradation between version?

[Thomas Boerkel]

With Una 7.05:
Running Una CursorTest... Time: 2204
Running Una CursorTest... Time: 2105
Running jTDS CursorTest... Time: 2747
Running jTDS CursorTest... Time: 2681
Running Una SingleSelectTest... Time: 2340
Running Una SingleSelectTest... Time: 2341
Running jTDS SingleSelectTest... Time: 2611
Running jTDS SingleSelectTest... Time: 2664

Still jTDS is slower here. I am using JDK 6.0u16.

I can't run our JUnit tests with the newest Una, because the trial is limited to 2 connections and we only have an older Una licensed.

[momo]

Thanks a lot for testing this. Now we know that Una version doesn't matter, there unfortunately are still tons of things that may cause the different measurement results. And whatever is found to be the cause should give a good idea how to improve jTDS. I do not think chances are high we could work out each and every possible factor but I could at least try a number of hardware/software/network configuration I am able to put together. But this will take a lot of time and my options are still limited. Please be aware that I cannot afford to buy any hard or software for this project so I completely rely on what I already have here. And keep in mind that jTDS is a hobby and I just started contributing a few weeks ago (being the one and only active developer right now).

But back to topic ;-) I did some profiling of the running test and don't think speeding up jTDS code would help that much. The client is virtually idle and throughput seems to be limited exclusive by the SQL server (constantly taking 100% CPU on the server machine). That of course would perfectly match my measurement results. So the big question is - if your test virtually measures server performance here, what exactly does it measure in your case? My first guess would be network performance and that's a point where jTDS may indeed do something suboptimal. I'll see if I can somehow slow down my connection and produce some test results...

[Thomas Boerkel]

Thanks for your efforts!

Regarding network: Everything is on one machine here.

[momo]

Well... I finally managed to reproduce the described problem by moving to an older machine:

Running Una CursorTest... Time: 3687
Running Una CursorTest... Time: 3125
Running jTDS CursorTest... Time: 4469
Running jTDS CursorTest... Time: 4547
Running Una SingleSelectTest... Time: 3546
Running Una SingleSelectTest... Time: 3484
Running jTDS SingleSelectTest... Time: 4234
Running jTDS SingleSelectTest... Time: 4125

What kind of puzzles me is the fact, that the whole test is noticeable faster on this box running both, the client and the server on one machine. The hardware I run the tests first should be much more performant than what I used now. Core2 Dual 2,6GHz Client + Quad-Opteron 3GHz server against Athlon 1,8Ghz - 8GB Ram client and 8GB Ram server against 768MB Ram... but maybe the operating System also plays an important role here. Anyway, I now have a test setup for preproducing the problem and can now try to track it down.

I'll let you know if I find out anything.