1

I have a filter where depending on the user selection I conditionally add in more Where/Joins.

Which method is faster than the other and why?

Example with Where:

var queryable = db.Sometable.Where(x=> x.Id > 30);
queryable = queryable.Where(x=> x.Name.Contains('something'));
var final = queryable.ToList();

Example with Join:

var queryable1 = db.Sometable.Where(x=> x.Id > 30);
var queryable2 = db.Sometable.Where(x=> x.Name.Contains('something'));
var final = (from q1 in queryable1 join q2 in queryable2 on q1.Id equals q2.Id select q1).ToList();

NOTE: I would have preferred the multiple Where but it is causing error as described in a question. Hence had to shift to JOIN. Hope 'JOIN' code is not slower than multiple WHERE

Community
  • 1
  • 1
Shyamal Parikh
  • 2,988
  • 4
  • 37
  • 78

3 Answers3

2

I just tried running similar linq statements against an MSsql 2008 database table with 10million rows. I found that the query optimizer converted both statements into similar query plans and the performance difference was a wash.

I would say that as someone who is reading the code, the first example more clearly states your intentions, and therefore would be preferred. Many times performance is not the best metric to choose when evaluating code.

Aheho
  • 12,622
  • 13
  • 54
  • 83
0

I'm sure you already understand that LINQ converts your code into a SQL statement. Your first query would result in something like:

SELECT * FROM Sometable WHERE Id > 30 AND Name LIKE '%something%'

Your second query would result in something like

SELECT q1.* 
FROM Sometable q1 
    JOIN Sometable q2 ON q1.Id = q2.Id
WHERE q1.Id > 30 AND q2.Name LIKE '%something%')

Nearly every time, a select from a single will return results faster than a join between 2 tables.

If you LINQ statement is failing to add tables, be sure you are including them.

var queryable = db.Sometable.Include(i => i.ForeignTable).Where(x=> x.Id > 30);
Justin
  • 541
  • 7
  • 15
0

i whould go for the where clause, avoiding to self joining the same table and make the code clearer

you can add a log to your dbcontext to see the generated sql query

db.context.Database.Log = System.Diagnostic.Debug.WriteLine;

anyway to improve the performance of the query i would :

  1. select ONLY the fields that you actually need (not *)

  2. check the indexes of the table

  3. do you really need the contains statement ? if the records grow a lot you will have performance issue with sql as "like '%XXX%'"
nrgjack
  • 66
  • 8