Linq query is super slow?

Question

I copied the sql query built by linq from sql profiler here is what's annoying about it:

1. It prefixes select ..columnNames.. from (select ..columnNames.. from ( select cNames from view) Comments: the 2 select statements here are unnecessary.

2. this query takes 14 seconds to execute

3. when I remove the last line, containing all the params like the @p_linq_0 .. and fill the param values in where condition and execute the very same dynamic query it takes 1 sec or less. Even otherwise this query is taking 0 sec when performed from sql studio. This is so lame of linq.

I might move this to stored proc but I'm scared of using linq now

After further research i found out:

Query 1 :

exec sp_executesql N'SELECT * from TableView WHERE Id = @Id', N'@Id int', @Id = 1

Query 2:

exec sp_executesql N'SELECT * from TableView WHERE Id = 1'

Query 1 takes 12 seconds, Query 2 takes 0 second. This explains why linq query is slow. So now, does that mean I should always use stored procedures or am I missing something?

Why can't Microsoft fix Linq query building, how difficult is it to parse that string and replace the param values instead of passing those params as arguments to sp_executesql

Answer 1

I'm with you. I mean, you could probably uncover the reason for this catastrophic performance hit you've discovered and find a remedy. But in practice this sort of thing happens with Linq to SQL and spending this extra time to optimize your Linq statement so that the SQL query is more performant on the back-end kind of defeats the purpose. Linq is supposed to make things easier.

Personally I use Linq-to-SQL or Entity Framework for performing CRUD operations on single records. And then with any large SELECT statements I'll simply write a stored proc as I've always done. This seems to be a good compromise between productivity and performance that has worked well for me.

EDIT: In fact the Entity Framework guys anticipated this. And Entity Framework works pretty well with stored procedures. You can add a stored proc to your Entity Framework model and get all that strongly typed goodness. Then you can call that stored proc from code.

Answer 2

This will work.

ParameterExpression parameterExpression = Expression.Parameter(typeof(TableView), "v");
Expression equalsExpression = Expression.Equal(Expression.Property(parameterExpression, "Id"), Expression.Constant(Id));
Expression<Func<TableView, bool>> predicate = Expression.Lamda<Func<TableView, bool>>(equalsExpression, parameter);

var query = from v in tableView
            orderby v.RDate descending
            select v;

if(!daysRange.Equals("All"))
{
  query = query.Where(v => v.RdDate >= fromDate && v.RDate <= toDate);
}

query.Where(predicate).ToList();

Answer 3

You should build the Where predicate dynamically taking out the Application specific daysRange like so

var query = from v in tableView
            where v.Id == Id
            orderby v.RDate descending
            select v;

if(!daysRange.Equals("All"))
{
  query = query.Where(v => v.RdDate >= fromDate && v.RDate <= toDate);
}

query.ToList();