ADO.NET vs entity framework performance Point

K P Singh Chundawat

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Sahoo11y ago
Accepted 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.
Ali Javani9y ago
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
Mahesh Chand9y ago
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.
Munesh Sharma10y ago
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.
Francis Susaimichael11y ago
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.