How buffer works?

[rocketRobin]

In read me, it says

Dapper's default behavior is to execute your SQL and buffer the entire reader on return. This is ideal in most cases as it minimizes shared locks in the db and cuts down on db network time.

And I saw the code

    public static IEnumerable<T> Query<T>(this IDbConnection cnn, CommandDefinition command)
    {
        var data = QueryImpl<T>(cnn, command, typeof(T));
        return command.Buffered ? data.ToList() : data;
    }

I dont't really understand why buffedred can minimizes shared locks in the db and cuts down on db network time.

[mgravell]

Right; first let's get clear on what we're talking about. In this context, buffered means that it is going to consume all the data from the reader before handing anything to the client, as a populated List<T>. The opposite to this - non-buffered - means that it is going to hand back an open IEnumerable<T> that is a wrapper over the underlying data-reader. and which is consuming data from the database as you iterate over the sequence, typically via foreach.

Now; the important difference here comes in the following:

foreach (var obj in query) {
    DoSomething(obj);
}

If DoSomething is embarrassingly fast, then: great, no problem. However, if DoSomething takes any non-trivial amount of time (by which I mean: more than a few nanoseconds), we can very quickly extend the time for which we have the reader held open. This might sound trivial to us, but to the database server, there's a huge difference between:

started reading data, consumed it as fast as I could give it, closed the reader

(which is "buffered"), and:

started reading the data, but took (from my perspective) a long time to consume each row, lazily crawling through the data, eventually deigning to close the reader

(which is "non-buffered" with a non-trivial delay)

The point is: how the actual database server implements reads (and especially large reads) is not simple; it might involve copying the data to an isolated location for the duration of the query (in which case we're taking server memory for the duration of our read, so the faster we read it: the better for server memory usage), but it might keep the actual query engine "alive" on the real data, requiring all the usual locks and other isolation / concurrency primitives. This means that if we take a long time to consume the data, we can be blocking other queries, or entering into complicated lock chains.

So, as a general rule: the faster you can consume data from a query, the better. Hence "buffered" is a good default.

Make sense?

[rocketRobin]

Yeah, thanks for answer ! It is really helpful! thanks a lot.