Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Why is so slow?

Skip to first unread message

Otto Perdeck

Nov 19, 2003, 4:13:16 AM11/19/03
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?



Otto Perdeck

Nov 19, 2003, 5:04:19 AM11/19/03
Some additional info:

The connection URL looks like
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" <> wrote in message

Sue Purkis

Dec 1, 2003, 10:51:17 AM12/1/03
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?


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

"Otto Perdeck" <> wrote in message news:<DeHub.201755$>...

Abdullah Kauchali

Dec 7, 2003, 3:09:57 PM12/7/03

"Otto Perdeck" <> wrote in message

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! :) ]

0 new messages