🥇C#.NET | жүйесінде LINQ сұрауларын оңтайландыру әдістері ProHoster

Кіріспе

В Бұл мақала оңтайландырудың кейбір әдістері қарастырылды LINQ сұраулары.
Мұнда біз сонымен қатар кодты оңтайландыруға қатысты тағы бірнеше тәсілдерді ұсынамыз LINQ сұраулары.

Белгілі LINQ(Language-Integrated Query) – деректер көзіне сұрау салуға арналған қарапайым және ыңғайлы тіл.

А LINQ to SQL ДҚБЖ деректерге қол жеткізу технологиясы болып табылады. Бұл деректермен жұмыс істеуге арналған қуатты құрал, мұнда сұраулар декларативті тіл арқылы құрастырылады, содан кейін ол түрленеді. SQL сұраулары платформасы және орындау үшін дерекқор серверіне жіберіледі. Біздің жағдайда ДҚБЖ деп біз айтамыз MS SQL сервері.

Алайда, LINQ сұраулары оңтайлы жазылғандарға айналдырылмайды SQL сұраулары, оны тәжірибелі DBA оңтайландырудың барлық нюанстарымен жаза алады SQL сұраулары:

оңтайлы қосылымдар (ЖОЛ) және нәтижелерді сүзу (ҚАЙДА)
байланыстар мен топтық шарттарды пайдаланудағы көптеген нюанстар
жағдайларды ауыстырудағы көптеген өзгерістер IN туралы БАРи ЕМЕС, <> қосулы БАР
уақытша кестелер, CTE, кесте айнымалылары арқылы нәтижелерді аралық кэштеу
сөйлемді қолдану (Опция) нұсқаулармен және кестелік кеңестермен МЕН (...)
таңдау кезінде артық деректер оқуларынан құтылу құралдарының бірі ретінде индекстелген көріністерді пайдалану

Нәтижедегі өнімділіктің негізгі кедергілері SQL сұраулары құрастыру кезінде LINQ сұраулары мыналар:

бір сұраныста деректерді таңдаудың бүкіл механизмін біріктіру
кодтың бірдей блоктарын қайталау, бұл ақыр соңында бірнеше қажетсіз деректерді оқуға әкеледі
көп компонентті шарттар топтары (логикалық «және» және «немесе») - ЖӘНЕ и OR, күрделі шарттарға біріктіру, қажетті өрістер үшін қолайлы кластерленбеген индекстерге ие оңтайландырғыштың соңында кластерленген индекске қарсы сканерлеуді бастауына әкеледі (ИНДЕКСТІ СКАНА) шарттар топтары бойынша
ішкі сұраулардың терең ұясы талдауды өте проблемалы етеді SQL мәлімдемелері және әзірлеушілер тарапынан сұраныс жоспарын талдау және DBA

Оңтайландыру әдістері

Енді оңтайландыру әдістеріне тікелей көшейік.

1) Қосымша индекстеу

Негізгі таңдау кестелеріндегі сүзгілерді қарастырған дұрыс, өйткені көбінесе бүкіл сұрау бір немесе екі негізгі кестенің айналасында (қолданбалар-адамдар-операциялар) және стандартты шарттар жиынтығымен (Жабық, Болдырылған, Қосылған, Күй) құрылады. Анықталған үлгілер үшін сәйкес индекстерді құру маңызды.

Бұл өрістерді таңдағанда, бұл шешім қайтарылған жиынды сұрауға айтарлықтай шектейді.

Мысалы, бізде 500000 2000 өтініш бар. Дегенмен, бар болғаны XNUMX белсенді қолданбалар бар. Сонда дұрыс таңдалған индекс бізді одан құтқарады ИНДЕКСТІ СКАНА үлкен кестеде және кластерленбеген индекс арқылы деректерді жылдам таңдауға мүмкіндік береді.

Сондай-ақ, индекстердің жетіспеушілігін сұрау жоспарларын талдау немесе жүйе көрінісі статистикасын жинау арқылы анықтауға болады. MS SQL сервері:

Барлық көрініс деректері кеңістіктік индекстерді қоспағанда, жетіспейтін индекстер туралы ақпаратты қамтиды.

Дегенмен, индекстер мен кэштеу көбінесе нашар жазылғанның салдарымен күресу әдістері болып табылады LINQ сұраулары и SQL сұраулары.

Өмірдің қатал тәжірибесі көрсеткендей, бизнес үшін белгілі бір мерзімде іскерлік мүмкіндіктерді енгізу маңызды. Сондықтан, ауыр сұраулар көбінесе кэштеу арқылы фонға ауыстырылады.

