DataTable v DataReader
Imagine we want to read data from a SQL Server database putting it in a generic object that can be used in a JSON API response. An ADO.NET DataReader should be faster and use less memory than a DataAdapter / DataTable approach. This post details some tests and what the differences are …
Test database
I’m going to test this on the Customers table within the Northwind database. I’ve created 5 copies of the Customers table with increasing number of rows within it.
A script to create the 5 Customers tables can be found here
Test app
The test app can be found here. The test app reads the Customers tables that vary in size using both the DataTable and DataReader / DataAdapter approaches. The time to read the data and put it in a generic object is captured along with the memory taken by the DataTable object.
Results
As you can see the difference is minimal for <100,000 rows.
| Rows | DataTable (ms) | DataReader (ms) | Difference (ms) | DataTable size (Mb) |
|---|---|---|---|---|
| 91 | 0.5 | 0.2 | 0.3 | 0.04 |
| 910 | 5.4 | 5.3 | 0.2 | 0.42 |
| 9,100 | 92.7 | 68.8 | 23.9 | 4.18 |
| 91,000 | 931.7 | 698.4 | 233.3 | 42.10 |
| 910,000 | 12,928 | 10,060 | 2,868 | 423.66 |
If you to learn about using React with ASP.NET Core you might find my book useful:
