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

Question

Since LINQ to SQL basically is 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 is irrelevant?

Answer 1

In execution it is fractionally slower. However, Linq to SQL saves developer time which is it's main benefit, IMHO.

Kindness,

Dan

Answer 2

Technically, yes there is some overhead, and Linq2Sql can generate some less that optimal SQL sometimes, but unless you are doing high performance or high throughput work you are probably unlikely to notice it.

My advice would be pick the DA technology that suits your requirements the most and go with it. Remember the advantages of things like Linq2SQL or EF is decreased developer time spent writing repetitive DA layers, and reduced code (so in theory perhaps reduced bugs and less maintenance - although that's debatable). Then profile for performance later and if you detect a bottle neck, you can start optimising specific queries then, and if absolutely necessary, very important queries that are causing big bottle necks can be converted to just plain ado.net

Answer 3

It does mean that ADO.NET is faster. It also gives you heaps of more freedom to optimize your SQL queries (well technically, you could use LINQ to SQL with stored procedures only, but you'd miss out on the whole point).

The fact is that if you really really really need to optimize for best performance, then nobody really recommends using an OR/M. There are heaps of considerations with OR/M:s, performance-wise. But a lot of sites do not really need to optimize that much for performance, in much the same way that we can afford programming in .NET rather than assembler, even though that is the same kind of overhead as compared to writing code in a lower level language.

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

Answer 4

It is true that LINQ to SQL is a layer on top of ADO.NET. But so are other options that allow you to hold the results in memory. If your intent is to act on the data immediately after retrieval you are better off to work with ADO.NET DataReader within a loop for better performance. However, if you need to do further processing on the data you will need to hold in it memory using some sort of variable such as an LINQ to SQL Entity or DataTable. Both of these memory variables are basically generated the same way under the covers. Given this fact, I’d say that the speed of which each option is available in memory is not as import as how you plan on working with the data.