Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Server + PreparedStatement Oddity

 
David Yutzy
tumbleweed and gunslinger
Ranch Hand
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 ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34401
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
Ranch Hand
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic