NVarchar Prefix causes wrong index to be selected

问题:

I have an entity framework query that has this at the heart of it:

SELECT 1 AS dummy
FROM   [dbo].[WidgetOrder] AS widgets
WHERE  widgets.[SomeOtherOrderId] = N'SOME VALUE HERE'

The execution plan for this chooses an index that is a composite of three columns. This takes 10 to 12 seconds.

However, there is an index that is just [SomeOtherOrderId] with a few other columns in the "include". That is the index that should be used. And when I run the following queries it is used:

SELECT 1 AS dummy
FROM   [dbo].[WidgetOrder] AS widgets
WHERE  widgets.[SomeOtherOrderId] = CAST(N'SOME VALUE HERE' AS VARCHAR(200))

SELECT 1 AS dummy
FROM   [dbo].[WidgetOrder] AS widgets
WHERE  widgets.[SomeOtherOrderId] = 'SOME VALUE HERE'

This returns instantly. And it uses the index that is just SomeOtherOrderId

So, my problem is that I can't really change how Entity Framework makes the query.

Is there something I can do from an indexing point of view that could cause the correct index to be selected?

回答1:

As far as I know, since version 4.0, EF doesn't generate unicode parameters for non-unicode columns. But you can always force non-unicode parameters by DbFunctions.AsNonUnicode (prior to EF6, DbFunctions is EntityFunctions):

from o in db.WidgetOrder
where o.SomeOtherOrderId == DbFunctions.AsNonUnicode(param)
select o

回答2:

Try something like ....

SELECT 1 AS dummy
FROM   [dbo].[WidgetOrder] AS widgets WITH (INDEX(Target_Index_Name))
WHERE  widgets.[SomeOtherOrderId] = N'SOME VALUE HERE'

This query hint sql server explicitly what index to use to get resutls.