LINQ to SQL vs ADO.NET - which is faster?

Question

Since LINQ to SQL is basically a layer on top of ADO.NET, it requires some translation. Does this mean that using ADO.NET directly is faster than LINQ? Or is the difference so small that it doesn't matter?

Answer 1

This means that ADO.NET is faster. It also gives you a ton of more freedom to optimize your SQL queries (technically, you can use LINQ to SQL only with stored procedures, but you would skip the whole point).

The fact is that if you really really need optimization for better performance, then no one recommends using OR / M. There are tons of considerations with OR / M: s, in terms of performance. But many sites really do not need to optimize this for performance, in much the same way that we can afford to program in .NET rather than in assembler, although this is the same overhead compared to writing code at a lower level.

The point of using LINQ to SQL or NHibernate, or indeed any other OR / M, is that (as with the .NET analogy) it will give you a lot of convenience, and it will save you a lot of development time and make refactoring and other subsequent changes much simpler task.

Answer 2

During execution, it is slower. However, Linq to SQL saves development time, which is a major advantage, IMHO.

Kindness,

Dan

Answer 3

Technically, yes, there is some overhead, and Linq2Sql can sometimes generate slightly less than optimal SQL, but if you are not performing high performance or high performance, you are unlikely to notice this.

My advice is to choose the DA technology that best suits your requirements and go with it. Remember the benefits of such things as Linq2SQL or EF, the time spent writing a developer of repeating DA layers is reduced, and the code is reduced (so in theory it is possible to have reduced errors and less maintenance - although this is debatable). Then the profile for performance later, and if you find the neck of the bottle, you can start optimizing specific queries, and, if absolutely necessary, very important queries that cause large bottle necks can be converted to simple ado.net.

Answer 4

It is true that LINQ to SQL is a layer on top of ADO.NET. But other parameters are also available that allow you to keep the results in memory. If you intend to act on the data immediately after extraction, you better work with the ADO.NET DataReader in a loop for better performance. However, if you need to perform further data processing, you will need to store the memory in it using some kind of variable, such as LINQ to SQL Entity or DataTable. Both of these memory variables are basically generated in the same way under covers. Given this fact, Id says that the speed with which each option is available in memory does not depend on how you plan to work with the data.