c# - multiple - linq a sql startwith columnas indexadas al rendimiento

linq to sql

Question

Yo uso primero el código de entidad. Columnas indexadas:

• SourceCatalogId
• Discapacitado
• CategoryPath

40 000 filas en la Tabla,

¡Mi problema es que la consulta tarda 40 segundos!

var result = DBContext.Set<SourceProduct>()
            .Include(x => x.SalesHistories, x => x.SourceCatalog)
            .Where(p => p.SourceCatalogId == 2)
            .where(p => p.Disabled == false)
            .where(x => x.CategoryPath.StartsWith("MyPath"))
            .orderby(x => x.ShortDesignation)
            .Skip(1)
            .Take(10)
            .toList();

SQL vía sql profiler:

exec sp_executesql N''SELECT TOP (10) 
[Project1].[SourceProductId] AS [SourceProductId], 
[Project1].[SourceSKU] AS [SourceSKU], 
[Project1].[SourceCatalogId] AS [SourceCatalogId], 
[Project1].[ManufacturerReference] AS [ManufacturerReference], 
[Project1].[Disabled] AS [Disabled], 
[Project1].[EAN] AS [EAN], 
[Project1].[ShortDesignation] AS [ShortDesignation], 
[Project1].[FullDesignation] AS [FullDesignation], 
[Project1].[Description] AS [Description], 
[Project1].[Url] AS [Url], 
[Project1].[CategoryPath] AS [CategoryPath], 
[Project1].[Condition] AS [Condition], 
[Project1].[BuyingPriceHT] AS [BuyingPriceHT], 
[Project1].[ShippingPriceHT] AS [ShippingPriceHT], 
[Project1].[PublicSellingPriceHT] AS [PublicSellingPriceHT], 
[Project1].[PictureUrl1] AS [PictureUrl1], 
[Project1].[PictureUrl2] AS [PictureUrl2], 
[Project1].[PictureUrl3] AS [PictureUrl3], 
[Project1].[PictureUrl4] AS [PictureUrl4], 
[Project1].[Quantity] AS [Quantity], 
[Project1].[AddDate] AS [AddDate], 
[Project1].[UpdateDate] AS [UpdateDate], 
[Project1].[Followers] AS [Followers]
FROM ( SELECT [Project1].[SourceProductId] AS [SourceProductId], [Project1].[SourceSKU] AS [SourceSKU], [Project1].[SourceCatalogId] AS [SourceCatalogId], [Project1].[ManufacturerReference] AS [ManufacturerReference], [Project1].[Disabled] AS [Disabled], [Project1].[EAN] AS [EAN], [Project1].[ShortDesignation] AS [ShortDesignation], [Project1].[FullDesignation] AS [FullDesignation], [Project1].[Description] AS [Description], [Project1].[Url] AS [Url], [Project1].[CategoryPath] AS [CategoryPath], [Project1].[Condition] AS [Condition], [Project1].[BuyingPriceHT] AS [BuyingPriceHT], [Project1].[ShippingPriceHT] AS [ShippingPriceHT], [Project1].[PublicSellingPriceHT] AS [PublicSellingPriceHT], [Project1].[PictureUrl1] AS [PictureUrl1], [Project1].[PictureUrl2] AS [PictureUrl2], [Project1].[PictureUrl3] AS [PictureUrl3], [Project1].[PictureUrl4] AS [PictureUrl4], [Project1].[Quantity] AS [Quantity], [Project1].[AddDate] AS [AddDate], [Project1].[UpdateDate] AS [UpdateDate], [Project1].[Followers] AS [Followers], row_number() OVER (ORDER BY [Project1].[ShortDesignation] ASC) AS [row_number]
    FROM ( SELECT 
        [Extent1].[SourceProductId] AS [SourceProductId], 
        [Extent1].[SourceSKU] AS [SourceSKU], 
        [Extent1].[SourceCatalogId] AS [SourceCatalogId], 
        [Extent1].[ManufacturerReference] AS [ManufacturerReference], 
        [Extent1].[Disabled] AS [Disabled], 
        [Extent1].[EAN] AS [EAN], 
        [Extent1].[ShortDesignation] AS [ShortDesignation], 
        [Extent1].[FullDesignation] AS [FullDesignation], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[Url] AS [Url], 
        [Extent1].[CategoryPath] AS [CategoryPath], 
        [Extent1].[Condition] AS [Condition], 
        [Extent1].[BuyingPriceHT] AS [BuyingPriceHT], 
        [Extent1].[ShippingPriceHT] AS [ShippingPriceHT], 
        [Extent1].[PublicSellingPriceHT] AS [PublicSellingPriceHT], 
        [Extent1].[PictureUrl1] AS [PictureUrl1], 
        [Extent1].[PictureUrl2] AS [PictureUrl2], 
        [Extent1].[PictureUrl3] AS [PictureUrl3], 
        [Extent1].[PictureUrl4] AS [PictureUrl4], 
        [Extent1].[Quantity] AS [Quantity], 
        [Extent1].[AddDate] AS [AddDate], 
        [Extent1].[UpdateDate] AS [UpdateDate], 
        [Extent1].[Followers] AS [Followers]
        FROM [dbo].[SourceProducts] AS [Extent1]
        WHERE ([Extent1].[SourceCatalogId] = @p__linq__0) AND (0 = [Extent1].[Disabled]) AND ([Extent1].[CategoryPath] LIKE @p__linq__1 ESCAPE N''''~'''')
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[ShortDesignation] ASC'',N''@p__linq__0 bigint,@p__linq__1 nvarchar(4000)'',@p__linq__0=2,@p__linq__1=N''MyPath%''

En la última cláusula where before, si elimino " escape N '''' ~ '''' " en:

WHERE ([Extent1].[SourceCatalogId] = @p__linq__0) AND (0 = [Extent1].[Disabled]) AND ([Extent1].[CategoryPath] LIKE @p__linq__1 ESCAPE N''''~'''')

la consulta toma 4s.

Es normal ? ¿Utiliza el índice? ¿Cómo puedo resolverlo con startWith?

EDITAR

Índice atributivo para la categoría Ruta:

[Index("IX_SourceProduct_SourceCatalogId_Disabled_CategoryPath", 3), StringLength(400)]
    public string CategoryPath { get; set; }

EDIT2

OK, creo que estoy bastante cerca, creo que el problema es el procedimiento almacenado.

string search = "julien";
            var list = db.Users.Where(x => x.Name.StartsWith(search));
            string query = list.ToString();

=> SELECT [Extensión1]. [UserId] AS [UserId], [Extensión1]. [Nombre] AS [Nombre] FROM [dbo]. [Usuarios] AS [Extensión1] DONDE [Extensión1]. [Nombre] LIKE @ p__linq__0 ESCAPE N ''~''

var list2 = db.Users.Where(x => x.Name.StartsWith("julien"));
            string query2 = list2.ToString();

=> SELECT [Extensión1]. [UserId] AS [UserId], [Extensión1]. [Nombre] AS [Nombre] FROM [dbo]. [Usuarios] AS [Extensión1] DONDE [Extensión1]. [Nombre] LIKE N''julien % ''

Entonces, si uso una variable en la consulta para obtener un procedimiento almacenado, si uso const, obtengo una selección.

En el procedimiento almacenado (generado por entidad) hace que aparezca @ p__linq__0 así que agregue ESCAPE N ''~'' para evitar wildCaractere en la variable.

Entonces ahora la pregunta es más simple. ¿Cómo evitar la consulta con variable? Es posible ? Gracias

Answer 1

Entonces, lo que tienes que hacer aquí es tomar el valor de una variable y usarla como una constante en una Expression que estás generando. Esto es bastante posible. Lo que necesitaremos es una expresión que acepte el parámetro que desee como parámetro de su selector real, como un segundo parámetro que sea un marcador de posición para el valor constante, y luego el valor que desea que sea una constante. Entonces podemos reemplazar todas las instancias del parámetro con el valor de la constante, dejando solo una función que mapea el parámetro real al resultado:

public static Expression<Func<TSource, TResult>> EmbedConstant
    <TSource, TResult, TConstant>(
    this Expression<Func<TSource, TConstant, TResult>> expression,
    TConstant constant)
{
    var body = expression.Body.Replace(
        expression.Parameters[1],
        Expression.Constant(constant));
    return Expression.Lambda<Func<TSource, TResult>>(
        body, expression.Parameters[0]);
}

Esto se basa en los siguientes métodos para reemplazar todas las instancias de una expresión por otra:

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

Esto le permite mapear esto:

string search = "julien";
var list = db.Users.Where(x => x.Name.StartsWith(search));
string query = list.ToString();

Dentro de esto:

string search = "julien";
Expression<Func<User, string, bool>> predicate = 
    (item, searchTerm) => item.Name.StartsWith(searchTerm);
var list = db.Users.Where(predicate.EmbedConstant(search));
string query = list.ToString();