SQL Server Performance Forum – Threads Archive

SqlCommand – ExecuteNonQuery

Hello, I’ve created a very simple test app that calls a stored procedure (which does nothing, just takes a few parameters and returns nothing). When I call this sproc from an SqlCommand, it runs about 1000 times a second. Unfortunately we need this to be much higher, and I see no reason why it shouldn’t be, given the fact that the stored procedure doesn’t do anything. Is there something I’m missing? Some points about our environment: – The stored procedure isn’t being recompiled, I checked with perfmon.
– We have 300 connections in our connection pool and the connection pool does not fill up.
– Our application requires the ability to call about 5000 stored procedures a second (it’s a very high end OLTP system with a lot of users). At this point, the bottleneck is simply the overhead in calling ExecuteNonQuery and even when we do a table insert in the sproc, the timing stays the same.
– We’re using the Microsoft Enterprise Library to do the connection, so the connection objects are being reused … there’s no connection open overhead. Does anyone have any ideas with regards to what we can do to improve performance? Does anyone have better benchmarks than 1000 sproc calls per second with ExecuteNonQuery? Any help would be greatly appreciated!

---

– did you specify "stored procedure" in your commandtipe ?
– what kind of isolation level are you using ?
– do the used parameters datatype match the stored proc’s prameters (exact !) (use db_datatype and length)

---

Thank you very much for your reply. To answer your questions. 1. Yes, we specify "stored procedure" as the commandtype.
2. We’re using read uncommitted.
3. Yes, we set the parameters exactly, and I’ve tried caching the parameter object and not caching the parameter object. I changed from TCP/IP to shared memory and this put me up to 1500 calls a second, but that’s still really low. Any other suggestions?

---

– are there SQL-transactions involved ?
–

---

Yes, the stored procedure contains a begin and commit transaction statement.

---

]]>