Re: MS SQL Server 2005 JDBC Driver Performance Issue

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Angel,

I base the statement on the fact that direct traces against our production
environment reveal that the only RPC completion events (event type 10) other
than internal processes such as TASKMAN are 'sp_preparesql' and
'sp_unprepare' while all other user stored procedure completion events map to
event type 43. I would be more than happy to submit more information along
with actual data for analysis if I can do it without opening a service ticket.

Additionally, the trace data records that approximately 50% of our CPU
utilization is eaten up in the type 10 events. Now this could be that the
trace is recording CPU cycles 'twice' (once against the RPC and again against
the SP) and the net cost is almost zero, but it could also mean that the only
gain I am getting is cache hits against going back to Ad Hoc!

As for your comments on v1.1 and configurable packet size, I can say that
for my environment that is HUGE. Although most folks may not work in an
environment where it matters, each packet has a base memory cost that exceeds
the 4K default (I want to say 12K from memory) and with the BOL indicating
that the 384K buffer SQL uses is non-configurable, we could push a lot more
data a lot quicker if the median package is even close to 4K. Of course your
statement that the driver can tweak to trade memory for performance indicates
that someone is 'holding back' some configuration info somewhere.

I'm looking forward to further comments, and again I'll mention that if
information is required that is more detailed than appropriate to tackle
here, I can be contacted via email over yahoo by the display name I am using.

BC

"Angel Saenz-Badillos[MS]" wrote:

Bill,
Welcome to the JDBC newsgroup, glad to have you.

As far as I am aware we don't loose the benefits of RPC, I have verified by
looking at the profiler trace. Can you give me a rundown of the scenario you
are looking at and why you believe we are not performant here?

As far as the original discussion, I have worked with Richard Yeo et.al to
try to understand their scenario, preliminary testing based on source code
they provided resulted in performance being on par with other drivers for a
standalone case. Unfortunately they are still seeing slowdowns when using
their entire frameworks, hopefully we will be able to hear from them if they
figure out what the possible causes for this would be.

On to the v1.1 driver! as you may know we are currently in ship mode trying
to get v1.1 out the door, there is going to be a great performance tweak
included in this driver release, specially if you are working with large
lobs. You will now be able to tweak the PacketSize (TDS buffer that you use
to communicate with the server) internal testing suggest that setting the
default packetsize to 8k instead of the 4k default gives significant
performance improvements for some scenarios. We have additionally tweaked
our driver to trade off memory for performance when using packetsize (it
will use more memory but be much faster) in these scenarios.

I am really looking forward to people trying out PacketSize when they run
into scenarios where they think that our driver is not performant, based on
customer feedback we will be able to make further performance improvements
in future driver versions.

--
Angel Saenz-Badillos [MS] Data Programmability
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://blogs.msdn.com/angelsb/




"fundba" <fundba@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D84EFE7A-4BE3-4116-B782-ADE64F618E28@xxxxxxxxxxxxxxxx
Starting with the disclaimer that I'm a DBA looking for answers and not a
coder with a solution, I'll mention what sent me here and see if it meshes
with this issue...

We are also running the SQL 2005 driver against SQL 2000 (build 2187) and
we
have found that no matter WHAT you do (based on available info), straight
stored procedure calls are still packaged and delivered as prepared
statements so we 'lose' all of the benefits of RPC. I suspect we are
losing
15-30% in dearly needed throughput because of this.

Is this an oversight, a limitation, or a case where the development team
can't find the needle in the haystack?

Since this is a first visit, I can be found on yahoo mail by the given
screen name with ideas or solutions.

Bill Clark


"Richard Yeo" wrote:

With the jTDS driver, SQL Server 2005 managed inserts at the rate of:
966.88
records/sec. This is effectively twice that of the Microsoft driver

And in Sybase, the records are inserted at the rate of: 829.18
records/sec.

Shame on Microsoft and Sybase for writing such badly performing JDBC
drivers!!!

We are now going to start end to end testing with the jTDS driver. Peter
T
thanks for recommending this driver.

Only once it has completed end to end testing will i be happy.

"Robert Klemme" wrote:

Richard Yeo wrote:
Peter

If you are porting an application particularly one as hard on
performance
(calls many stored procs per page using JDBC) as ours the performance
the
application receives from the JDBC Driver and RDBMS combination is
very
important!

Our test is very valid given the ported DB needs to perform at
similar
levels. We are testing all areas of performance not just the driver.
You
can't just test one area. You have to test each link and identify the
weak
link in the chain which is clearly the JDBC driver.

Just to throw in another opinion: since you're running against
different
databases not only the driver *implementation* may be the issue but
network protocols may differ! Also, did you try out different
connection settings? There are actually settings of the JDBC driver (I
know for jTDS but I believe this is true for MS as well) that affect
performance.

Did you do the comparison test with jTDS yet?

Kind regards

robert




.

Relevant Pages