Parameters.Add faster than Parameters.AddWithValue

Question

Hi,

i noticed that when i use Parameters.Add it is more fast than Parameters.AddWithValue. The difference time work between 2 method it is very important, expecially when they are used into a loop. Here there's an example:

' Using Parameters.Add

cmd.Parameters.Add("@id", SqlDbType.NVarChar)

cmd.Parameters.Add("@name", SqlDbType.NVarChar)

cmd.Parameters.Add("@address", SqlDbType.NVarChar)

For i As Integer = 0 To 100

     cmd.Parameters.Item(0).Value = "example"

     cmd.Parameters.Item(1).Value = "example"

     cmd.Parameters.Item(2).Value = "example"

Next

' Using Parameters.AddWithValue

For i As Integer = 0 To 100

     cmd.Parameters.AddWithValue("@id", "example")

     cmd.Parameters.AddWithValue("@name", "example")

     cmd.Parameters.AddWithValue("@address", "example")

Next

Is that possible , or i wrong something to use method?

Answer 1

In you exampel the first one is faster, because you create the parameters only once and change only the value. In the second example you always add new parameters to it. If you look at the parameters after the first loop you have 3 in the collection. After the second loop you should have 100 + 3 in the collection.

That's why your second loop is slower: it creates 100 parameters, while the first one creates three and changes then only the value of the parameters.

Parameters.Add and Parameters.AddWithValue always create a new parameter. That's what's making it slower: object creation takes some time!

Answer 2

So in a loop i must use the first one (Parameters.Add). Thanks

Answer 3

pol86 wrote:

So in a loop i must use the first one (Parameters.Add). Thanks

To have speed measurement done properly you need to do this for the first loop:

For i As Integer = 0 To 100

cmd.Parameters.Add("@id", SqlDbType.NVarChar)

cmd.Parameters.Add("@name", SqlDbType.NVarChar)

cmd.Parameters.Add("@address", SqlDbType.NVarChar)

     cmd.Parameters.Item(0).Value = "example"

     cmd.Parameters.Item(1).Value = "example"

     cmd.Parameters.Item(2).Value = "example"

Next

 

Now look what is faster :) Both should be even!

Answer 4

thanks