ADO.NET vs entity framework performance Point

Question

Hi,

I am looking for some feedback on performance between using straight SQL commands/connections (ADO.NET) vs utilizing the Entity Framework or Linq for the database work.

I wanted to get some feedback on a couple questions.

1. I have read that the EF is slower than using straight ADO.NET, but I wanted to get confirmation that that is still the case now with EF 6.0.

2. My second question has to do with structuring my code. Am I better off leaving a connection open the entire time the application is open, or am I better off opening a new connection for each write to the database. With the amount of writes that I am doing I assume I would waste of a lot of time opening and closing connections, but it would also be more stable as the application is multithreaded and could cause locking problems. I know connection poolings come into play there too.

3. Lastly, if ADO.NET is the better path, am I better off using Async calls instead of straight ExecuteNonQueries? The query returns are quick because the generally only update a single record, so I wasn’t sure if that would have an value.

Thanks in advance..

KP Singh Chundawat

Suraj Sahoo · Answer

Hello K P, This is very good query you have posted here. This has been a topic of discussion for long without any strong and final decision as there is nothing called the best approach in this scenario. We follow what has been followed the most. Now coming back to your first point. 1- We know that EF is called ADO.NET EF which means that EF sits on top of the ADO.NET , which tells us that it cant be faster than ADO.NET. But remember the power of LINQ which EF provides the developers. It is really powerful when comes with EF. Since EF encapsulates ADO.NET at the background it used ADO.NET only, but the question comes why EF then?? Yes if we use EF and LINQ then the maintainability and code redundancy reduces as we donot have to write the big queries anymore like SP and all. 2-See the connection and dispose of connection is a very vital thing to keep in mind. When we use EF then the context objects are used to connect to the database and for the operation in the LINQs. So when we create a new instance of the context using the "using" then it by default disposes the context when ever the use is over. Also now when we are using Dependency Injection tool like Ninject, they do the disposing internally using the IDisposable extension. So under the belt the connection gets disposed, but sometimes not immediately. 3- Async methods are good to use when you know the flow properly and sure of it. For example you need to send emails to say 50 persons, then if that is not run on the background then it would eat up a lot of time and memory. SMTP connection is not that fast. So better use async methods so that it runs on the background. But for single methods which will just return you an object which will be used then, avoid/donot use Async in this case, as we are unsure of the thread execution on the background. So better first try and know the scenario and then use async methods. I hope I could explain something to you. Accept if this helps you understand anyway. Thanks again.

Mahesh Chand · Answer

ADO.NET is much faster than EF. If perfromance is your criteria for you app, use ADO.NET. EF's advantage is to save time and not write bunch of Data layer code.

Ali Javani · Answer

Create Your OWN Micro ORM (as a 200 line class) and don't USE Low Performance OF EF . don't forget in big data (4 GB+) you just need use SP. in complexity result queries best solution is SP. EF Code First can't mapping your SP but your Micro ORM rely can do that. this is one of reason of 1000 reasons ORM never can better than you'r code. in real big projects you need use ( distributed , service oriented ) APP EF DB FIRST just a good mapper for your DB (Nothing More) cause you have complexity business any way ;) ___________________________________ In addition System.Data.SqlClient.SqlConnection have async too GOOD LUCK

Francis · Answer

1. This stack overflow discussion is give reason for your question. This MSDN url also give a good over view on the performance of EF. 2. It's always good to close a connection whenever you have done your job with it. 3. It depends on your scenario.

Munesh Sharma · Answer

First time EF loads metadata into memory, that takes a time. It builds in-memory representation of model from edmx file, or from source code if you are using code first. Actually EF is build at the top of ADO.NET, so it can't be faster. But it makes development much faster. And improvesmaintainability of your code.