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 ]
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.
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.