Dapper GetList vs Query Method with the different performance

Question

In order to support the popular database, such as sqlserver, oracle and mysql, we changed the raw sql conditoin Query() method to GetList() method which are from the DapperExtension, but there will be a performance issue when using GetList(), it seemed it will do a full table read firstly rather than a sql query.

the sql query method:

     TaskViewEntity taskView = null;
     string sql = @"SELECT
                     * 
                  FROM vwWfActivityInstanceTasks 
                  WHERE ActivityInstanceID=@activityInstanceID
                     AND ProcessInstanceID=@processInstanceID
                 ";

     var list = Repository.Query<TaskViewEntity>(sql,
     new
     {
         processInstanceID = processInstanceID,
         activityInstanceID = activityInstanceID

     }).ToList();

The linq method:

var sqlQuery = (from tv in Repository.GetAll<TaskViewEntity>(conn, trans)
                        where tv.ActivityInstanceID == activityInstanceID
                            && tv.ProcessInstanceID == processInstanceID
                        select tv
                        );
var list = sqlQuery.ToList<TaskViewEntity>();


    //The dapper extension GetList() method
    public IEnumerable<T> GetAll<T>(IDbConnection conn, IDbTransaction trans) where T : class
    {
        var dataList = conn.GetList<T>(null, null, trans);
        return dataList;
    }

score 1 · Answer 1 · answered Oct 19 '21 at 12:18

Let's look at your LINQ method, here:

var sqlQuery = (from tv in Repository.GetAll<TaskViewEntity>(conn, trans)
                where tv.ActivityInstanceID == activityInstanceID
                    && tv.ProcessInstanceID == processInstanceID
                select tv);

Now: the thing about LINQ is that it depends hugely on what it is working with. You appear to be expecting it to compose this into a SQL query with additional where clauses, to send down to the database. However, that isn't what we have here; GetAll returns an IEnumerable<T>, and LINQ on an IEnumerable<T> just processes the data that it finds. In the case of where, we could write that ourselves (for illustration) as simply:

public static IEnumerable<T> Where(this IEnumerable<T> source, Func<T, bool> predicate)
{
    foreach (var obj in source)
    {
        if (predicate(obj))
        {
            yield return obj;
        }
    }
}

The actual implementation is a little more nuanced, but: not much - and it is still doing the same thing fundamentally: taking inputs as they arrive in memory, testing them, and yielding them.

To do what you want would require something like IQueryable<T>, which represents a composable query. Writing an IQueryable<T> provider is really, really hard, and is very far outside of what Dapper tries to offer. I suspect that the GetList<T> method you're using (which is not part of Dapper itself) also doesn't provide this - it presumably just returns the data as a List<T>. So: no query composition is happening - you're just fetching everything over the network, then filtering it locally afterwards.

What I strongly suggest here is: if you want query composition that is going to build a SQL query for you (with where, order by etc), use an ORM - for example Entity Framework, LLBLGen, or any of the others. That doesn't mean you can't also use Dapper when it suits your needs. These are simply tools. You're allowed to choose multiple tools, and use each where it excels. You don't need to pick one tool at the start of a job and use it for everything. I readily use both Dapper and EF side-by-side in the same project, and I wrote Dapper.

Dapper GetList vs Query Method with the different performance

1 Answers1