Geek Answers Handbook

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

Which method 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;
+4
source share
4 answers

Two other options:

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

In addition, I do not think that the difference in speed between any of them will be sufficient so that you can choose on it; Choose the one that is the most readable for you and your team.

+13
source

Strictly speaking, it is faster to use int overloading. The reason is that the underlying collection is stored as an array and uses integer offsets. When you call overloading a string, it converts the parameter to int, and then essentially causes int overloading.

The real question, though this is "does it matter"? It is very unlikely that this will be of great importance if you do not have, perhaps, thousands of parameters. And even then you cannot confirm this with a problem until the profiler says that it is.

+9
source

You do not need to create parameters and then add them and then fill them. It will quickly become completely bulky.

The "best practice" approach is to use AddWithValue :

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

As for speed, this is a classic case of premature optimization. You just don't need to focus on such small differences in speed when your overall design still needs such a lot of work.

+4
source

The problem with using numbered parameters, regardless of speed, is that if changing the order of the stored procedures changes your code, recompilation is required.

Imagine that proc is used from two different parts of the code, but you are not aware of the second use, you add a new parameter with a default value in the middle of the parameter list. You just advertised broken things because you used numbered parameters.

The question is not the one that is faster. The question is which is more reliable, because the speed difference is probably relatively insignificant if you do not press this stored procedure is extremely difficult. This probably means that you need to come up with some kind of solution to optimize your system, and not to look for small optimizations that it will bring.

+1
source

More articles:


All Articles