Why is reading a Dapper IEnumerable(dynamic) an order of magnitude slower than reading an IEnumerable(IDataRecord)?

Question

In comparing Dapper with the Enterprise Library Data Access Access block for getting data via stored procedure. I see an overall performance benefit of about 40% when using Dapper, which is somewhat surprising.

However, when comparing iteration and getting data from an IEnumerable(IDataRecord) vs. IEnumerable(dynamic), IEnumerable(IDataRecord) is approximately an order of magnitude faster. Is this behavior well understood and to be expected or is there something not right here?

The results:

IEnumerable(IDataRecord) enter image description here

IEnumerable(dynamic) - using dapperObject.propertyName enter image description here

Now the interesting part, when using dapperObject["propertyName"], the performance is on par with IDataRecord. Not at all what I would have expected. enter image description here

The relevant portion of the profiling code

using System;
using System.Collections.Generic;
using System.Linq;
using Dapper.DataAccess;
using System.Data;
using tophat;

namespace Dapper.TestRunner
{
    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = "data source=WEBDBdev3,1866; User id=hsbmhw;Password=gEner4Y&M;Persist Security Info='true'; initial catalog=myhomeworks;";
            //The following uses Tophat to create a singleton connection instance.
            Database.Install<SqlServerConnectionFactory>(connectionString, ConnectionScope.ByRequest);
            DapperTest();
            DapperTest2();
            EnterpriseLibraryIDataRecordTest();

        }

        private static void DapperTest()
        {
            for (int i = 0; i < 100; i++)
            {
                IEnumerable<dynamic> users = MyRepository.GetUsersDapper();
                PopulateBusinessObjectsDynamic(users);
            }
        }

        private static void DapperTest2()
        {
            for (int i = 0; i < 100; i++)
            {
                IEnumerable<dynamic> users = MyRepository.GetUsersDapper();
                PopulateBusinessObjectsDynamic2(users);
            }
        }

        private static void EnterpriseLibraryIDataRecordTest()
        {
            for (int i = 0; i < 100; i++)
            {
                IEnumerable<IDataRecord> users = MyRepository.GetUsersEntlib();
                PopulateBusinessObjectsIDataRecord(users);
            }
        }

        private static void PopulateBusinessObjectsDynamic(IEnumerable<dynamic> users)
        {
            foreach (var user in users)
            {
                BusinessObject bo = new BusinessObject(user);
            }
        }

        private static void PopulateBusinessObjectsDynamic2(IEnumerable<dynamic> users)
        {
            foreach (var user in users)
            {
                BusinessObject bo = new BusinessObject(user);
            }
        }


        private static void PopulateBusinessObjectsIDataRecord(IEnumerable<IDataRecord> users)
        {
            foreach (var user in users)
            {
                BusinessObject bo = new BusinessObject(user);
            }
        }
    }



    public class BusinessObject
    {

        public DateTime CreateDate { get; set; }
        public String CreateDateString { get; set; }
        public String FirstName { get; set; }
        public bool IsApproved { get; set; }
        public bool IsLockedOut { get; set; }
        public DateTime LastActivityDate { get; set; }
        public DateTime LastLoginDate { get; set; }
        public String LastName {get;set;}
        public String Organization{get;set;}
        public int OrganizationId{get;set;}
        public int PersonId{get;set;}
        public String ProfileLastUpdatedBy{get;set;}
        public DateTime ProfileLastUpdatedDate{get;set;}
        public String RoleName{get;set;}
        public long RowNumber{get;set;}
        public int TotalCount{get;set;}
        public Guid UserId{get;set;}
        public string UserName {get;set;}
        public string UserStatus{get;set;}

        public BusinessObject(dynamic user)
        {
            CreateDate=user.CreateDate;
            CreateDateString = user.CreateDateString;
            FirstName = user.FirstName;
            IsApproved = user.IsApproved;
            IsLockedOut = user.IsLockedOut;
            LastActivityDate= user.LastActivityDate;
            LastLoginDate = user.LastLoginDate;
            LastName = user.LastName;
            Organization = user.organization;
            OrganizationId=user.organization_id;
            PersonId = user.party_id;
            ProfileLastUpdatedBy = user.ProfileLastUpdatedBy;
            ProfileLastUpdatedDate = user.ProfileLastUpdatedDate;
            RoleName = user.RoleName;
            RowNumber = user.RowNumber;
            TotalCount = user.TotalCount;
            UserId = user.UserId;
            UserName= user.UserName;
            UserStatus = user.UserStatus;
        }

        public BusinessObject(bool x, dynamic user)
        {
            CreateDate = user["CreateDate"];
            CreateDateString = user["CreateDateString"];
            FirstName = user["FirstName"];
            IsApproved = user["IsApproved"];
            IsLockedOut = user["IsLockedOut"];
            LastActivityDate = user["LastActivityDate"];
            LastLoginDate = user["LastLoginDate"];
            LastName = user["LastName"];
            Organization = user["organization"];
            OrganizationId = user["organization_id"];
            PersonId = user["party_id"];
            ProfileLastUpdatedBy = user["ProfileLastUpdatedBy"];
            ProfileLastUpdatedDate = user["ProfileLastUpdatedDate"];
            RoleName = user["RoleName"];
            RowNumber = user["RowNumber"];
            TotalCount = user["TotalCount"];
            UserId = user["UserId"];
            UserName = user["UserName"];
            UserStatus = user["UserStatus"];
        }

        public BusinessObject(IDataRecord user)
        {
            CreateDate = (DateTime)user["CreateDate"];
            CreateDateString = (string)user["CreateDateString"];
            FirstName = (string)user["FirstName"];
            IsApproved = (bool)user["IsApproved"];
            IsLockedOut = (bool)user["IsLockedOut"];
            LastActivityDate = (DateTime)user["LastActivityDate"];
            LastLoginDate = (DateTime)user["LastLoginDate"];
            LastName = (string)user["LastName"];
            Organization = (string)user["organization"];
            OrganizationId = (int)user["organization_id"];
            PersonId = (int)user["party_id"];
            ProfileLastUpdatedBy = (string)user["ProfileLastUpdatedBy"];
            ProfileLastUpdatedDate = (DateTime)user["ProfileLastUpdatedDate"];
            RoleName = (string)user["RoleName"];
            RowNumber = (long)user["RowNumber"];
            TotalCount = (int)user["TotalCount"];
            UserId = (Guid)user["UserId"];
            UserName = (string)user["UserName"];
            UserStatus = (string)user["UserStatus"];
        }
    }
}

