It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes SQL Server + PreparedStatement Oddity Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Server + PreparedStatement Oddity" Watch "SQL Server + PreparedStatement Oddity" New topic

SQL Server + PreparedStatement Oddity

David Yutzy
tumbleweed and gunslinger
Ranch Hand

Joined: Jun 29, 2001
Posts: 192
We have a high traffic web environment and found a significant CPU spike when using PreparedStatements versus Stored Procedures in SQL Server 2000.

Server: 2 gig RAM, Dell 2650, dual 2.1 ghz CPU, Windows 2000, SQL Server 2000
Application Server: Sun ONE AS 7, Solaris v5.9, 2 gig RAM, dual 1.9 ghz CPU

Message we saw in SQL trace resemble:

Before anyone cries fowl, no, I certainly did NOT define each parameter as nvarchar(4000), that is what JDBC used, disregarding our explicite

Using the exact same application and code, except moving the call into a SP versus PS, we get almost zero CPU activity and the speed of the SP is sub 1 second during normal activity and the PS is near 15-30 seconds, depending on activity.

What I'm trying to figure out is why this causes such a slow response from PS. When I've used PS with MySQL, I've not had any slowness, but with SQL Server, I can demonstrate this behavior very easily and it's quite dramatic.

[edited to add line breaks]
[ November 04, 2004: Message edited by: Jeanne Boyarsky ]
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

Is it safe to assume there is an index on jsessionid in the SQL server version? If so, it may have to do with transaction settings or locking. You may want to verify the query time for a single user.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
David Yutzy
tumbleweed and gunslinger
Ranch Hand

Joined: Jun 29, 2001
Posts: 192
Yes, we have this indexed on jSessionID.

We typically encounter "blocking" issues with SQL Server anyway every once in a great while, but more so when using PS because of the huge CPU spike, which seems to cause everything else to run slower which seems to cause blocking scenarios, etc...

Using SP there is no real perceptable CPU activity at all with the same code.

When running SQL Profiler and tracing the activity, you can see from my previous post that it's creating temp procedures with every call and we've been able to watch the CPU spike on our staging server which we can isolate to a single user and it spikes to 100% every time I do a PS.

The tables have about 6.5 million records and the SQL is like:

It returns about 10 fields.

Using a SP is instant and no CPU, PS is 100% CPU spike.
I agree. Here's the link:
subject: SQL Server + PreparedStatement Oddity
It's not a secret anymore!