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 ]