Win a copy of Machine Learning with R: Expert techniques for predictive modeling this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

SQL Server + PreparedStatement Oddity

tumbleweed and gunslinger
Posts: 192
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
author & internet detective
Posts: 39540
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
David Yutzy
tumbleweed and gunslinger
Posts: 192
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
Poop goes in a willow feeder. Wipe with this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!