linq to sql стартира с колони, индексирани за производителност

Първо използвам код на обект. Индексирани колони:

  • SourceCatalogId
  • хора с увреждания
  • CategoryPath

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''~'')

заявката отнема 4s.

нормално ли е Индексът използва? Как мога да го реша със 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();

=> SELECT [Extent1].[UserId] AS [UserId], [Extent1].[Name] AS [Name] FROM [dbo].[Users] AS [Extent1] WHERE [Extent1].[Name] LIKE @p__linq__0 < strong>ИЗБЯГВАНЕ 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, получавам select.

В съхранената процедура (генерирана от обект) се появява @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, което означава, че ще трябва да започнете да извиквате съхранени процедури. Но увеличението на скоростта си заслужава. Преминахме от 1m30s на ‹1s за заявка, която правехме. simple-talk .com/sql/learn-sql-server/   -  person Mashton    schedule 19.09.2014
comment
@Mashon, благодаря ви за отговора. Сега е по-ясно за индекса на пълен текст. Но имам нужда само от функцията stratWith и обикновен индекс трябва да е достатъчен (за функция съдържа сте прав, ще е необходим пълен текст). Мога да проверя какво казвам, като премахна ESCAPE N''~'' в sql заявката. Заявката е толкова бърза и виждам в sql profiler, че използвам индекса. Как мога да деактивирам функцията за бягство в обект? изглежда затворен за това: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
WTF? Целият този код само за включване на Contains или 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(търсене) изберете I).tolist()); - person Arcadian; 20.02.2015
comment
Би било полезно да знаете къде да поставите горния код. - person yondaimehokage; 06.05.2017
comment
Току-що пробвах, това е EF 6.2.0 и все още работи като чар! - person RoLYroLLs; 08.02.2019