LINQ takes 40s to end query - Performance

Question

I am making a call to an external database outside of my local network, however the query takes 40 seconds to end..

i am making the call using edmx.

String Example = "Example";
var Result = EDMXEntity.Entities
    .Where(
        x => 
        (
            x.Name.ToString().ToLower().Contains(Example.ToLower())
        ))
    .Take(50)
    .ToList();

Current code thanks to Szer:

var Result = EDMXEntity.Entities.Where(x => SqlFunctions.PatIndex(x.Name.ToString().ToLower(), Example.ToLower()) > 0).Take(50).ToList();

Answer 1

Late answer from my comment above:

Problem is that you are loading all Entities from DB to memory and filtering on your machine. You should filter your query before with help of your DB engine.

To do that you should use SqlFunctions which are mapped to direct T-SQL code by LINQ SQL provider. In your case you could replace string.Contains() to SqlFunctions.PatIndex which is almost the same (it returns int instead of bool)

Like this:

var result = EDMXEntity.Entities
   .Where(x => SqlFunctions.PatIndex(
      stringPattern: x.Name.ToString().ToLower(), 
      target:        Example.ToLower()) > 0)
   .Take(50)
   .ToList();

Answer 2

Your best bet for the String comparison would be to use case inequality, so you don't have to deal with converting strings. In that case, the .ToString on the name isn't really necessary I don't think (then again, I'm not sure how it is stored, so it could be needed). Also with the name, you could try using equality or at least

Finally, you could try splitting it out into a Queryable object, and running the query that way. This way, you can cut down on the number of resources used for the query itself.

Edit

Since it's not desirable to check with the whole string, you could use IndexOf instead in order to not use complete strings

See the following:

String Example = "Example";
var EDMXEntity = new List<String>();
var Query = EDMXEntity.Entities.AsQueryable();
var Result = Query
    .Where(
        x => 
        (
            x.Name.ToString().ToLower().Contains(Example.ToLower())
        ))
    .Take(50)
    .ToList();

Answer 3

sql server management studio does it in less than a second

Chances are, this is caused by a bad cached query execution plan, as described here, here and here.

People have found that running the following commands against SQL Server fixes this problem for them. (It's possible that only the second command is really necessary.)

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE