jTDS JDBC drivers with CFMX 7 Enterprise

[MARMC WebMaster]

We are using CFMX Enterprise 7.0.1 (multi-server install) and SQL Server 2000
SP4 (NT Auth only), each on dedicated servers (in the same domain). We are
looking at switching to the jTDS JDBC drivers instead of the DataDirect drivers
due to NT Authentication issues (we have to stop using a domain account to
authenticate CF to SQL). jTDS is working as expected (using a local account on
the SQL server), except for one problem: performance seems to be worse than
the DataDirect (3.5) drivers we are already using, approximately 3 times
slower. For really small resultsets, this is not much of an issue. But as
soon as we get around 500 records or more, the difference is noticable (compare
jTDS at 1180ms and DataDirect at 158ms for a 1000 record resultset).

As far as we know, we have specified the correct JDBC URL in the datasource:


jdbc:jtds:sqlserver://server:1433/db;domain=devserver;sendStringParametersAsUnic
ode=false;cachemetadata=true

So what are we missing? Why is jTDS so much slower?

Also, if there is a way to use the DataDirect drivers to log in across two
servers using a local account on the SQL server, I'd be happy to know it.

[PaulH *ACE*]

MARMC WebMaster wrote:

> authenticate CF to SQL). jTDS is working as expected (using a local account on
> the SQL server), except for one problem: performance seems to be worse than
> the DataDirect (3.5) drivers we are already using, approximately 3 times

that's exactly the opposite of what we normally see. what authentication are you
using (sql or trusted)? haven't really tested the trusted authentication but
using an sql server account (sql server login), jTDS flys.

[MARMC WebMaster]

Actually, the jTDS people were able to solve the problem: set useLOBs=false.
It turns out that we were testing on a table containing a text column. jTDS
was converting it to a CLOB instead of leaving it as a longvarchar.

As soon as we added this parameter, jTDS went from being 3 times slow to being
2 times faster!

And we are using NT authentication only (performance is the same using NT
username/password or letting it pass the service SID/credentials).

[PaulH *ACE*]

MARMC WebMaster wrote:

> Actually, the jTDS people were able to solve the problem: set useLOBs=false.
> It turns out that we were testing on a table containing a text column. jTDS
> was converting it to a CLOB instead of leaving it as a longvarchar.

good catch. if you have a blog it's something worth repeating publicly.

[jonnycattt]

just tried jtds, and i'm seeing a tremendous performance hit on a table with a
bunch of ntext fields, in sql server 2000.

here's the url:
jdbc:jtds:sqlserver://server:1433/dbname;sendStringParametersAsUnicode=false;cac
hemetadata=true;useLOBs=false

otherwise, kept all the defaults in cfadmin.

any advice here?