none
SqlParameter Assignment for Performance? RRS feed

  • Question

  • I have the following pattern in my software for inserting data using the DAAB...

     

    Code Snippet

    public static int InsertData(MyData data, string dbConnString)
    {
     string spName = "spDataInsert";

     SqlParameter[] parms = SqlManagerParameterCache.GetCachedParameterSet(dbConnString, spName);
     if ( parms == null )
     {
      parms = SqlManagerParameterCache.GetSpParameterSet(dbConnString, spName, false);
      SqlManagerParameterCache.CacheParameterSet(dbConnString, spName, parms);
     }

     foreach ( SqlParameter parm in parms )
     {
      switch ( parm.ParameterName.ToLower() )
      {
       case "@data1":
        parm.Value = data.Data1;
        break;
       case "@data2":
        parm.Value = data.Data2;
        break;
       // TODO: VERY LONG LIST OF PARAMETERS
       // etc...
      }
     }

     int retval = SqlManager.ExecuteNonQuery(dbConnString, CommandType.StoredProcedure, spName, parms);

     // @id should be the last parameter
     for ( int i = parms.Length - 1; i >= 0; i-- )
     {
      if ( parms[i].ParameterName.ToLower() == "@id" )
      {
       retval = Convert.ToInt32(parms[i].Value);
       break;
      }
     }

     return retval;
    }

     

     

    The point of interest is marked as the TODO: line. I usually have a very lengthy list of parameters to assign and I was wondering if this is the most performant solution.

     

    Does anyone have any alternate ideas?

    Tuesday, June 10, 2008 2:35 PM

All replies

  • Actually do you mean the runtime performance or the development performance?

    If the development performance, you could use reflection.

     

    foreach(bla param in blablabla)

    {

    parm.Value = data.GetType().GetProperty(parm.ParameterName.SubString(1)).GetValue(data, null);

    }

     

    Remember you should have the null check Smile

     

    Wednesday, June 11, 2008 4:38 AM
  • Thanks for the response.

     

    I'm referencing the runtime performance of assigning values to a large number of SqlParameters.

     

    1) I'm using an array of SqlParameters because that's what the DAAB library pushes to me from its cache? Is there a more performant construct for this?

     

    2) Relating to #1, I'm using a "foreach" statement to iterate the array. Would a "for" statement loop be more performant?

     

    3) Within the "foreach" loop, I use a "switch" control statement, with a .ToLower() call for string matching, to determine the correct parameter to assign the value to. Is there a more performant way to do this?

     

    Let me add that while I realize that some of my specific questions relate more to C# semantics, I really looking to see if there's a pattern and/or methodology for assigning large numbers of parameter values in a high-performance way.

     

     

    My general requirement is to build a database with several related tables from disk files containing a couple of billion (with a B) records.

    Wednesday, June 11, 2008 3:51 PM
  • 1) I don't think there're any other ways to get it.

    2) Absolutely

    3) Actually you'll call ToLower() once every parameters and it is necessary and the least time to call ToLower().

     

    As you're using only one stored procedure, you could make the parameter "ordered". That is to say, construct the parameter list in a order in the stored procedure. Then you could have a binary-search for the parameter list.

    Thursday, June 12, 2008 6:11 AM
  •  

    As far as I understand, you are going to fill a set of records into a table but the order of element in the records is different than that in the table.

     

    I assume the structure of the records and tables are fixed and can be described as follows:

    Record: A B C D E F G

    Table: d g a b e f c

     

    A piece of record contains N elements (say n = 7 here) and the table contains N columns corresponding to the record. For each element in the record, you are going to find the corresponding column in the table and put it in. Say put the element in position A in record to column a in table, B to b, etc...

     

    Since the structure is fixed, you can create a mapping to do it. For example, map [2, 3, 6, 0, 4, 5] means to put element 0 in the record to column 2 in table, element 1 to column 3, etc...

     

    For each table you are going to fill with records, you can create such mapping and then put the records according to the mapping. The pseudo code may look like:

    Code Snippet

    for(int i = 0; i < record.size; ++i){

    row[map[i]] = record[i];

    table.add(row);

    }

     

     

    where record[ i ] means the ith element in the record and row[j] means the jth column of the table.

     

    This process will be very efficient if the number of tables is much less than the number of records.

    Thursday, June 12, 2008 6:36 AM
  • I'd support Shuhai's solution.

    Thursday, June 12, 2008 7:06 AM
  •  Hooray Hu-MSFT wrote:

    I'd support Shuhai's solution.

     

    Thank you both for your help. So just to confirm, the order of the elements in the SqlParameter array cache pushed to me from the DAAB library can be depended on to match the ordering of the parameters in the stored procedure definition?

    Friday, June 13, 2008 4:53 PM
  • Absolutely yes. Smile

    Monday, June 16, 2008 12:22 PM