linq to sql startwith индексированные столбцы производительности

Сначала я использую код сущности. Индексированные столбцы:

  • SourceCatalogId
  • Неполноценный
  • КатегорияПуть

40 000 строк в таблице,

Моя проблема в том, что запрос занимает 40 секунд!

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 через профилировщик sql:

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%'

В последнем перед предложением where, если я удалю "escape N''~''" в:

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

запрос занимает 4 секунды.

Это нормально? Индекс использует ? Как я могу решить это с помощью startWith?

ИЗМЕНИТЬ

Атрибут индекса для categoryPath:

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

ИЗМЕНИТЬ2

Хорошо, я думаю, что я довольно близок, я думаю, проблема в хранимой процедуре.

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

=> ВЫБЕРИТЕ [Extent1].[UserId] AS [UserId], [Extent1].[Name] AS [Name] FROM [dbo].[Users] AS [Extent1] WHERE [Extent1].[Name] LIKE @p__linq__0 < strong>ESCAPE N'~'

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

=> ВЫБЕРИТЕ [Extent1].[UserId] AS [UserId], [Extent1].[Name] AS [Name] FROM [dbo].[Users] AS [Extent1] WHERE [Extent1].[Name] LIKE N'julien %'

Итак, если я использую переменную в запросе для получения хранимой процедуры, если я использую const, я получаю выбор.

В хранимой процедуре (сгенерированной сущностью) отображается @p__linq__0, поэтому добавьте ESCAPE N'~', чтобы избежать wildCaractere в переменной.

Так что теперь вопрос проще. Как избежать запроса с переменной? возможно ? Благодарность


sql c# linq entity
person Julian50    schedule 19.09.2014    source источник
comment
Просто найдите этот пост, но не поймите, есть ли какое-нибудь решение? stackoverflow .com/questions/20496098/   -  person Julian50    schedule 19.09.2014
comment
вы используете полнотекстовый индекс или просто обычное индексирование (которое не поможет вам с частичным сопоставлением строк)?   -  person Mashton    schedule 19.09.2014
comment
@Mashton Я не понимаю вашего вопроса, но именно так я индексирую код, сначала вижу первый РЕДАКТИРОВАТЬ   -  person Julian50    schedule 19.09.2014
comment
Полнотекстовый индекс используется, когда вы хотите ускорить сопоставление строк с образцом, и это не то же самое, что обычное индексирование столбца. Индексация столбца означает, что он может быстрее находить точные совпадения столбцов, но FTI позволяет вам сопоставлять биты этих строк. Вы не сможете использовать свой linq.StartsWith, поскольку FTI требует, чтобы вы использовали команду sql CONTAINS, что означает, что вам придется начать вызывать хранимые процедуры. Но увеличение скорости того стоит. Мы перешли с 1 минуты 30 секунд на ‹1 секунду для запроса, который мы выполняли. simple-talk .com/sql/learn-sql-сервер/   -  person Mashton    schedule 19.09.2014
comment
@Mashon, спасибо за ответ. Теперь про полнотекстовый индекс стало понятнее. Но мне нужна только функция stratWith, и простого индекса должно быть достаточно (для функции contains вы правы, потребуется полный текст). Я могу проверить, что я говорю, когда я удаляю ESCAPE N''~'' в запросе sql. Запрос такой быстрый, и я вижу в профилировщике sql, что использую индекс. Как я могу отключить функцию побега в сущности? выглядит закрытым для этого: stackoverflow.com/questions/20496098/   -  person Julian50    schedule 19.09.2014

Ответы (1)


arrow_upward
7
arrow_downward

Итак, что вам нужно сделать здесь, это взять значение переменной и использовать его как константу в Expression, который вы генерируете. Это на самом деле вполне возможно. Нам понадобится выражение, которое принимает параметр, который вы хотите, в качестве параметра вашего реального селектора, в качестве второго параметра, который является заполнителем для постоянного значения, а затем значение, которое вы хотите сделать константой. Затем мы можем заменить все экземпляры параметра значением константы, оставив только функцию, которая отображает реальный параметр в результат:

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]);
}

Это основано на следующих методах замены всех экземпляров одного выражения другим:

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);
    }
}

Это позволяет вам отобразить это:

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

В это:

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();
person Servy    schedule 19.09.2014
comment
Удивительно работает!!! Один день, потраченный на это... большое спасибо. Могу я предложить вам медведя ;) (как мы говорим спасибо во Франции) - person Julian50; 19.09.2014
comment
ВТФ? Весь этот код только для того, чтобы включить в запрос «Содержит» или «StartWIth»??? и без этого .. это было бы ооочень медленно. - person Arcadian; 20.02.2015
comment
как мне использовать это с LINQ для возражения? это дает мне ошибки - person Arcadian; 20.02.2015
comment
пытаясь сделать это для linq to object var results = (из I в db.mytable, где i.mychildtable.col1.contains(search) select I).tolist()); - person Arcadian; 20.02.2015
comment
Было бы полезно знать, куда поместить приведенный выше код. - person yondaimehokage; 06.05.2017
comment
Только что попробовал, это EF 6.2.0, и он до сих пор прекрасно работает! - person RoLYroLLs; 08.02.2019