Бұл ішінара негізделген, өйткені пайдаланушы әрқашан соңғы деректерге мұқтаж емес және пайдаланушы интерфейсінің қолайлы деңгейі бар.

Бұл тәсіл бизнес қажеттіліктерін шешуге мүмкіндік береді, бірақ ақыр соңында проблемаларды шешуді кешіктіру арқылы ақпараттық жүйенің өнімділігін төмендетеді.

Сондай-ақ, қосу үшін қажетті индекстерді іздеу процесінде ұсыныстарды есте ұстаған жөн MS SQL оңтайландыру дұрыс емес болуы мүмкін, соның ішінде келесі жағдайларда:

егер өрістердің ұқсас жиыны бар индекстер бар болса
егер кестедегі өрістерді индекстеу шектеулеріне байланысты индекстеу мүмкін болмаса (толығырақ сипатталған) осында).

2) Атрибуттарды бір жаңа атрибутқа біріктіру

Кейде шарттар тобына негіз болатын бір кестедегі кейбір өрістерді бір жаңа өріс енгізу арқылы ауыстыруға болады.

Бұл әдетте бит немесе бүтін типті күй өрістеріне қатысты.

Мысал:

Жабық = 0 ЖӘНЕ Болдырылды = 0 ЖӘНЕ Қосылған = 0 ауыстырылады Күй = 1.

Бұл күйлердің кестеде толтырылуын қамтамасыз ету үшін бүтін күй атрибуты осы жерде енгізіледі. Содан кейін бұл жаңа төлсипат индекстеледі.

Бұл өнімділік мәселесінің түбегейлі шешімі, өйткені біз деректерге қажетсіз есептеулерсіз қол жеткіземіз.

3) Көріністің материалдануы

Өкінішке орай, в LINQ сұраулары Уақытша кестелерді, CTE және кесте айнымалыларын тікелей пайдалану мүмкін емес.

Дегенмен, бұл жағдайды оңтайландырудың тағы бір жолы бар - индекстелген көріністер.

Шарттар тобы (жоғарыдағы мысалдан) Жабық = 0 ЖӘНЕ Болдырылды = 0 ЖӘНЕ Қосылған = 0 (немесе басқа ұқсас шарттар жиынтығы) үлкен жиыннан деректердің шағын бөлігін кэштеу, оларды индекстелген көріністе пайдаланудың жақсы нұсқасы болады.

Бірақ көріністі жүзеге асыру кезінде бірқатар шектеулер бар:

бағыныңқы сұрауларды, сөйлемдерді қолдану БАР пайдалану арқылы ауыстырылуы керек ЖОЛ
сөйлемдерді қолдана алмайсыз СОҢҒЫ, БАРЛЫҚ, ЕРЕКШЕЛІК, ҚАТЫСТЫРУ
Кесте кеңестерін және сөйлемдерді пайдалана алмайсыз Опция
циклдармен жұмыс істеу мүмкіндігі жоқ
Әр түрлі кестелердегі деректерді бір көріністе көрсету мүмкін емес

Индекстелген көріністі пайдаланудың нақты пайдасы оны нақты индекстеу арқылы ғана қол жеткізуге болатынын есте ұстаған жөн.

Бірақ көріністі шақырған кезде бұл индекстер пайдаланылмауы мүмкін және оларды анық пайдалану үшін сіз көрсетуіңіз керек WITH(КЕҢЕЙУ).

Бастап LINQ сұраулары Кестелік кеңестерді анықтау мүмкін емес, сондықтан басқа ұсынуды – келесі пішіндегі «ораманы» жасау керек:

CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Кесте функцияларын қолдану

Жиі кіреді LINQ сұраулары Күрделі құрылымы бар көріністерді пайдаланатын ішкі сұраулардың немесе блоктардың үлкен блоктары өте күрделі және оңтайлы емес орындау құрылымы бар соңғы сұрауды құрайды.

Кесте функцияларын пайдаланудың негізгі артықшылықтары LINQ сұраулары:

Көріністер жағдайындағы сияқты объект ретінде пайдалану және көрсету мүмкіндігі, бірақ енгізу параметрлерінің жинағын беруге болады:
FROM FUNCTION (@param1, @param2...)
Нәтижесінде икемді деректер таңдауына қол жеткізуге болады
Кесте функциясын пайдалану жағдайында жоғарыда сипатталған индекстелген көріністер жағдайындағыдай күшті шектеулер жоқ:
1. Кесте бойынша кеңестер:
  через LINQ Сұрау кезінде қандай индекстерді пайдалану керектігін және деректерді оқшаулау деңгейін анықтай алмайсыз.
  Бірақ бұл функцияның мүмкіндіктері бар.
  Функцияның көмегімен сіз индекстермен және деректерді оқшаулау деңгейлерімен жұмыс істеу ережелері анықталған жеткілікті тұрақты орындау сұрау жоспарына қол жеткізе аласыз.
