Why is com.microsoft.jdbc.sqlserver.SQLServerDriver so slow?

Thread view:

Otto Perdeck - 18 Nov 2003 10:48 GMT

I found out that the MS JDBC driver is terribly slow when doing anything
slightly more complex than "select a,b,c from x". Keeping it that simple
gives a fast response. But adding even a simple where clause makes it very,
very slow (from 2 seconds suddenly down to 45 minutes). We've found a
work-around by first creating a temporary table with the select result, then
selecting all from that temporary table (with a really simple selection).
But this is pretty bizarre. No other JDBC driver we're using requires such
measures (Oracle, mySQL, or commercial alternatives to the MS driver) - and
we're using the same SQL in all cases.

Anyone any explanation?

Thanks

Otto

Reply to this Message

Joe Weinstein - 18 Nov 2003 17:17 GMT

> I found out that the MS JDBC driver is terribly slow when doing anything
> slightly more complex than "select a,b,c from x". Keeping it that simple
[quoted text clipped - 7 lines]
>
> Anyone any explanation?

What connection properties are you supplying? Are you using selectMethod=cursor?
Are your queries involving varchar indexes? If so, add this property:
p.put("sendStringParametersAsUnicode", "false");

Joe Weinstein at BEA

> Thanks
>
> Otto

Reply to this Message

Otto Perdeck - 19 Nov 2003 08:15 GMT

The connection URL looks like
"jdbc:microsoft:sqlserver://Formelmonum:1433;SelectMethod=cursor".
SelectMethod cursor proved to be necessary because of other driver related
problems. Varchar indices?? What's that?

But my real question is how it can be that a query like

SELECT weight,field_2,caseid FROM omegauser148_SAMPLES

takes about 2 seconds, while it takes about 45 minutes to do

SELECT weight,field_2, smptbl.caseid FROM omegauser148_SAMPLES AS
smptbl, omegauser148_DATASETS AS dstbl WHERE dstbl.datasetid = 56 AND
dstbl.caseid = smptbl.caseid

and this only for the MS JDBC driver. When using J-SQL (NetDirect) for
example, there is no significant difference in speed.

-Otto

> > I found out that the MS JDBC driver is terribly slow when doing anything
> > slightly more complex than "select a,b,c from x". Keeping it that simple
[quoted text clipped - 17 lines]
> >
> > Otto

Reply to this Message

Joe Weinstein - 19 Nov 2003 17:02 GMT

> The connection URL looks like
> "jdbc:microsoft:sqlserver://Formelmonum:1433;SelectMethod=cursor".
> SelectMethod cursor proved to be necessary because of other driver related
> problems. Varchar indices?? What's that?

nevermind... Your query doesn't use varchar search arguments, so you don't
need to know...

> But my real question is how it can be that a query like
>
[quoted text clipped - 8 lines]
> and this only for the MS JDBC driver. When using J-SQL (NetDirect) for
> example, there is no significant difference in speed.

This may well have to do with the (necessary for othe reasons) selectMethod
setting. Please run a tiny 15-line standaone program that gets a driver
connection without the selectMethod setting, and I'll bet you see the better
performance. Are you using a PreparedStatement or a plain Statement?
Joe Weinstein at BEA

> -Otto
>
[quoted text clipped - 37 lines]
>>>
>>>Otto

Reply to this Message

Otto Perdeck - 21 Nov 2003 11:53 GMT

Joe,

Thx for thinking along, but as I already mentioned, there is no problem with
a simple select, but there is with a slightly more complex one. I'm using
the same URL all the time, which includes the SelectMethod=Cursor (and this
for good reasons, because the #$&^#*$& driver otherwise refuses multiple
open ResultSet's).

I'm not searching for a work-around, that I already have. I'm looking for an
explanation. How can it be that a small change in the SQL makes such a huge
difference in performance? And how can it be that the MS JDBC driver is the
only one (as far as I know) to have this behaviour?

I also would like to see confirmation from other users that this MS JDBC
driver is very lousy.

-Otto

> > The connection URL looks like
> > "jdbc:microsoft:sqlserver://Formelmonum:1433;SelectMethod=cursor".
[quoted text clipped - 64 lines]
> >>>
> >>>Otto

Reply to this Message

Joe Weinstein - 21 Nov 2003 16:47 GMT

> Joe,
>
[quoted text clipped - 3 lines]
> for good reasons, because the #$&^#*$& driver otherwise refuses multiple
> open ResultSet's).

You are missing my point. I think it is an effect of the more complex query
and the way the DBMS must create a cursor for it, when operating in that
mode. The driver itself does nothing to your SQL. Simple or complex, the
driver just sends it directly to the DBMS.
So, it you have the time, just run a plain non-cursor-mode connectiom
and a single plain statement with your more complex query. Let us know if it
is also slower, or if it's as fast as it should be.

Joe Weinstein at BEA

> I'm not searching for a work-around, that I already have. I'm looking for an
> explanation. How can it be that a small change in the SQL makes such a huge
[quoted text clipped - 89 lines]
>>>>>
>>>>>Otto

Reply to this Message

Why is com.microsoft.jdbc.sqlserver.SQLServerDriver so slow?

Free Magazines