When data processing speed is key

The Microsoft Entity Framework (EF) has been around for a long time more than 15 years! It gets more feature rich and performant with each release cycle. It’s a great framework and for many it is all they know, other than maybe other competitive ORM packages like Dapper or NHibernate to name but two. The EF handles a whole stack of boilerplate code that oldie developers like me would spend days if not months writing to gain the benefits of having strongly typed domain models referenceable within our business logic.

What about solving data access challenges where performance and memory efficiency is the priority, and the solution must be implemented using the .NET Framework?

I wrote a simple test case where I retrieved 10,000 records from a Microsoft SQL Server table and enumerated each record extracting its primary key value into an Int32 variable. My performance test using the EF processed 10,000 records in 17.31 seconds equating to a record every 0.01731 seconds pretty good going!

Here I will introduce ADO.NET the data access services that EF is built upon. Running the same test case but using an ADO.NET SqlDataReader resulted in 10,000 records being processed in 0.76 seconds equating to a record every 0.000076 seconds, now that’s fast!

It’s true this test performed no meaningful manipulation of data, but it does demonstrate the stark contrast in performance between the two mechanisms. It should also be noted that the SqlDataReader does not cache all 10,000 records in client memory, hence it natively scales for larger datasets more efficiently than writing batch logic for your EF process.

Our business case for switching to ADO.NET was a data warehouse bulk import process which reduced the elapsed time 10-fold. It also ensures a much lower memory footprint on client infrastructure essential when working with Azure Function Apps or other memory constrained environments.

There are cons to using ADO.NET specifically you lose your strongly typed domain models and for some organizations this return to running raw SQL would be too painful to contemplate. There are also challenges with long running data processing tasks. These tasks can be affected by transient communication errors between the client and SQL Server causing huge frustration when a process fails 3,000,000 records in for a 3,000,010 row data set. There are solutions to make your data processing loop using ADO.NET more resilient and robust I might do another article on this.

ADO.NET does not solve all problems and I am not advocating a return to column ordinal hell for normal CRUD type API’s however, ADO.NET has its place and is a worthwhile tool to have in your locker or at least be aware of to help solve certain challenges.

To learn more about ADO.NET: -