0

This is what I found when digging into Sql Server for three executions of a parameterized query batch;

enter image description here

It seems that because of the @Person_Name length variation, Sql query engine is creating new execution plans for the same query. I am generating a parameterized query through a loop and not specifying SqlDbType Length.

What I want to have is all three queries using the same execution plan as everything should be identical except the values.

Here is the VB.Net code. For query.

Dim fields_Count As Integer = FieldsList.Split(",").Count

If fields_Count = Values.Count Then

    Dim insert_Param As New List(Of FieldsValuesParamList)

    insert_Param = get_FieldsValuesParamList(FieldsList, Values)

    Dim fields_String As String = String.Join(",", insert_Param.Select(Function(t) t._Field_Name).ToArray())
    Dim param_String As String = String.Join(",", insert_Param.Select(Function(t) t._Param_Name).ToArray())

    Dim sql_Insert As String

    sql_Insert = String.Concat("INSERT INTO ", TableName, " (", fields_String, ") VALUES(", param_String, ")")

    sql_Command.CommandText = sql_Insert

    For Each paramValue In insert_Param

        sql_Command.Parameters.Add(paramValue._Param_Name, SqlDbType.VarChar).Value = paramValue._Value_Name

        'sql_Command.Parameters.AddWithValue(paramValue._Param_Name, paramValue._Value_Name)
    Next

    sql_Command.ExecuteNonQuery()
    sql_Command.Parameters.Clear()

Else
    Throw New Exception("Fields and Values count should be equal.")
End If
Visual Vincent
  • 18,045
  • 5
  • 28
  • 75
Shaunak Srivastava
  • 43
  • 7
  • 2
    You need provide `Length` for string types. `Length` need to be same for reusing same query plan. So if you use length of column which your parameter represent, then it will use same query plan. – Fabio Oct 05 '16 at 11:23
  • Okay so either i have to pickup length value from table schema or put a a length value that will never reached. – Shaunak Srivastava Oct 05 '16 at 11:29
  • I have entered vb code also, i need a semantic way to do it if possible. – Shaunak Srivastava Oct 05 '16 at 11:36
  • 2
    Another common approach would be to move the repeatable logic into an Sql Stored Procedure and have the procedure reuse it's execution plan (if you have access to implement such a change). – Edward Comeau Oct 05 '16 at 11:38
  • I don't want to go for stored procedures, How it would be if i extract the fields type and their length from schema and use them. – Shaunak Srivastava Oct 05 '16 at 12:24
  • That approach seems like it would meet your needs, as long as you're giving the length property. Your code looks very dynamic so querying the sys db schema will fit in. sys.columns will give you max_length but be aware this is a byte count not character, some collations of varchar and I think all nvarchar will use two bytes per character – Edward Comeau Oct 05 '16 at 13:05
  • I am afraid you trying to invent your own ORM framework. For query using already compiled query plan you need provide correct type and length for strings and Precision and Scale for decimals for examples. One way - stored procedures for every table. Second way hardcode or retrieve from schema once all information about needed columns and generate Sql parameters based on this information – Fabio Oct 05 '16 at 13:31
  • Would this schema approach affect application performance when multi users working on sametime. – Shaunak Srivastava Oct 05 '16 at 13:34
  • A final suggestion requested from all of you, **"Would schema approach affect application performance while multi users are working on it".** – Shaunak Srivastava Oct 05 '16 at 14:25

0 Answers0