WEB SQL2000 SP21 Performance

I was wondering if anyone can share their experience with the performance of WEB at SP21 level with
WIN2K / SQL2000 / IIS / WS3.5 / MS JDBC?

In one instance, I'm seeing a printer prompt when submitting a UBE taking ~30 seconds.

SQL gets 1 simple query, but it goes berserk for about 20 seconds retrieving the data (which takes under a second from ISQLW) using 100% of one CPU.

No logging anywhere. Fat clients are fine.

Same takes about 1-2 seconds with SQL7 / Merant JDBC...

It can't be normal, can it? I seem to remember seeing something similar before, but so far I couldn't recall where nor why.

Regards,
Alex.

I have exactly the same issue.

Someone told me it has something to do with the MDAC SP2.7SP1 - which, of course, is installed with SQL 2000 SP3.

I haven't tried downgrading yet - but try installing MDAC 2.5 - it should speed it back up ?

We have seen this problem and the problem is jdbc driver... I think if you install the latest version of jdbc from MS it speed up but it is not as good as merant driver

I have tried the latest MS JDBC SP1 (ver. 2.2) - exactly same results...

It was the same even before SQL SP3 with the previous MDAC version, plus 2.5
is a bit too old and is not supported...

Regards,
Alexander Pastuhov
Pastuhov Consulting Pty. Ltd.
E-Mail: [email protected]
Mobile: 0414 453 433
WWW: http://members.optushome.com.au/apastuhov/index.htm

install the latest version of jdbc from MS it speed up but it is not as good
as merant

Interesting regarding the merant driver, isn't the ms jdbc driver a merant
driver under the covers?
Regards,
Kieran Fitzgerald

Re: RE: WEB SQL2000 SP21 Performance

Seems to be quite different. Could be heavily modified?..

Update: I have found another system here where the same operation (printer prompt) takes ~13 seconds with the same config (SQL2000 / MS JDBC), so this does appear to be the standard problem.

I'm surprised nobody has complained loud enough before. This has to be painful!

I'll be trying a few things - let's see if this can be fixed.

Regards,
Alex.

Alex,

How is your JDBC URL connect string configured?
One of my clients had the same issue, and we changed the connect string to not use UNICode and all application sped up. The connect string looks like this:

JDV7334=jdbc:sqlserver://ENTERPRISE:1433;databasename=JDE_Development;SelectMethod=cursor;sendStringParametersAsUnicode=false|S|TESTDTA|JDE|xxxxxx



Hope this helps.

Andy

RE: RE: WEB SQL2000 SP21 Performance

Have you tried updating the table structure of the two tables, cant remember
the KG doc off the top of my head. Am on a site with this config tomorrow so
will check for you if they have the same problem there.
Regards
Kieran Fitzgerald

Yes, it's exactly what we have here, although, it is my understanding that
the "SelectMethod=cursor" was a work-around for old OW SP's before SP18 and
is now not required, because this parameter is now set programmatically per
connection anyway.

Regards,
Alexander Pastuhov

not use UNICode and all application sped up. The connect string looks like
this:
JDV7334=jdbc:sqlserver://ENTERPRISE:1433;databasename=JDE_Development;Select
Method=cursor;sendStringParametersAsUnicode=false|S|TESTDTA|JDE|xxxxxx

I have just tested some of the parameters floating round on this conversation
topinc here (on a live site) and found that this string gives the best
performance with sp20/mssql2000/latest msjdbc driver
Business Data -
TEST=(jdbc:microsoft:sqlserver://SERVERNAME:1433;databasename=JDE_DEVELOPMENT
;SelectMethod=cursor;SendStringParametersAsUnicode=false)
Even after reloading websphere, the performance is impressive, when it is
normally horrendously slow caching objects for the first time...
I believe from research that you can drop the unicode parameter after sp22 as
it is set programatically. See sar no 6615111
Regards
Kieran Fitzgerald