none
LINQ to SQL using contains (var) clause generate ESCAPE N'~' which slows performance significantly RRS feed

  • General discussion

  • I have added a "contains (searchVar)" clause to my LINQ query. The resulting LINQ to SQL translated query contained ESCAPE N'~'., which resulted in a significant performance drop. When removing ESCAPE N'~' from the SQL query, data returns within 2 sec, while with the ESCAPE N'~' , data returns after more than 30 secs, and in some cases simply times out. I have run the execution plan on the query, and found out that the ESCAPE N'~' causes the execution to run in a single threat, while without the ESCAPE N'~', it execute using parallel processing.

    I have two questions:

    1. While does the ESCAPE N'~' being added to the SQL query?

    2. How do I get rid of it? 

    In the interest of saving time, I have already tried all of the following with no success, simply because of how the query is built in my code. So please avoid the the solutions in your answer.

    1. turning the var into a constant

    2. using SqlMethods.Like 

    3. using SqlQuery('...")

    Your help is greatly appreciated.

    Friday, July 12, 2019 4:41 PM

All replies

  • https://social.msdn.microsoft.com/Forums/en-US/home?forum=linqtosql

    You're in luck there is a forum.

    Friday, July 12, 2019 6:49 PM
  • That's how it works when using variables in combination with contains.  The discussion is partially here. While it talks about EF, LINQ 2 SQL does the same thing. When using a variable it doesn't look at the value directly so it has to "encode" characters that SQL would see as escape characters. That is what the ~ is for. For constants it does know the values in advance so it just fixes them up before sending them.

    This really shouldn't impact performance but that is a question for the TechNet SQL forums. To work around it you have limited options.

    1. Rewrite your query so you don't need it.

    2. Move the query into SQL (via a sproc or inline query). You didn't mention why SqlQuery didn't work but since it doesn't translate stuff I'm surprised it didn't work.

    3. Some folks have said using PatIndex instead works but I think it would strongly depend upon your query.

    4. I guess you could also write a SQL batch command and then execute that directly instead of using LINQ's methods. This should also work around the issue and should be very similar to SqlQuery.


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, July 12, 2019 6:58 PM
    Moderator
  • If it is difficult to get rid of ESCAPE and your column is char or varchar, did you try to convert it to nchar or nvarchar, at least for experiments?

    Friday, July 12, 2019 7:58 PM
  • thank you Michael for your reply.

    Since you asked, the reason I can't use the methods I mentioned is that I am using IQueryable<type> object, which can not combine with SQLMethods or SQLQuery. Unless there is a way to mix the two.

    for example, if I have a series of filtering assignments:

    filter = filter.Where (<condition 1>)

    ...

    filter = filter.Where (<condition 2>)

    ...

    filter = filter.Where(x => x.SQLMethods("..."))

    ...

    list = filter.ToList(); //that does not work, seems like the mixing of IQueryable statement and the SQLMethods

    same results for SQLQeury, you can't mix it within the  IQueryable object.

    Monday, July 15, 2019 7:41 PM