Is Linq to SQL fast when using stored procedures?

Question

I want to use Linq for SQL to communicate db. I have read many pages mentioning that Linq is slow, but I like that it was a fast-moving technique.

Please help me using Linq to SQL with a stored procedure to bring any performance advantage?

thanks

Answer 1

I would recommend using stored procedures when accessing the database through LINQ-To-SQL or EF for better performance.

Using a simple LINQ that will generate SQL queries can be quite inefficient when it comes to any moderately complex query.

Answer 2

The main advantage here is to get the IDE to write you all the plumbing, i.e. get parameters and return data sorted.

In terms of performance; there is no expression tree for parsing with the stored procedure, so it should be pretty fast. However, internally, we noticed a random slowdown in the LINQ-to-SQL materializer, which can affect you if you use it heavily.

If you need the fastest performance, I recommend looking at the dapper-dot-net that we wrote (and released as OSS) after finding these pause materializers. The use is pretty simple. In particular, it neatly allows you to use dapper to do the hard work, but still use the data types created by the IDE for the data.

Inside we do not use stored procedures; we use a combination of:

• LINQ-to-SQL Expression Tree Code
• LINQ to SQL ExecuteQuery (etc.)
• dapper-dot-net

Performance preference is (in our experience) the last. The first is convenient to write and often fast enough. The middle one is fast, but when the dapper has an almost identical API (and faster), it's hard to love now, p

Answer 3

The overhead of building SQL is unlikely. This is especially noticeable when performing volume inserts. Using stored procedures can help here a little, since all this generator logic is distributed and the parameters are mapped to an SP call, which is very easy. For my own application, in particular with one of my tables with 100+ columns, the improvement was significant - at least four or five times faster than generating SQL on the fly.

This is fairly easy to do if you are only creating entity classes. Just declare your extensibility methods for Insert and Update in your generated entity classes. You will have an object handle, and from there you can connect ADO.NET to call SP.

With all that was said, if I were you, I would not go along this route until you confirm that this is the place where you encountered difficulties. For most purposes, the generated SQL works quite well.

Answer 4

Linq to SQL is as fast as manually writing ado.net code to call a stored procedure.