can I ask: which version number of dapper is this test using? — Marc Gravell, May 22 '13 at 08:18
In particular, I'm a bit confused because for both the Nuget version and the "git" version, *there is no indexer*; `user["CreateDate"]` doesn't work for a `dynamic` dapper object. Are you using the `IDictionary` API? — Marc Gravell, May 22 '13 at 08:45
Where is GetUsersDapper method body? I'd think it's the most interesting part... — Zar Shardan, Jun 04 '13 at 14:46
It turns out that our IEnumerator inplementation that wraps the data reader for enterprise library executereader results was the cause of the performance difference. We were using this because our data library was written in VB and the yield statement was not available. Iterating over and IDataReader using method that yields is much quicker. In fact, there appears to be a very slight performance advantage for entlib when accessing stored procedures. — Richard Collette, Jun 07 '13 at 19:09

Marc Gravell · Accepted Answer · 2013-05-22T08:55:34.687

You actually seem to be running the same test twice; the input (users) is the same, from here:

    private static void DapperTest()
    {
        for (int i = 0; i < 100; i++)
        {
            IEnumerable<dynamic> users = MyRepository.GetUsersDapper();
            PopulateBusinessObjectsDynamic(users);
        }
    }

    private static void DapperTest2()
    {
        for (int i = 0; i < 100; i++)
        {
            IEnumerable<dynamic> users = MyRepository.GetUsersDapper();
            PopulateBusinessObjectsDynamic2(users);
        }
    }

And the actual "what do we do" is the same, here:

    private static void PopulateBusinessObjectsDynamic(IEnumerable<dynamic> users)
    {
        foreach (var user in users)
        {
            BusinessObject bo = new BusinessObject(user);
        }
    }

    private static void PopulateBusinessObjectsDynamic2(IEnumerable<dynamic> users)
    {
        foreach (var user in users)
        {
            BusinessObject bo = new BusinessObject(user);
        }
    }

So... I have to conclude "a combination of JIT, data caching (at the database server) assembly loading/validation/fusion, connection pooling, and the dynamic cache features made the second test appear faster".

Note that the dynamic side of dapper is intended for ad-hoc usage only anyway. If you wanted the optimal face of dapper, you would use Query<T>, not dynamic.

In particular, AFAIK no build of dapper supports a string indexer on the dynamic API. The object implements IDictionary<string,object> for member access, but you would need to explicitly cast to that to use it - you can't do user["PropName"] if user is typed as dynamic (if I'm wrong, please tell me!).

As it happens, the unreleased "git" code (for the dynamic API) is noticeably faster than the current "nuget" implementation - but that is a bit of a tangent to this specific question.

Marc, you are correct on all counts. I created a second constructor for the business object with a fake parameter just so I could get a different signature and either for got to update the constructor call in the second test or I used undo at some point an it rolled back. I was also looking at http://stackoverflow.com/questions/6168799/is-there-a-way-to-access-the-columns-in-a-dapper-fastexpando-via-string-or-index and missed the cast to IDictionary. Finally, the git code you mention may be directly related to my question though I'm not sure it will be related to property read performance — Richard Collette, May 22 '13 at 12:40
After implementing the IDictionary cast, and running tests switching the order of calls to DapperTest() and DapperTest2(), it appears that the very first read of A dapper dynamic object must have some one time overhead associated with it because which ever test is run second, runs slightly faster than the first one called. This overhead is largely irrelevant due to the overall 40% performance improvement over enterprise library. — Richard Collette, May 22 '13 at 13:00

Why is reading a Dapper IEnumerable(dynamic) an order of magnitude slower than reading an IEnumerable(IDataRecord)?

1 Answers1