What is faster: SqlCommand.Parameters[string] or .Parameters[int] ?

Question

Which way is preferred?

SqlCommand = new SqlCommand(query);

command.Parameters.Add("@Foo");
command.Parameters[0].Value = Foo;
command.Parameters.Add("@Bar");
command.Parameters[1].Value = Bar;

// or

command.Parameters.Add("@Foo");
command.Parameters.Add("@Bar");
command.Parameters["@Foo"].Value = Foo;
command.Parameters["@Bar"].Value = Bar;

Answer 1

Two other options:

command.Parameters.AddWithValue("@Foo", Foo);

command.Parameters.Add("@Foo").Value = Foo;

Additionally, I don't think the speed difference between any of them would be enough that you should choose based on it; Pick the one that is the most readable to you and your team.

Answer 2

Strictly speaking it's faster to use the int overload. The reason being that the underlying collection is stored as an array and uses integer offsets. When you call the string overload, it will translate the parameter to an int, and then essentially call the int overload.

The real question though is "does it matter"? It's highly unlikely that this will matter much unless you have quite possibly thousands of parameters. And even then you can't confirm it's a problem until a profiler says it is.

Answer 3

You do not need to create parameters and then Add them and then Fill them. This will quickly become completely unwieldy.

The "best practices" approach is to use AddWithValue:

command.Parameters.AddWithValue("@ParamName", Value);

As far as "speed" goes, this is a classic case of premature optimization. You simply don't need to be focused on such small speed differences when your overall design still needs so much work.

Answer 4

The problem with using numbered parameters regardless of speed is that if the underlying stored procedure's parameter order changes your code is going to need a recompile.

Imagine the proc is used from two different pieces of code, but you are unaware of the second usage you add a new parameter with a default value in the middle of the parameter list. You have just in advertently broken things because you were using numbered parameters.

The question isn't which is faster. The question is which is more robust, because the speed difference is probably relatively minor unless you are hitting that stored procedure extremely hard. Which probably means you need to come up with some solution to optimise you system rather than looking for the kind of small optimisations that this would bring.