2. Функцияны пайдалану индекстелген көріністермен салыстырғанда мыналарды алуға мүмкіндік береді:
  - күрделі деректерді іріктеу логикасы (тіпті циклдарды қолдану арқылы)
  - көптеген әртүрлі кестелерден деректерді алу
  - пайдалану СОҢҒЫ и БАР
Ұсыныс Опция параллельді бақылауды қамтамасыз ету қажет болғанда өте пайдалы ОПЦИЯ(MAXDOP N), сұранысты орындау жоспарының реті. Мысалы:
- сұрау жоспарын мәжбүрлеп қайта жасауды көрсетуге болады ОПЦИЯ (қайта құрастыру)
- сұрау жоспарын сұрауда көрсетілген біріктіру ретін пайдалану үшін мәжбүрлеу керектігін көрсетуге болады ОПЦИЯ (ТӘРТІП БЕРУ)
туралы толығырақ Опция сипатталған осында.
Ең тар және ең қажетті деректер бөлігін пайдалану:
Үлкен деректер жиынын кэштерде сақтаудың қажеті жоқ (индекстелген көріністер сияқты), олардан әлі де деректерді параметр бойынша сүзу қажет.
Мысалы, сүзгісі бар кесте бар ҚАЙДА үш өріс қолданылады (a, b, c).

Шартты түрде барлық сұраныстардың тұрақты шарты болады a = 0 және b = 0.

Дегенмен, өріс үшін сұраныс c көбірек айнымалы.

Шартты болсын a = 0 және b = 0 Бұл шынымен де мыңдаған жазбаларға қажетті нәтиже жинағын шектеуге көмектеседі, бірақ шарт қосулы с таңдауды жүз жазбаға дейін тарылтады.

Мұнда кесте функциясы жақсырақ нұсқа болуы мүмкін.

Сондай-ақ, кесте функциясы орындалу уақытында болжамды және сәйкес келеді.

мысалдар

Мысал ретінде Questions дерекқорын пайдаланып іске асыру мысалын қарастырайық.

Өтініш бар ТАҢДАУ, ол бірнеше кестелерді біріктіреді және бір көріністі (OperativeQuestions) пайдаланады, онда мүшелік электрондық пошта арқылы тексеріледі (арқылы). БАР) «Жедел сұрақтарға»:

№1 сұрау

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL) 
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));

Көрініс өте күрделі құрылымға ие: оның ішкі сұрау біріктірулері бар және сұрыптауды пайдаланады DISTINCT, бұл жалпы алғанда жеткілікті ресурстарды қажет ететін операция.

OperativeQuestions үлгісі он мыңға жуық жазбаны құрайды.

Бұл сұраудағы негізгі мәселе сыртқы сұраудағы жазбалар үшін ішкі ішкі сұрау [OperativeQuestions] көрінісінде орындалады, ол [Электрондық пошта] = @p__linq__0 үшін шығыс таңдауын шектейміз (арқылы БАР) жүздеген жазбаларға дейін.

Ішкі сұрау [Email] = @p__linq__0 арқылы жазбаларды бір рет есептеуі керек сияқты көрінуі мүмкін, содан кейін бұл бірнеше жүз жазбалар сұрақтармен идентификатор арқылы қосылуы керек және сұрау жылдам болады.

Шындығында, барлық кестелердің бірізді байланысы бар: Id сұрақтарының OperativeQuestions ішінен идентификаторға сәйкестігін тексеру және электрондық пошта арқылы сүзу.

Шын мәнінде, сұрау барлық он мыңдаған OperativeQuestions жазбаларымен жұмыс істейді, бірақ электрондық пошта арқылы тек қызықты деректер қажет.

Оперативті сұрақтар мәтінін қарау:

№2 сұрау

 
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM            [dbo].Questions AS Q INNER JOIN
                         [dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id 
OUTER APPLY
                     (SELECT   1 AS HasNoObjects
                      WHERE   NOT EXISTS
                                    (SELECT   1
                                     FROM     [dbo].ObjectUserAccesses AS BOU
                                     WHERE   BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
                         [dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE        CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL 
AND (BO.HasNoObjects = 1 OR
              EXISTS (SELECT   1
                           FROM   [dbo].ObjectUserAccesses AS BOU INNER JOIN
                                      [dbo].ObjectQuestions AS QBO 
                                                  ON QBO.[Object_Id] =BOU.ObjectId
                               WHERE  BOU.ProcessUserAccessId = BPU.Id 
                               AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));

DbContext ішіндегі бастапқы көріністі салыстыру (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}

Бастапқы LINQ сұрауы

var businessObjectsData = await context
    .OperativeQuestions
    .Where(x => x.Email == Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

Бұл нақты жағдайда біз бұл мәселені инфрақұрылымдық өзгерістерсіз, дайын нәтижелері бар бөлек кестені («Белсенді сұраулар») енгізбей шешуді қарастырамыз, бұл оны деректермен толтыру және оны жаңартып отыру механизмін қажет етеді. .

Бұл жақсы шешім болса да, бұл мәселені оңтайландырудың тағы бір нұсқасы бар.

Негізгі мақсат - OperativeQuestions көрінісінен [Электрондық пошта] = @p__linq__0 арқылы жазбаларды кэштеу.

Дерекқорға [dbo].[OperativeQuestionsUserMail] кесте функциясын енгізіңіз.

Электрондық поштаны енгізу параметрі ретінде жіберу арқылы біз мәндер кестесін қайтарамыз:

№3 сұрау


CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
    @Email  nvarchar(4000)
)
RETURNS
@tbl TABLE
(
    [Id]           uniqueidentifier,
    [Email]      nvarchar(4000)
)
AS
BEGIN
        INSERT INTO @tbl ([Id], [Email])
        SELECT Id, @Email
        FROM [OperativeQuestions]  AS [x] WHERE [x].[Email] = @Email;
     
    RETURN;
END

Бұл алдын ала анықталған деректер құрылымы бар мәндер кестесін қайтарады.

OperativeQuestionsUserMail сұрауларының оңтайлы болуы және оңтайлы сұрау жоспарлары болуы үшін қатаң құрылым қажет емес, КЕСТЕН ҚАЙТАРУ РЕТІНДЕ...

Бұл жағдайда қажетті 1-сұрау 4-сұранысқа түрлендіріледі:

№4 сұрау

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
    SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);

DbContext ішіндегі көріністер мен функцияларды салыстыру (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

Соңғы LINQ сұрауы

var businessObjectsData = await context
    .OperativeQuestions
    .GetByUserEmail(Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

Орындау уақытының реті 200-800 мс, 2-20 мс және т.б. дейін төмендеді, яғни ондаған есе жылдамырақ.

Егер біз оны орташа есептесек, 350 мс орнына 8 мс.

Айқын артықшылықтардан біз де аламыз:

оқу жүктемесінің жалпы төмендеуі,
блоктау ықтималдығының айтарлықтай төмендеуі
орташа блоктау уақытын қолайлы мәндерге дейін қысқарту

қорытынды

Дерекқор шақыруларын оңтайландыру және дәл баптау MS SQL через LINQ шешуге болатын мәселе болып табылады.

Бұл жұмыста ұқыптылық пен жүйелілік өте маңызды.

Процестің басында:

сұрау жұмыс істейтін деректерді тексеру қажет (мәндер, таңдалған деректер түрлері)
осы деректерді дұрыс индекстеуді жүзеге асырыңыз
кестелер арасындағы қосылу шарттарының дұрыстығын тексеру

Оңтайландырудың келесі итерациясы мынаны көрсетеді:

сұраныстың негізі болып табылады және негізгі сұрау сүзгісін анықтайды
ұқсас сұрау блоктарын қайталау және шарттардың қиылысуын талдау
SSMS немесе басқа графикалық интерфейсте SQL Server өзін оңтайландырады SQL сұрауы (аралық деректерді сақтау орнын бөлу, осы жадты пайдаланып нәтиже сұрауын құру (бірнеше болуы мүмкін))
соңғы кезеңде нәтижені негізге ала отырып SQL сұрауы, құрылым қайта құрылуда LINQ сұрауы

Нәтижесінде LINQ сұрауы анықталған оңтайлыға құрылымы бойынша бірдей болуы керек SQL сұрауы 3-тармақтан.

Алғыс

Әріптестерге көп рахмет jobgemws и alex_ozr компаниядан Фортис осы материалды дайындауға көмек үшін.

Ақпарат көзі: www.habr.com

C#.NET жүйесінде LINQ сұрауларын оңтайландыру әдістері