What is more efficient with Joins and multiple selects working with LINQ?

Question

I have some complex queries with LINQ which behind the scenes do around 7-9 joins. I am in the process of optimizing the queries.Now I am little confused about a couple of things here -

1. Should I just have a stored procedure doing maybe dynamic sql instead of having it as a LINQ query.When should have a stored procedure doing something instead of having a LINQ query? I guess it depends..but what are the best practices? When should have a stored procedure? I see sometimes LINQ does do some weird inefficient stuff behind the scenes? That is my concern..

2. There are place where I do multiple selects...as compared when I can join with the tables but if get into joining them it gets little more complex(say 7-9 joins as compared to 3-5 Selects). I think personally join would be efficient right ? since only one request to database? Whereas with multiple selects it has to make multiple requests? What are your thoughts on it ?

Answer 1

There are many factors that contribute here and there are many unknowns for me, so the subtext for my answer is "it depends".

1) With a well defined Datacontext LINQ-to-SQL typically generates efficient queries. Filters are automatically parametrized which enables the database engine to cache execution plans. So I believe the only time stored procedures would perform better is if query hints were required or some other mechanic unsupported by the query generator. I believe it is best practice is use LINQ and only employ sprocs when a significant optimization can be demonstrated.

2) Typically limiting your trips to the DB is optimal. But reading the amount of joins required in your relationship makes wonder if you would be well served by adding a View. It'll abstract the complexity, views integrate well with LINQ and it would shield your code from future schema alterations.

Let me also recommend LINQPad to help optimize LINQ-to-SQL queries. It has become an indispensable tool for me and, if you aren't already using it, I bet it will become one for you as well.

Also, for more opinions regarding LINQ vs SProcs check out the discussion in this question: LINQ-to-SQL vs stored procedures?