Asyncronous While Loop?

Question

I have a pretty great SqlDataReader wrapper in which I can map the output into a strongly typed list.

What I am finding now is that on larger datasets with larger numbers of columns, performance could probably be a bit better if I can optimize my mapping.

In thinking about this there is one section in particular that I am concerned about as it seems to be the heaviest hitter

What I would really like to know, is if there is a way that I can make this loop asyncronous? I feel that will make all the difference in the world with this beast :)

Here is the entire Map method in case anyone can see where I can make further improvements on it...

IList<T> Map<T>

using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace o7th.Class.Library.Data
{
public class WrapperTest
{

    public static string Message { set { _Msg = value; } get { return _Msg; } }
    private static string _Msg;

    // Instantiate our caching methods
    internal static Common.CustomCache _Cache = new Common.CustomCache();

    private static IEnumerable<T> Map<T>(SqlDataReader dr) where T : new()
    {
        var enumerableDataReader = dr.Cast<DbDataRecord>().AsEnumerable();
        var tObj = new T();
        PropertyInfo[] propertyInfo = tObj.GetType().GetProperties();
        var batches = enumerableDataReader.Batch(10000);
        var resultCollection = new ConcurrentBag<List<T>>();
        Parallel.ForEach(batches, batch => resultCollection.Add(MapThis<T>(propertyInfo, batch)));
        return resultCollection.SelectMany(m => m.Select(x => x));
    }

    private static List<T> MapThis<T>(PropertyInfo[] propertyInfo, IEnumerable<DbDataRecord> batch) where T : new()
    {
        var list = new List<T>();
        batch.AsParallel().ForAll(record =>
        {
            T obj = new T();
            foreach (PropertyInfo prop in propertyInfo)
            {
                var dbVal = record[prop.Name];
                if (!Equals(dbVal, DBNull.Value))
                {
                    prop.SetValue(obj, dbVal, null);
                }
            }
            list.Add(obj);
        });
        return list;
    }


    public static IEnumerable<T> GetResults<T>(string _Qry, System.Data.CommandType _QryType,
                                        string[] _ParamNames = null,
                                        object[] _ParamVals = null,
                                        System.Data.SqlDbType[] _ParamDTs = null,
                                        bool _ShouldCache = false,
                                        string _CacheID = "") where T : new()
    {
        // Create a reference to a potential already cached IList
        IEnumerable<T> _CachedItem = _Cache.Get<IEnumerable<T>>(_CacheID);
        // If we're already cached, there's no need to fire up the data access objects, so return the cached item instead
        if (_CachedItem != null && _ShouldCache)
        {
            return _CachedItem;
        }
        else
        {
            // Fire up our data access object
            using (Access db = new Access())
            {
                try
                {
                    // create a new ilist reference of our strongly typed class
                    IEnumerable<T> _Query = null;
                    // set the query type
                    db.QueryType = _QryType;
                    // set the query text
                    db.Query = _Qry;
                    // make sure we've got some parameters, if we do the set them to our db access object
                    if (_ParamNames != null)
                    {
                        // set the parameter names
                        db.ParameterNames = _ParamNames;
                        // set the parameter values
                        db.ParameterValues = _ParamVals;
                        // set the parameter data types
                        db.ParameterDataTypes = _ParamDTs;
                    }
                    // start using our db access :)  Fire off the GetResults method and return back a SqlDataReader to work on
                    using (SqlDataReader r = db.GetResults())
                    {
                        // make sure the data reader actually exists and contains some results
                        if (r != null && r.HasRows)
                        {
                            // map the data reader to our strongly type(s)
                            _Query = Map<T>(r);
                        }
                    }
                    // check if we should cache the results
                    if (_ShouldCache)
                    {
                        // if so, set the query object to the cache
                        _Cache.Set<IEnumerable<T>>(_Query, _CacheID);
                    }
                    // return our strongly typed list
                    return _Query;
                }
                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.GetResults Exception: " + ex.Message + db.Message;
                    ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.GetResults", _Msg);
                    // make sure this method returns a default List
                    return default(IList<T>);
                }
            }
        }
    }
}

