DAL mapping efficiency

Question

I am wondering if I can make these classes a bit more efficient.

Test Results

Single Run

• Method 1: 5 Columns - Text Query - 81178 Records = 00:00:00.6390366 secs
• Method 2: 5 Columns - Text Query - 81178 Records = 00:00:00.5360307 secs

10 Run Loop

• Method 1: 5 Columns - Text Query - 81178 Records = 00:00:05.3253045 secs
• Method 2: 5 Columns - Text Query - 81178 Records = 00:00:05.0912912 secs

100 Run Loop

• Method 1: 5 Columns - Text Query - 81178 Records = 00:00:54.1270959 secs
• Method 2: 5 Columns - Text Query - 81178 Records = 00:00:53.8710813 secs

All 3 attempts for both methods never peak over 25% CPU usage.

As you can see there really is no significant improvement over either method, and method 2 (judging by CPU usage) does not seem to multi-thread.

I am thinking that if I can get rid of my usage of reflection to map the columns to strongly-typed classes that it would make a significant boost to both methods performance, and I am sure that I can make improvements to the asyncronicity of method 2 as well... I just don't know how.

WrapperTest.cs

 private static IList<T> Map<T>(DbDataReader dr) where T : new()
 {
 try
 {
 // initialize our returnable list
 List<T> list = new List<T>();
 // fire up the lamda mapping
 var converter = new Converter<T>();
 // read in each row, and properly map it to our T object
 var obj = converter.CreateItemFromRow(dr);
 // reutrn it
 return list;
 }
 catch (Exception ex)
 {
 // Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
 _Msg += "Wrapper.Map Exception: " + ex.Message;
 ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.Map", _Msg);
 // make sure this method returns a default List
 return default(List<T>);
 }
 }

This is a continuation of this question.

The code above definitely runs more efficiently now. Is there any way to make it better?

• Malachi: you should try to break this up into several questions.
• Malachi: you can also link to the other questions that you have posted since it is the same application. I think that is reasonable.
• Mathieu Guindon: Private fields naming convention is _field, not _Field... I guess VB is rubbing off on your C#, don't worry, soon your C# will rub off on your VB ;)
• tia: Any reasons you are not using existing ORM like LINQ-to-SQL or Entity Framework?
• Mathieu Guindon: I don't think EF is C#-specific (although I never tried with VB), @tia is right, an ORM would make your like much easier. Besides L2S isn't rwally an ORM so you can start with that :) ....as for the naming, no, it doesn't really matter. Just as long as you're consistent through.

Answer 1

Use LINQ expression compilation to generate mapping code at runtime. The concept is to generate a method that does obj.Property1 = dataReader["Property1"]; ... dynamically.

public class Converter<T> where T : new()
{
 private static ConcurrentDictionary<Type, object> _convertActionMap = new ConcurrentDictionary<Type, object>();
 private Action<IDataReader, T> _convertAction;
 private static Action<IDataReader, T> GetMapFunc()
 {
 var exps = new List<Expression>();
 var paramExp = Expression.Parameter(typeof(IDataReader), "dataReader");
 var targetExp = Expression.Parameter(typeof(T), "target");
 var getPropInfo = typeof(IDataRecord).GetProperty("Item", new[] { typeof(string) });
 foreach (var property in typeof(T).GetProperties())
 {
 var getPropExp = Expression.MakeIndex(paramExp, getPropInfo, new[] { Expression.Constant(property.Name, typeof(string)) });
 var castExp = Expression.TypeAs(getPropExp, property.PropertyType);
 //var bindExp = Expression.Bind(property, castExp);
 var bindExp = Expression.Assign(Expression.Property(targetExp, property), castExp);
 exps.Add(bindExp);
 }
 return Expression.Lambda<Action<IDataReader, T>>(Expression.Block(exps), new[] { paramExp, targetExp }).Compile();
 }
 public Converter()
 {
 _convertAction = (Action<IDataReader, T>)_convertActionMap.GetOrAdd(typeof(T), (t) => GetMapFunc());
 }
 public T CreateItemFromRow(IDataReader dataReader)
 {
 T result = new T();
 _convertAction(dataReader, result);
 return result;
 }
}

Test method with 80,000 x 100 iteration

 static void Main(string[] args)
{
 var dummyReader = new DummyDataReader();
 var properties = typeof(DummyObject).GetProperties();
 var startDate = DateTime.Now;
 var converter = new Converter<DummyObject>();
 for (int i = 0; i < 80000 * 100; i++)
 {
 //var obj = CreateItemFromRow2<DummyObject>(new DummyDataReader());
 var obj = CreateItemFromRow<DummyObject>(dummyReader, properties);
 //var obj = converter.CreateItemFromRow(dummyReader);
 dummyReader.DummyTail = i;
 }
 //var obj = CreateItemFromRow2<DummyObject>(new DummyDataReader());
 Console.WriteLine("Time used : " + (DateTime.Now - startDate).ToString());
 Console.ReadLine();
}

Result:

CreateItemFromRow : 18.5 seconds
Converter<T> : 7.3 seconds

Map function:

 private static IList<T> Map<T>(DbDataReader dr) where T : new()
 {
 // initialize our returnable list
 List<T> list = new List<T>();
 // fire up the lamda mapping
 var converter = new Converter<T>();
 while (dr.Read()) {
 // read in each row, and properly map it to our T object
 var obj = converter.CreateItemFromRow(dr);
 list.Add(obj);
 }
 // reutrn it
 return list;
 }

• tia: I think I know that Int32 error, but not sure of the rest. Could you post your class structure? and did you use my Convert<T> in your Map<T> method?
• Kevin: So, I've converted most of your code, and simply added it to my Wrapper class. (with some mods), it definately seems to be alot faster now. I am testing while using the same Sproc in my DB, but with differing parameters that return the same number of records. Do you see any way to make what I posted above more efficient? Or do you think that's about it?
• tia: You should still use Convert<T> class or adapt it. The point of Convert<T> class is to prepare stuff for type T, which is cache lookup. Thread-safe cache lookup is relatively costly, and that's why I separated it to Convert<T> so the cache lookup was done once for the whole list.
• Kevin: I tried using it like that but came up with errors, that I had posted here as comments, but were never answered for, so I tried it this way. I will attempt your class again
• Kevin: Once again, I get the error... "Invalid attempt to read when no data is present."