Is it faster to query using linq-to-entities or a data adapter?

Question

I'm creating an MVC application in VS2012 and I reverse-engineered the models from existing Oracle tables. The tables are part of a MASSIVE database. Besides the main tables that I need to insert, update and delete from I also (on a whim) selected a few tables and views that my application only needs to read from. Now I am doubting my decision and considering deleting the less-important models.

It is definitely neater to query using linq-to-entities instead of creating an oracle connection, creating an adapter, filling a dataset and reading what I need, but which is faster?

Answer 1

In general, ADO.NET (OracleConnection, OracleCommand, etc.) is going to be faster.

However, in cases where your LINQ query gets translated directly into a single SQL query, the difference in speed should be pretty minimal. There is a certain amount of additional wrapper logic that still must be applied (and, you're at the mercy of the translation logic), but in the overall scheme of things the difference in speed is usually pretty insignificant.

The real slowdown occurs when you do something in your LINQ query that prevents it from being executed as a single SQL query, e.g. invoking ".ToList()" on one of the collections you're joining together in your "from" clause. Joining to something that's not a database table would also have this effect, but that's not really a fair comparison; ADO.NET can't even run that sort of query (at least, not as a query per se).

In my experience, one can tell whether a LINQ query is getting directly translated to a SQL query by trying to get the actual SQL during a runtime debugging session. If it's there (e.g. when you hover over a variable into which the query is loaded), that's good.

Answer 2

Try it and see!

One thing to note is that if you use Linq, and you do not include a .Select clause, you will be (effectively) running "select * " from the DB, which will be slower and less efficient.

When I last used Oracle with Linq, it generated particularly nasty SQL, running to many pages. I think the provider has been updated since then. You can turn on logging and see what SQL is being run when your LINQ query is executed, and look at the execution plan to see the difference between that and your hand-coded query.

Of course, you could do the best of both worlds: hand-write your queries and expose them as stored procedures. Then use Linq to access these, the fast and efficient query will be executed even though you're calling it from LINQ.