public static class Extensions
{
    /// <summary>
    /// Take a collection and split it into smaller collections
    /// </summary>
    /// <typeparam name="T">The Type</typeparam>
    /// <param name="collection">The collection to split</param>
    /// <param name="batchSize">The size of each batch</param>
    /// <returns></returns>
    public static IEnumerable<IEnumerable<T>> Batch<T>(this IEnumerable<T> collection, int batchSize)
    {
        var nextbatch = new List<T>(batchSize);
        if (collection == null)
        {
            yield break;
        }
        foreach (T item in collection)
        {
            nextbatch.Add(item);
            if (nextbatch.Count != batchSize)
            {
                continue;
            }
            yield return nextbatch;
            nextbatch = new List<T>(batchSize);
        }
        if (nextbatch.Count > 0)
        {
            yield return nextbatch;
        }
    }
}
}

db.GetResults() is a simple ExecuteReader via using SqlClient.SqlDataReader

p.s. This is my first c# project. I am a long time basic/qbasic/vb programmer =)

Here's my Test ConsoleApp:

Test

using o7th.Class.Library.Data;
using System;
using System.Collections.Generic;
using System.Threading;

namespace Testing
{
class Program
{
    static void Main(string[] args)
    {
        long startTime = DateTime.Now.Ticks;

        IList<Typing> _T = Wrapper.GetResults<Typing>("List.ZipSearch",
                                                    System.Data.CommandType.StoredProcedure,
                                                    new string[]{"@ZipCode", "@RadiusMile"},
                                                    new object[] { "01020", 10000 },
                                                    new System.Data.SqlDbType[] { System.Data.SqlDbType.VarChar, System.Data.SqlDbType.Float},
                                                    true, "TestCache1");
        long endTime = DateTime.Now.Ticks;
        TimeSpan timeTaken = new TimeSpan(endTime - startTime);
        Console.WriteLine("Task Took: " + timeTaken + " for: " + _T.Count + " records.");

        Thread.Sleep(2000);

        long startTime2 = DateTime.Now.Ticks;
        IEnumerable<Typing> _T2 = WrapperTest.GetResults<Typing>("List.ZipSearch",
                                                    System.Data.CommandType.StoredProcedure,
                                                    new string[] { "@ZipCode", "@RadiusMile" },
                                                    new object[] { "01020", 10000 },
                                                    new System.Data.SqlDbType[] { System.Data.SqlDbType.VarChar, System.Data.SqlDbType.Float },
                                                    true, "TestCache2");

        long endTime2 = DateTime.Now.Ticks;
        TimeSpan timeTaken2 = new TimeSpan(endTime2 - startTime2);
        Console.WriteLine("Task Took: " + timeTaken2 + " for: " + _T2 + " records.");
        Console.WriteLine("");
        Console.WriteLine("Press any key to continue...");

        Console.ReadKey();
    
    }

    partial class Typing {
        public long ZipID { get; set; }
        public string ZipCode { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string County { get; set; }
        public double Mileage { get; set; }
    }

}

}

Answer 1

A tiny change I'd make if I used that code would be to change your if to only set using PropertyInfo when required (newObject is already default(T)):

if ((info != null) && info.CanWrite && !(_Rdr.GetValue(i) is DBNull))
{
    info.SetValue(newObject, _Rdr.GetValue(i), null);
    break;
}

That will save you an extra call to default(T) and it will also save you overwriting newObject with it's own default value. It's a TINY optimization. Also, you seen to overwrite newObject multiple times, so that makes me thing that your if is only true once, so I've added a break to save you extra enumerations, assuming a large dataset, that could save you some time too.

How about this?

    var readerValue = _Rdr.GetValue(i);
    if ((info != null) && info.CanWrite && !(readerValue is DBNull))
    {
        info.SetValue(newObject, readerValue, null);
        break;
    }

*Edit to add more code.

Not sure whether this would improve things or not:

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;

namespace ConsoleApplication1
{
    internal class Program
    {
        private static readonly SqlToObjectReflectionMappingService MappingService = new SqlToObjectReflectionMappingService();

        private static void Main(string[] args)
        {
            // Call ConvertTable here...
        }

