Is Linq to SQL fast when using with stored procedures?

Question

I want to use Linq to SQL for db communications. I have read many a page mentioning Linq is slow, but I like its being rapid development technique.

Please guide me using Linq to SQL with stored procedure bring any advantage in term of performance ?

Thanks

Answer 1

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

In terms of raw performance; with a stored procedure there is no expression tree to parse, so it should be pretty quick. However, internally we have noticed occasional slowdowns in the LINQ-to-SQL materializer, which might impact you if you have heavy usage.

If you want the fastest performance, my advice is to look at dapper-dot-net, which we wrote (and released as OSS) after finding these materializer pauses. The usage is pretty simple. In particular, this neatly allows you to use dapper to do the heavy work, but still use the IDE-generated types for the data.

Internally, we don't use stored-procedures; we use a combination of:

• LINQ-to-SQL expression tree code
• LINQ-to-SQL ExecuteQuery (etc) code
• dapper-dot-net

The preference for performance is (in our experience) the last. The first is convenient to write, and is often plenty fast enough. The middle option is fast, but when dapper has an almost identical API (and is faster) is hard to love now ;p

Answer 2

There's a fair bit of overhead in terms of constructing the SQL. This is particularly evident when doing bulk inserts. Using stored procedures can help quite a bit here, as all of that generator logic is dispensed and the parameters get mapped to an SP call which is very lightweight. For my own app, in particular with one of my tables with 100+ columns, the improvement was dramatic -- at least four or five times quicker than generating the SQL on the fly.

It's pretty easy to do, too, so long as you are the one generating your entity classes. Just declare your extensibility methods for Insert and Update in your generated entity classes. You'll have a handle to the entity, and from there you can make the ADO.NET connection to invoke the SP.

With all that being said, if I were you, I wouldn't go down this route until you confirm that this is where your performance bottleneck is. For most purposes, the generated SQL performs well enough.