linq to sql - index - LinqToSql.Contains y nvarchar vs varchar parameters-> plan de conversión de índice

sql server like vs contains performance

Question

Tengo una sola tabla mapeada en un contexto de datos. Aquí está la propiedad y el atributo en la columna de interés:

[Column(Storage="_CustomerNumber", DbType="VarChar(25)")]
public string CustomerNumber
{

Esta columna es, de hecho, un varchar (25) y tiene un índice.

Tengo un código simple:

DataClasses1DataContext myDC = new DataClasses1DataContext();
myDC.Log = Console.Out;

List<string> myList = new List<string>() { "111", "222", "333" };
myDC.Customers
    .Where(c => myList.Contains(c.CustomerNumber))
    .ToList();

Que genera este texto SQL:

SELECT [t0].[CustomerNumber], [t0].[CustomerName]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerNumber] IN (@p0, @p1, @p2)
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [111]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [222]
-- @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [333]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

¡Note que los parámetros son nvarchar!

Cuando esta consulta llega a la base de datos, genera un plan horrible que implica convertir el índice de filas multimillonario en NúmeroCliente en nvarchar antes de buscar dentro de él.

No puedo cambiar la tabla, pero puedo cambiar la consulta y el dbml. ¿Qué puedo hacer para obtener los datos sin obtener esta conversión de índice?

Answer 1

Esto es lo que tengo como un posible trabajo alrededor. Estoy interesado en ver otras soluciones también:

List<IQueryable<Customer>> myQueries = 
    myList.Select(s => myDC.Customers.Where(c => c.CustomerNumber == s)).ToList();
IQueryable<Customers> myQuery = myQueries.First();
foreach(IQueryable<Customer> someQuery in myQueries.Skip(1))
{
    myQuery = myQuery.Concat(someQuery);
}
myQuery.ToList();

Esto genera el siguiente SQL:

SELECT [t4].[CustomerNumber], [t4].[CustomerName]
FROM (
    SELECT [t2].[CustomerNumber], [t2].[CustomerName]
    FROM (
        SELECT [t0].[CustomerNumber], [t0].[CustomerName]
        FROM [dbo].[Customer] AS [t0]
        WHERE [t0].[CustomerNumber] = @p0
        UNION ALL
        SELECT [t1].[CustomerNumber], [t1].[CustomerName]
        FROM [dbo].[Customer] AS [t1]
        WHERE [t1].[CustomerNumber] = @p1
        ) AS [t2]
    UNION ALL
    SELECT [t3].[CustomerNumber], [t3].[CustomerName]
    FROM [dbo].[Customer] AS [t3]
    WHERE [t3].[CustomerNumber] = @p2
    ) AS [t4]
-- @p0: Input VarChar (Size = 3; Prec = 0; Scale = 0) [111]
-- @p1: Input VarChar (Size = 3; Prec = 0; Scale = 0) [222]
-- @p2: Input VarChar (Size = 3; Prec = 0; Scale = 0) [333]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Answer 2

Esta es la forma en que resuelvo este problema hoy en día. Esto convierte los parámetros al tipo deseado y luego ejecuta la consulta. Genera el mismo sql que se generó originalmente, solo con diferentes tipos de parámetros.

DbCommand myCommand = myDataContext.GetCommand(query);

foreach (DbParameter dbParameter in myCommand.Parameters)
{
  if (dbParameter.DbType == System.Data.DbType.String)
  {
    dbParameter.DbType = System.Data.DbType.AnsiString;
  }
}    

myDataContext.Connection.Open();

System.Data.Common.DbDataReader reader = myCommand.ExecuteReader();
List<RecordType> result = myDataContext.Translate<RecordType>(reader).ToList();

myDataContext.Connection.Close();

Answer 3

Para el proyecto en el que estoy trabajando, tenemos que usar ObjectContext lugar de DbContext , por lo que no tengo acceso inmediato a GetCommand() o a los DbParameters . Lo que hago es convertir la consulta en una cadena, eliminar el indicador NVARCHAR en la consulta y luego ejecutar la consulta contra ObjectContext . Aquí hay una implementación detallada para el ejemplo dado:

List<string> myList = new List<string>() { "111", "222", "333" };

IQueryable<Customers> badQuery =  myDC.Customers
    .Where(c => myList.Contains(c.CustomerNumber));

string query = ((System.Data.Objects.ObjectQuery)badQuery).ToTraceString();
query = query.Replace(",N''", ",''").Replace("(N''","(''");

List<Customers> customers = myDC.ExecuteStoreQuery<Customers>(query).ToList();