{"action":"created","issue":{"url":"https://api.github.com/repos/dotnet/corefx/issues/28769","repository_url":"https://api.github.com/repos/dotnet/corefx","labels_url":"https://api.github.com/repos/dotnet/corefx/issues/28769/labels{/name}","comments_url":"https://api.github.com/repos/dotnet/corefx/issues/28769/comments","events_url":"https://api.github.com/repos/dotnet/corefx/issues/28769/events","html_url":"https://github.com/dotnet/corefx/issues/28769","id":310852515,"number":28769,"title":"Add a fast and convenient deserialization API to ADO.NET","user":{"login":"GSPP","id":12032350,"avatar_url":"https://avatars0.githubusercontent.com/u/12032350?v=4","gravatar_id":"","url":"https://api.github.com/users/GSPP","html_url":"https://github.com/GSPP","followers_url":"https://api.github.com/users/GSPP/followers","following_url":"https://api.github.com/users/GSPP/following{/other_user}","gists_url":"https://api.github.com/users/GSPP/gists{/gist_id}","starred_url":"https://api.github.com/users/GSPP/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/GSPP/subscriptions","organizations_url":"https://api.github.com/users/GSPP/orgs","repos_url":"https://api.github.com/users/GSPP/repos","events_url":"https://api.github.com/users/GSPP/events{/privacy}","received_events_url":"https://api.github.com/users/GSPP/received_events","type":"User","site_admin":false},"labels":[{"id":246305482,"url":"https://api.github.com/repos/dotnet/corefx/labels/area-System.Data","name":"area-System.Data","color":"d4c5f9","default":false},{"id":145399957,"url":"https://api.github.com/repos/dotnet/corefx/labels/enhancement","name":"enhancement","color":"207de5","default":true},{"id":153500238,"url":"https://api.github.com/repos/dotnet/corefx/labels/tenet-performance","name":"tenet-performance","color":"c2e0c6","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/dotnet/corefx/milestones/5","html_url":"https://github.com/dotnet/corefx/milestone/5","labels_url":"https://api.github.com/repos/dotnet/corefx/milestones/5/labels","id":1328080,"number":5,"title":"Future","description":"Milestone tracking issues not triaged to a planned release. Work in this milestone may happen, or may not: it is not committed.","creator":{"login":"joshfree","id":1302850,"avatar_url":"https://avatars1.githubusercontent.com/u/1302850?v=4","gravatar_id":"","url":"https://api.github.com/users/joshfree","html_url":"https://github.com/joshfree","followers_url":"https://api.github.com/users/joshfree/followers","following_url":"https://api.github.com/users/joshfree/following{/other_user}","gists_url":"https://api.github.com/users/joshfree/gists{/gist_id}","starred_url":"https://api.github.com/users/joshfree/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/joshfree/subscriptions","organizations_url":"https://api.github.com/users/joshfree/orgs","repos_url":"https://api.github.com/users/joshfree/repos","events_url":"https://api.github.com/users/joshfree/events{/privacy}","received_events_url":"https://api.github.com/users/joshfree/received_events","type":"User","site_admin":false},"open_issues":2048,"closed_issues":111,"state":"open","created_at":"2015-09-29T00:25:45Z","updated_at":"2018-04-21T21:05:37Z","due_on":null,"closed_at":null},"comments":27,"created_at":"2018-04-03T14:08:14Z","updated_at":"2018-04-22T21:26:55Z","closed_at":null,"author_association":"NONE","body":"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:\r\n\r\n```\r\n    static class Program\r\n    {\r\n        static void Main()\r\n        {\r\n            DeserializationBenchmark.Run();\r\n        }\r\n\r\n        public static class DeserializationBenchmark\r\n        {\r\n            public static void Run()\r\n            {\r\n                using (var connection = new SqlConnection(\"Data Source=(local); Integrated Security=True; Initial Catalog=master;\"))\r\n                {\r\n                    connection.Open();\r\n\r\n                    PrepareTestData(connection);\r\n\r\n                    RunBenchmarkLoop();\r\n                }\r\n            }\r\n\r\n            static void PrepareTestData(SqlConnection connection)\r\n            {\r\n                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))\r\n                {\r\n                    command.ExecuteNonQuery();\r\n                }\r\n            }\r\n\r\n            static void RunBenchmarkLoop()\r\n            {\r\n                long count = 0;\r\n\r\n                while (true)\r\n                {\r\n                    ReadData();\r\n\r\n                    count++;\r\n\r\n                    if (count % 10 == 0)\r\n                        Console.WriteLine($\"Completed {count} iterations.\");\r\n                }\r\n            }\r\n\r\n            static void ReadData()\r\n            {\r\n                using (var connection = new SqlConnection(\"Data Source=(local); Integrated Security=True; Initial Catalog=master;\"))\r\n                {\r\n                    connection.Open();\r\n\r\n                    using (var transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))\r\n                    {\r\n                        using (var command = new SqlCommand(@\"SELECT * FROM ##BenchmarkRows\", connection, transaction))\r\n                        using (var reader = command.ExecuteReader())\r\n                        {\r\n                            while (reader.Read())\r\n                            {\r\n                                var value1 = reader.GetInt32(0);\r\n                                var value2 = reader.GetInt32(1);\r\n                                var value3 = reader.GetInt32(2);\r\n                                var value4 = reader.GetInt32(3);\r\n                                var value5 = reader.GetInt32(4);\r\n                            }\r\n                        }\r\n\r\n                        transaction.Commit();\r\n                    }\r\n                }\r\n            }\r\n        }\r\n    }\r\n```\r\n\r\nThis 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.\r\n\r\n![image](https://user-images.githubusercontent.com/12032350/38203900-13b55bf6-36a1-11e8-9cdc-344a27d2c90c.png)\r\n\r\nI 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.\r\n\r\nThere 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.\r\n\r\nOld:\r\n```\r\nIDataReader reader = command.ExecuteReader();\r\nwhile (reader.MoveNext()) {\r\n //Read all fields individually\r\n}\r\n```\r\n\r\nNew:\r\n```\r\nIEnumerable<MyDTO> resultRows = command.ExecuteObjects<MyDTO>();\r\n```\r\n\r\nThis API could be made extremely fast because:\r\n\r\n* It is less chatty. ADO.NET can access it's internal buffers more directly.\r\n* 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.\r\n* 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(), ... }`.\r\n* The lambda can be cached internally. Alternatively an API consumer would use a factory to create a \"fast reader\" and cache the reader.\r\n* The entire row can be read in one go. I believe this makes it faster to parse the TDS protocol.\r\n* If async is in use there will be less async overhead because the APIs are less chatty.\r\n\r\n<hr>\r\n\r\nPossible extensions of this idea would be:\r\n\r\n* Allow structs as well as classes\r\n* Allow invoking a constructor\r\n* Allow sequences of the form `IEnumerable<object[]>` where all field values are provided as objects. \r\n* Add a pull-style API. Very rough API idea: `readerObject.GetNextObject<MyDTO>()`.\r\n* Add a way to provide and reuse preallocated objects e.g. `readerObject.GetNextObject<MyDTO>(existingObject)`.\r\n* Async support (`IAsyncEnumerable` or other suitable mechanisms).\r\n* Maybe there can be a parallel mode that hands our a `ParallelQuery`. I'm *not* optimistic this can be done.\r\n* 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.\r\n\r\nI 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.\r\n\r\n<hr>\r\n\r\nThe 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:\r\n\r\nInstead 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:\r\n\r\n```\r\nvar fastReader = FastReader.Create<MyDTO>(\r\n    columnDescriptions /* contains the type and order of columns */,\r\n    (Expression[] columnValueExpressions) =>\r\n        Expression.New(typeof(MyDTO), columnValueExpressions) //Create new MyDTO(...) expression\r\n});\r\n\r\n//cache fastReader\r\n\r\nIEnumerable<MyDTO> objects = fastReader.Read(mySqlDataReader);\r\n```\r\n\r\nIn 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.\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/dotnet/corefx/issues/comments/383413714","html_url":"https://github.com/dotnet/corefx/issues/28769#issuecomment-383413714","issue_url":"https://api.github.com/repos/dotnet/corefx/issues/28769","id":383413714,"user":{"login":"jnm2","id":8040367,"avatar_url":"https://avatars2.githubusercontent.com/u/8040367?v=4","gravatar_id":"","url":"https://api.github.com/users/jnm2","html_url":"https://github.com/jnm2","followers_url":"https://api.github.com/users/jnm2/followers","following_url":"https://api.github.com/users/jnm2/following{/other_user}","gists_url":"https://api.github.com/users/jnm2/gists{/gist_id}","starred_url":"https://api.github.com/users/jnm2/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jnm2/subscriptions","organizations_url":"https://api.github.com/users/jnm2/orgs","repos_url":"https://api.github.com/users/jnm2/repos","events_url":"https://api.github.com/users/jnm2/events{/privacy}","received_events_url":"https://api.github.com/users/jnm2/received_events","type":"User","site_admin":false},"created_at":"2018-04-22T21:26:55Z","updated_at":"2018-04-22T21:26:55Z","author_association":"COLLABORATOR","body":"If you don't want IAsyncEnumerable, I'd also be interested in `reader.ReadRows<T>`. This can be generalized in the same way. Rather than filling an array element per row, ADO.NET would call an action per row and leave all the decisions to the user delegate (whether to allocate, how to instantiate, etc):\r\n\r\n```cs\r\nawait reader.ReadRowsAsync(\r\n    (int x, DateTime y, int z) => buffer.Add(new Foo(…)),\r\n    min: 1 // Keep waiting until at least 1 row is available and sent to the delegate\r\n    max: buffer.Capacity); // If extra rows are *synchronously* available\r\n```\r\n\r\nA user like me could easily wrap an `IAsyncEnumerable` API around this with `min: 1, max: 1`."}}
{"actor":{"display_login":"jnm2"}}