What is the fastest code to call multiple stored procedures?

Question

Solution 1 :

foreach (var item in itemList)
{
   myContext.ExecuteStoreCommand("EXEC MyProc {0};", item); // Insertion
}

or

Solution 2 :

StringBuilder sb = new StringBuilder();
foreach (var item in itemList)
{
   sb.AppendLine(String.Format("EXEC MyProc {0};", item)); // Insertion
}
myContext.ExecuteStoreCommand(sb.ToString());

Answer 1

both are second one is subject to sql injection, that is for sure

by reading this and this, I agree with kekekela

Answer 2

I would guess Solution 2 because there is less I/O between your application and the database. If speed is all you're concerned with, you could check for yourself by debugging with the System.Diagnostics.Stopwatch utility.

Answer 3

This sounds like an opportunity for a small optimization which is easily tested.

Likely they are very close to the same speed.

Answer 4

Solution 2 is faster as its only a single call to myContext.ExecuteStoreCommand so less overhead from method calls through the context object

Answer 5

The one you haven't mentioned.

Prepare an SqlCommand with the stored procedure's name as the CommandText, the CommandType set to CommandType.StoredProcedure, and the params appropriately added.

Besides the performance gain from not using an ad-hoc query (that'd have to be reparsed each time), you'll also nip most of your current SQL injection issues in the bud.

Answer 6

Second is faster (one call to the database instead of multiple), first is safer since it protects against SQL injection.