Add a fast and convenient deserialization API to ADO.NET

[GSPP]

When using ADO.NET to read rows the deserialization cost can be very significant. This is important in scenarios where a lot of data is streamed. Here is directly executable code to demonstrate this:

    static class Program
    {
        static void Main()
        {
            DeserializationBenchmark.Run();
        }

        public static class DeserializationBenchmark
        {
            public static void Run()
            {
                using (var connection = new SqlConnection("Data Source=(local); Integrated Security=True; Initial Catalog=master;"))
                {
                    connection.Open();

                    PrepareTestData(connection);

                    RunBenchmarkLoop();
                }
            }

            static void PrepareTestData(SqlConnection connection)
            {
                using (var command = new SqlCommand(@"SELECT o1.object_id value1, o1.object_id value2, o1.object_id value3, o1.object_id value4, o1.object_id value5 INTO ##BenchmarkRows FROM sys.all_objects o1 cross join (select top 100 * from sys.all_objects o2) o2", connection))
                {
                    command.ExecuteNonQuery();
                }
            }

            static void RunBenchmarkLoop()
            {
                long count = 0;

                while (true)
                {
                    ReadData();

                    count++;

                    if (count % 10 == 0)
                        Console.WriteLine($"Completed {count} iterations.");
                }
            }

            static void ReadData()
            {
                using (var connection = new SqlConnection("Data Source=(local); Integrated Security=True; Initial Catalog=master;"))
                {
                    connection.Open();

                    using (var transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
                    {
                        using (var command = new SqlCommand(@"SELECT * FROM ##BenchmarkRows", connection, transaction))
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var value1 = reader.GetInt32(0);
                                var value2 = reader.GetInt32(1);
                                var value3 = reader.GetInt32(2);
                                var value4 = reader.GetInt32(3);
                                var value5 = reader.GetInt32(4);
                            }
                        }

                        transaction.Commit();
                    }
                }
            }
        }
    }

This code simply deserializes a table of a few thousand rows. There are 5 int columns. The benchmark is designed to demonstrate the issue but it's not unrealistic. I also used ADO.NET best practices.

I might be mistaken but it seems that a lot of the time is spent calling chatty APIs on SqlDataReader. It looks like there is a lot of API overhead and comparatively little work actually parsing the network protocol. The console process uses 100% of one CPU core. SQL Server uses only about 1/3 of one CPU core. This is further (heuristic) evidence that the client is less efficient than the server.

There is no way to avoid this cost at the moment. The code is as fast as it gets. I suggest the following enhancement: Provide an API that directly deserializes into a user-provided type.

Old:

IDataReader reader = command.ExecuteReader();
while (reader.MoveNext()) {
 //Read all fields individually
}

New:

IEnumerable<MyDTO> resultRows = command.ExecuteObjects<MyDTO>();

This API could be made extremely fast because:

• It is less chatty. ADO.NET can access it's internal buffers more directly.
• Metadata overhead has to be incurred only once for the whole result set. Many per-row and per-field metadata accesses and validations can be eliminated.
• ADO.NET can compile a lambda expression dynamically that pulls data from the internal byte buffers and pushes it into the user-provided type. To illustrate the point: The expression could look like this: new MyDTO() { Field1 = internalReader.GetNextFieldAsInt32(), Field2 = internalReader.GetNextFieldAsString(), ... }.
• The lambda can be cached internally. Alternatively an API consumer would use a factory to create a "fast reader" and cache the reader.
• The entire row can be read in one go. I believe this makes it faster to parse the TDS protocol.
• If async is in use there will be less async overhead because the APIs are less chatty.

---

Possible extensions of this idea would be:

