Alter EF Generated query parameters

Question

I'm using EF 6.4.4 to query a SQL view. Now the view is not really performing optimal, but i don't control it.

I'm executing the following code with a WHERE statement on a string/nvarchar property

_context.ViewObject
   .Where(x => x.Name == nameFilter)
   .ToList();

Similarly, i have the same SQL statement executed in SMSS

SELECT [Id]
     , [Name]
     , ...
FROM [View]
WHERE [Name] = '<nameFilter>'

My problem is that the EF variant is way slower than the direct SQL query. When checking out the SQL query generated by EF i see the following:

SELECT [Id] 
     , [Name]
     , ...
FROM [View]
WHERE [Name] = @p__linq__0

with parameter @p__linq__0 is of type NVARCHAR(4000) NULL

This even though that my input variable is not NULL and has a lenght of maximum 6 characters. When i execute the same sql query with this parameter, it is slow in SMSS as well. Apparently, this has somethign

So what i want to do is alter the SQL query parameter that EF is using to generate this query. This to make sure that my parameter is more accurately represented in the query and that i can get the same performance as directly in SMSS.

Is there a way to do this?

You appear to be chasing a red herring. What if you execute the original query with a parameter as well, and declare that parameter as `NVARCHAR(6) NOT NULL`? — CodeCaster, Jul 31 '20 at 09:12
Show us the code where you "execute the same sql query with this parameter"? What type is the `[Name]` db column, both in the table and as exposed by the view? — Caius Jard, Jul 31 '20 at 09:15
@CodeCaster yes, though you'll note the original assertion is that performance is good where `select x from y where z = 'str'` is done, and it is not a Unicode 'str'.. so really we should be looking at first the perf of `'str'` vs `N'str'` (and also make sure we are executing against a "warm" db) — Caius Jard, Jul 31 '20 at 09:17

Preben Huybrechts · Answer 1 · 2020-07-31T09:48:49.810

Whats going on: parameter sniffing

Execute the following in SSMS and you will propably see the same performance.

EXECUTE sp_executesql N'SELECT [Id]
         , [Name]
         , ...
    FROM [View]
    WHERE [Name] = @nameFilter'
    ,N'@nameFilter nvarchar(4000)'
    ,@nameFilter = '<namefilter>';

sp_executeSql is used by EF to execute queries against a database and thus, when you write .Where(x => x.Name == nameFilter) this is translated to the above statement.

Making you suffer from parameter sniffing.

You could fix this by adding recompile to your queries like described here But be aware that adding recompile to all queries might have negative impact on the other queries.

You can execute the following queries with actual execution plan to see the difference:

Query with WHERE Name = @NameFilter
Query with WHERE Name = '<NameFilter>'
Query with WHERE Name = @NameFilter OPTION(RECOMPILE)

If it's not parameter sniffing, it might be implicit conversions, but I'm guessing both types are NVARCHAR so this shouldn't matter.

99% of the time it's parameter sniffing.

Alter EF Generated query parameters

1 Answers1