none
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?

    Monday, March 12, 2007 11:48 AM

Answers

  • 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!

    Monday, March 12, 2007 11:59 AM

All replies

  • 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!

    Monday, March 12, 2007 11:59 AM
  • So in a loop i must use the first one (Parameters.Add). Thanks
    Monday, March 12, 2007 12:50 PM
  •  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!

    Monday, March 12, 2007 1:11 PM
  • thanks
    Monday, March 12, 2007 1:25 PM