• Allow structs as well as classes
• Allow invoking a constructor
• Allow sequences of the form IEnumerable<object[]> where all field values are provided as objects.
• Add a pull-style API. Very rough API idea: readerObject.GetNextObject<MyDTO>().
• Add a way to provide and reuse preallocated objects e.g. readerObject.GetNextObject<MyDTO>(existingObject).
• Async support (IAsyncEnumerable or other suitable mechanisms).
• Maybe there can be a parallel mode that hands our a ParallelQuery. I'm not optimistic this can be done.
• Allow sequences of the form IEnumerable<MyDTO[]>. Here, ADO.NET would hand out rows in chunks. The chunk size would be chosen optimally. Probably, ADO.NET would convert each network buffer into a chunk. This would make the API even less chatty.

I understand that this proposal is a bit raw. The API that I proposed is a straw man that clearly has some flaws. I think an API in this spirit could improve efficiency a lot. It also would be very convenient. The existing pattern is extremely awkward to use. People are using light-weight wrappers like Dapper which look just like this API proposal.

---

The API should be so that ORMs such as Entity Framework can use it as well. This might not be trivial depending on what Entity Framework does. Here's an idea on how to support arbitrary object instantiation:

Instead of making ADO.NET figure out how to construct the new MyDTO()... part of the expression tree, let the user provide that. ADO.NET provides one Expression for each field to be deserialized. It could look like this:

var fastReader = FastReader.Create<MyDTO>(
    columnDescriptions /* contains the type and order of columns */,
    (Expression[] columnValueExpressions) =>
        Expression.New(typeof(MyDTO), columnValueExpressions) //Create new MyDTO(...) expression
});

//cache fastReader

IEnumerable<MyDTO> objects = fastReader.Read(mySqlDataReader);

In this example, the API caller can create an arbitrary deserialization expression that uses the ADO.NET provided column values in columnValueExpressions. columnValueExpressions[0] would correspond to internalReader.GetNextFieldAsInt32() in the code further above. Entity Framework could construct an expression tree that does anything it wants including creating multiple objects, calling factory methods and calling into the object state manager. All of this processing would be very close to obtaining the actual column values from internal ADO.NET data structures. No chattyness.

[divega]

System.Data Triage: This proposal has a lot in common with ideas we have been discussing as part of the data access performance effort, in particular the "shaper" API that materializes objects straight out of buffers that @anpete has prototyped.

We can use this issue to track the general idea, so moving to the Future milestone for now.

cc also @roji @ajcvickers in case they want to comment on the proposal.

[divega]

Here are a couple of initial comments:

In https://github.com/aspnet/dataaccessperformance/, we happened to do some analysis of the inherent overhead of the ADO.NET API itself: we built a simple but very fast provider for PostgreSQL that exposes a somewhat similar API to what is described in this proposal, then we created a façade ADO.NET API over it. When we ran some benchmarks using both APIs, and the difference turned out to be negligible.

In some respects, IEnumerable may have more allocation overhead than the current data reader API: after all, each new row is a new instance, while you re-use the same instance of DbDataRader in ADO.NET. However, we agree that this overhead may be offset by new opportunities to do things more efficiently, especially in the cases in which the desired way to consume the results is an object instance per row anyway.

[roji]

Just to state the obvious, it's important to test with another provider before determining that the chattiness of the DbDataReader API is a major cause of slowdown. I don't know how things work with SQL Server/TDS, but at least in PostgreSQL the protocol overhead really shouldn't be that big a factor. It's definitely worth profiling to see with other database (SQLite, PostgreSQL, MySQL) before going for something like this for performance reasons.

However, That doesn't mean that a new API to deserialize a row into an object isn't interesting, even from an API convenience standpoint (making ADO.NET more modern). I agree with @divega regarding allocations, but unless I'm mistaken it may be possible to do this without any allocations - the IEnumerable returned could be recycled across queries, keeping it on the DbCommand instance, or even on the DbConnection instance (if only one query is supported at a given time). If the implementation of this API really causes an allocation per row I think it's a pretty bad idea.

One nice aspect here is that a default implementation could be added to DbCommand (or even DbConnection?), which would simply call DbDataReader.GetFieldValue<T>(), but a provider could override this to prevent an efficient implementation if they wanted.

Finally, this is probably related to #25298.

[divega]

If the implementation of this API really causes an allocation per row I think it's a pretty bad idea.

@roji FWIW, what I mean is that when you consume an IEnumerable<T>, the object returned by Enumerator<T>.Current is normally a new instance for every row, while when you consume a DbDataReader, you keep using the same object instance to read the field values from the next row. My second point is that if you want to expose query results as an IEnumerable<T> anyway (as most O/RMs and micro-O/RMs do), then you may as well want to do it as efficiently as possible, which is where an API like this could provide some value.

[roji]

@divega of course, you're right - in my response above I concentrated on the IEnumerable instance itself, but of course that's much less important.

There's the theoretical possibility of returning the same T from Enumerator<T>.Current, and caching T. However, that's going to break the usual enumerable behavior (stuff like ToList(), ToArray() will totally fail). I do wonder if there's any precedence of this in ASP.NET, corefx to avoid allocations, it may be worth checking.

In any case, what's clear in mind is that adding an API that's supposed to help efficiency (e.g. via code-generating row parsing) but which allocates on every row seems like a very self-defeating thing. As usual it won't necessary show up on a microbenchmark, but the overall impact on an application is likely to be negative.

Another comment is that stuff like code-generating row parsing is expensive, and it isn't clear at what point this would happen nor how it could be cached. If you code-generate a row-parser every time a query is executed, then again it's likely the the overall effect would be negative, because of the generation overhead when executing small, easy-to-parse queries. However, this could be a good fit for preparation, which is meant precisely for doing one-time heavy operations in preparation for many executions.

[divega]

@roji yes, I was thinking the same thing re preparation.

[GSPP]

@divega

It's great to hear that a performance effort is underway. Many times I felt the SQL Server ADO.NET client was too slow for what it did.

I do not believe that allocations would be a significant concern. I mean look at all the work that is being performed in this profile. Allocating and deleting even a million short lived objects each second would not create much GC impact compared to all this work. while(true) new object(); runs at about 100m objects per second if I remember correctly. No data access API can ever provide anywhere near 100m objects per second. This is a heuristic argument to the idea that allocations would always be de minimis compared to the other work going on.

I generally feel that avoiding allocations is a bit over-prioritized these days. Sometimes it's valuable but often the real work overshadows any allocations issues. On the other hand if this API really gets as fast as I think it might then allocations might turn up in the profile.

I proposed a clean way to reuse objects: Let the caller hand in objects so that he is responsible for object reuse. He can the reuse them across calls even.

@roji great idea to see how other providers do. On the other hand I would argue that SQL Server is the "elephant in the room" use case. It's most important. So maybe it's just an internal SqlDataReader fix but given how the profile looks I kind of doubt it. Also, I do not believe the TDS protocol is at fault at all. It's the code that turns TDS into user-accessible data.

Code should never be generated per query as that is too expensive. As a first layer it would be an API where the user explicitly creates a FastReader. Creating this is a slow process and the user is expected to create one and cache it. Entity Framework has compiled queries already. So it must have some internal per-query cache object. It can easy perform the FastReader caching itself.

ADO.NET could have some default caching built-in. I believe the TDS protocol starts by sending the structure of rows and then the actual data for those rows. Each query result set returns schema info. So the cache could be keyed on the type of the user provided DTO and the schema info sent by the server. Then, the generated code can be perfectly specialized not only to the DTO type but also the binary row layout! This would become insanely fast. Then, the generated code would become what I showed in the proposal:

(InternalReader internalReader) =>
     new MyDTO() {
       Field1 = internalReader.GetNextFieldAsInt32(),
       Field2 = internalReader.GetNextFieldAsString(),
       ...
      }

Currently, the SqlDataReader.GetInt32(index) API must take care to read fields out of order and validate all kinds of things. Also, I think it reads rows incrementally from the network and it supports streaming large byte/char streams. That can all go away. Parsing would be almost as efficient as manually written code using something like BinaryReader. This would be the end state of efficiency. Cannot be improved further.

[roji]

I do not believe that allocations would be a significant concern.

I think that depends on which allocations are being discussed... If you're referring to an allocation that happens once per command execution, then you're likely right (since a command execution basically means network traffic). However, an allocation that happens on each row, or worse, on each column, could have a significant impact on performance. In my benchmarks of Npgsql I definitely saw some significant improvements when eliminating row- and column-based allocations.

Basically I think that saying "allocations" doesn't mean much without specifying exactly when these allocations take place.

@roji great idea to see how other providers do. On the other hand I would argue that SQL Server is the "elephant in the room" use case. It's most important.

I'm not sure exactly how you mean that - why is SQL Server the most important? Lots of people use .NET Core with Sqlite, PostgreSQL or MySQL.

So maybe it's just an internal SqlDataReader fix but given how the profile looks I kind of doubt it. Also, I do not believe the TDS protocol is at fault at all. It's the code that turns TDS into user-accessible data.

So again, I'm not sure what the evidence is that parsing TDS is a significant performance bottleneck in SQL Client, and much less in ADO.NET drivers in general (after all we're discussing a general improvement to ADO.NET that doesn't affect only SQL Server). Unless I'm mistaken the Techempower benchmarks currently perform significantly worse for SQL Server compared to PostgreSQL/Npgsql. Of course that doesn't tell you if the problem is in the ADO.NET client, the server or both (the benchmark measures the entire stack), but this should be explored and proved before making any assumptions.

Code should never be generated per query as that is too expensive. As a first layer it would be an API where the user explicitly creates a FastReader. Creating this is a slow process and the user is expected to create one and cache it. Entity Framework has compiled queries already. So it must have some internal per-query cache object. It can easy perform the FastReader caching itself.

I think what you're referring to would be better done as part of command preparation, which is already a part of the standard ADO.NET API. I don't really see any need to introduce a new API such as FastReader - DbCommand.Prepare() could be the user-induced trigger to do the code generation.

ADO.NET could have some default caching built-in. I believe the TDS protocol starts by sending the structure of rows and then the actual data for those rows. Each query result set returns schema info. So the cache could be keyed on the type of the user provided DTO and the schema info sent by the server.

Again, that sounds like a good fit for a more generalized auto-preparation mechanism (see #25185). Compiling a row reader is just one part of optimizing repeated execution of the same command, and the concept of preparation already exists as a standard database API. Npgsql specifically already has a facility whereby when an SQL is executed more than X times, it gets automatically prepared, with the prepared sources being keyed on the SQL. This currently involves server statement preparation, but it could just as well include client-side code generation.

But I think there are more important question to be answered.

First, are we sure that there actually is a problem here? Your original profiling session shows that 77% of the time is spent in SqlDataReader.GetInt32(). However, your benchmark doesn't actually do anything with the values it reads, so it makes sense for the bulk of the time to be spent in SqlDataReader. It doesn't mean that anything is actually wrong or slow...

Second, even if we agree that SqlDataReader.ReadInt32() is somehow too slow, you still need to show that this is a systematic problem across ADO.NET providers in order to justify a new, cross-provider ADO.NET feature for row parsing. Otherwise you may be projecting from an SQL Client inefficiency to the entire database API.

Finally, even if we accept that a new code-generating API is needed, it still isn't clear what would the API shape should be. The original API proposed here (IEnumerable<MyDTO> resultRows = command.ExecuteObjects<MyDTO>()) suffers from the allocation-per-row issue, unless you break IEnumerable semantics and return the same MyDTO instance for every row (in which case ToList() and related stop working). Maybe you have another API in mind...

Then, the generated code can be perfectly specialized not only to the DTO type but also the binary row layout!

The binary layout doesn't depend just on the resultset schema/metadata, since fields have different lengths in different rows. In other word, the same string column could have different lengths in different rows, changing the position of all fields coming afterwards. That's one reason why I think the value of code generation isn't huge here.

Currently, the SqlDataReader.GetInt32(index) API must take care to read fields out of order and validate all kinds of things. Also, I think it reads rows incrementally from the network and it supports streaming large byte/char streams. That can all go away. Parsing would be almost as efficient as manually written code using something like BinaryReader. This would be the end state of efficiency. Cannot be improved further.

Reading fields out of order really isn't much. Also, unless CommandBehavior.Sequential is specified the row should in general be buffered in memory, making all column read operations very light memory operations - there's no reason for there to be any sort of overhead for reading rows incrementally from the network, supporting large byte/char stream, etc.. Maybe this isn't the way SQL Client operates (I have no idea), but it definitely could be.

I'd really like to see some hard figures with Npgsql before concluding that ADO.NET needs to be changed somehow.

[jnm2]

I'd absolutely love the ability to obtain an IAsyncEnumerable<T> from a query. Most of the time I do this, T will either be a primitive type or an immutable struct (or less frequently, immutable class). So I'd expect the API to pass the values to the constructor in those cases rather than setting properties or fields.

To me performance is secondary. The real win is that the data conveniently comes back already in the form that I want to pass around.

[roji]

Regarding IAsyncEnumerable<T>, see #25298

[jnm2]

Isn't #25298 about IAsyncEnumerable<DbDataReader>, or does it cover IAsyncEnumerable<ImmutableDTO> too?

[roji]

Isn't #25298 about IAsyncEnumerable, or does it cover IAsyncEnumerable too?

#25298 is about making "DbDataReader implement IAsyncEnumerable", I don't really know what IAsyncEnumerable<DbDataReader> would mean (an enumeration of resultsets?).

The question is still whether that IAsyncEnumerable would be of some sort of general record API (like a modern DbDataRecord) or of a user-provided DTO type. In any case, #25298 suffers from the same design issue as this proposal - either it constantly returns the same instance (in which case it breaks enumerable semantics and is only useful with foreach) or it allocates on each row, which isn't good for perf.

[jnm2]

I don't really know what IAsyncEnumerable<DbDataReader> would mean (an enumeration of resultsets?).

Right now it's IEnumerable and returns itself (or a proxy object implementing IDataRecord?) as enumerator.Current, right? I would have said IAsyncEnumerable<IDataRecord> but I'd want to use the async getters which are only available on DbDataReader. That's where that came from.

Anyway, my interest is really in materialized immutable read model objects. (Usually value types.) Even though IAsyncEnumerable<DbDataRecord> would be cool if DbDataRecord had async getters, it would only be a means to that end. Which thread is closest to tracking that particular end; #25298?

[roji]

I don't really know what IAsyncEnumerable would mean (an enumeration of resultsets?).

Right now it's IEnumerable and returns itself (or a proxy object implementing IDataRecord?) as enumerator.Current, right? I would have said IAsyncEnumerable but I'd want to use the async getters which are only available on DbDataReader. That's where that came from.

Ah OK, I understand better. Yeah, the current IEnumerable implementation returns IDataRecord, which exposes pretty much the same API as DbDataReader.

Regarding implementing IAsyncEnumerable<T> vs. IAsyncEnumerable<IDataRecord>, the two could be useful. Once again, rather than (or in addition to) IAsyncEnumerable<T>, DbDataReader could simply have something like ReadRows<T>(T[] rows) which populates row objects given by the user - this would eliminate any allocations.

I guess the bigger question is whether ADO.NET should start mapping rows to user DTOs, which is a step in the direction of an O/RM (or at least something like Dapper).

[jnm2]

ReadRows<T>(T[] rows) works if the DTOs are mutable. You can still be allocation-free with IAsyncEnumerable<ImmutableStructDTO>, IAsyncEnumerable<int>, etc.

I guess the bigger question is whether ADO.NET should start mapping rows to user DTOs, which is a step in the direction of an O/RM (or at least something like Dapper).

How ORMy? I like the idea of ADO.NET staying close to the metal with this. Having relationships between objects seems like something you'd use EF for. I'm envisioning ADO.NET mapping a row to a constructor call, forcing each column name to match each constructor parameter. If you want nested structured types, you'd do the translation yourself from the primitive values you receive in the DTO's constructor.