• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL Server + PreparedStatement Oddity

 
tumbleweed and gunslinger
Posts: 192
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
David,
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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
I hired a bunch of ninjas. The fridge is empty, but I can't find them to tell them the mission.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic