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

[Otto Perdeck]

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

[Otto Perdeck]

Some additional info:

The connection URL looks like
"jdbc:microsoft:sqlserver://Formelmonum:1433;SelectMethod=cursor".
SelectMethod cursor proved to be necessary because of other driver related
problems. The 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 Perdeck" <otto.p...@xs4all.nl> wrote in message
news:faed9b3c.03111...@posting.google.com...

[Sue Purkis]

Otto,
Since our internal performance testing does include joins and we do
not see this kind of degradation, I need some additional information
to see if I can help pinpoint the problem. Can you give me some idea
of what type of data is in each of these tables?

Thanks.

Sue Purkis
DataDirect Technologies
sue.purkisatdatadirect.com
Please replace the "at" above with @ to reach me by email.

"Otto Perdeck" <Otto.P...@xs4all.nl> wrote in message news:<DeHub.201755$di.45...@amsnews02.chello.com>...

[Abdullah Kauchali]

"Otto Perdeck" <otto.p...@xs4all.nl> wrote in message
news:faed9b3c.03111...@posting.google.com...

Try this:

props.put("sendStringParametersAsUnicode, "false");

on the connection object before hitting your query?

The "nvarchar-unicode-table scan" problems is usually the case for strangely
poor performance. [Joe Weinstein educated me on this one! :) ]