        private static IEnumerable<T> ConvertTable<T>(DataTable dataTable) where T : new()
        {
            return MappingService.DataTableToObjects<T>(dataTable);

        }

        public class SqlToObjectReflectionMappingService : ISqlToObjectMappingService
        {
            public T DataRowToObject<T>(DataRow row, PropertyDescriptorCollection propertyDescriptorCollection)
                where T : new()
            {
                var obj = new T();
                foreach (PropertyDescriptor propertyDescriptor in propertyDescriptorCollection)
                {
                    propertyDescriptor.SetValue(obj, row[propertyDescriptor.Name]);
                }
                return obj;
            }

            public IEnumerable<T> DataTableToObjects<T>(DataTable table) where T : new()
            {
                var obj = new T();
                var props = TypeDescriptor.GetProperties(obj);
                return table.AsEnumerable().AsParallel().Select(m => DataRowToObject<T>(m, props));
            }
        }

        public interface ISqlToObjectMappingService
        {
            T DataRowToObject<T>(DataRow row, PropertyDescriptorCollection propertyDescriptorCollection) where T : new();
            IEnumerable<T> DataTableToObjects<T>(DataTable table) where T : new();
        }
    }
}

*Edit to add even more code.

using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            // Call ConvertTable here
        }

        private static IEnumerable<T> Map<T>(SqlDataReader dr) where T : new()
        {
            var enumerableDataReader = dr.Cast<DbDataRecord>().AsEnumerable();
            var tObj = new T();
            PropertyInfo[] propertyInfo = tObj.GetType().GetProperties();
            var batches = enumerableDataReader.Batch(10000);

            var resultCollection = new ConcurrentBag<List<T>>();
            Parallel.ForEach(batches, batch => resultCollection.Add(MapThis<T>(propertyInfo, batch)));

            return resultCollection.SelectMany(m => m.Select(x => x));
        }

        private static List<T> MapThis<T>(PropertyInfo[] propertyInfo, IEnumerable<DbDataRecord> batch) where T : new()
        {
            var list = new List<T>();
            batch.AsParallel().ForAll(record =>
            {
                T obj = new T();
                foreach (PropertyInfo prop in propertyInfo)
                {
                    var dbVal = record[prop.Name];
                    if (!Equals(dbVal, DBNull.Value))
                    {
                        prop.SetValue(obj, dbVal, null);
                    }
                }
                list.Add(obj);
            });
            return list;
        }
    }

    public static class Extensions
    {
        /// <summary>
        /// Take a collection and split it into smaller collections
        /// </summary>
        /// <typeparam name="T">The Type</typeparam>
        /// <param name="collection">The collection to split</param>
        /// <param name="batchSize">The size of each batch</param>
        /// <returns></returns>
        public static IEnumerable<IEnumerable<T>> Batch<T>(this IEnumerable<T> collection, int batchSize)
        {
            var nextbatch = new List<T>(batchSize);
            if (collection == null)
            {
                yield break;
            }
            foreach (T item in collection)
            {
                nextbatch.Add(item);
                if (nextbatch.Count != batchSize)
                {
                    continue;
                }
                yield return nextbatch;
                nextbatch = new List<T>(batchSize);
            }
            if (nextbatch.Count > 0)
            {
                yield return nextbatch;
            }
        }
    }
}

• Another approach

Answer 2

Are you aware that each time you call String.ToUpper() you are creating a new string just to throw away? And for each record?

I presume you're using an HashTable, you might be better off with:

_ht = new Dictionary<string, PropertyInfo>(StringComparer.OrdinalIgnoreCase);

Then you could just use it like this:

PropertyInfo info = _ht[_Rdr.GetName(i)];

You might want to look at Parallel.For or Parallel.ForEach if you want to parallelize it.

But all that wouldn't avoid massive use of reflection.

But what I really think you should do is build a mapper (and, probably, cache it).

If you don't want to go the route of emiting IL, you might want to use expression trees:

• Hydrating Objects With Expression Trees - Part I
• Hydrating Objects With Expression Trees - Part II
• Hydrating Objects With Expression Trees - Part III