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();

your `IQueryable` converts to `SELECT TOP 50 * FROM [Extent1] WHERE (CAST(CHARINDEX(LOWER(@p__linq__0), LOWER(CASE WHEN ([Extent1].[Name] IS NULL) THEN N'' ELSE [Extent1].[Name] END)) AS INT)) > 0`. Probably where clause is the reason for lags — Szer, Jun 17 '15 at 16:12
@Szer yes the query is converted to that, what i wanted was someting like select * from Entities Where lower(Name) like '%Example%', executing this query in SqlServer management studio it takes ~1s — BrunoMartinsPro, Jun 17 '15 at 16:15
@neverendingqs yes the query generated by EF using LINQ is slow.. — BrunoMartinsPro, Jun 17 '15 at 16:16
@Lightwalker try this `var Result = EDMXEntity.Entities.Where(x => SqlFunctions.PatIndex(x.Name.ToString().ToLower(), Example.ToLower()) > 0).Take(50).ToList();` — Szer, Jun 17 '15 at 16:24
@Szer WOW that was a stunning increase of performance, but i had to change the expression a bit, but it seems in some cases it now takes ~30s others it takes ~3s.. var Result = EDMXEntity.Entities.Where(x => SqlFunctions.PatIndex("%" + Example.ToLower() +"%", x.Name.ToString().ToLower()) > 0).Take(50).ToList(); — BrunoMartinsPro, Jun 17 '15 at 16:47
@Szer that was my first guess, but sql server management studio does it in less than a second.. — BrunoMartinsPro, Jun 17 '15 at 16:58
@Szer can you answer the question with your solution so i can mark it as closed? "var Result = EDMXEntity.Entities.Where(x => SqlFunctions.PatIndex(x.Name.ToString().ToLower(), Example.ToLower()) > 0).Take(50).ToList();" — BrunoMartinsPro, Jul 24 '17 at 11:42

score 1 · Answer 1 · edited May 23 '17 at 12:29

1

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

edited May 23 '17 at 12:29

Community

1
1

answered Jun 19 '15 at 14:42

StriplingWarrior

151,543
27
246
315

Dylan Corriveau · Answer 2 · 2015-06-22T12:11:55.220

1

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();

edited Jun 22 '15 at 12:11

answered Jun 19 '15 at 15:03

Dylan Corriveau

2,561
4
29
36

Dylan, the problem with this approach is that the query expects complete strings meaning if the Name of a user is "John", if you search "Joh" you wont get any data, thats why(thanks to @Szer) i used the SqlFunctions.PatIndex("%" + Example.ToLower() +"%", x.Name.ToString().ToLower()) > 0, but since you will only run the query when you do .ToList() it may be faster. – BrunoMartinsPro Jun 19 '15 at 16:16
1

@Lightwalker I have updated my code with a better implementation (one that won't just use complete strings) – Dylan Corriveau Jun 19 '15 at 16:42
linq throws an error... "LINQ to Entities does not recognize the method 'Int32 IndexOf(System.String, System.StringComparison)' method, and this method cannot be translated into a store expression." – BrunoMartinsPro Jun 22 '15 at 09:50
The only difference between your edit and my initial code is .AsQueryable(); – BrunoMartinsPro Jun 22 '15 at 15:14

score 1 · Accepted Answer · answered Jul 24 '17 at 12:42

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();

LINQ takes 40s to end query - Performance

3 Answers3