🥇Optimalisatiemethoden voor LINQ-query's in C#.NET

Inleiding

In dit artikel Er zijn verschillende optimalisatiemethoden overwogen LINQ-query's.
Hier presenteren we enkele andere benaderingen voor het optimaliseren van de code, die verband houden met LINQ-query's.

Het is bekend dat LINQ(Language-Integrated Query) is een eenvoudige en handige querytaal voor gegevensbronnen.

A LINQ to SQL is een technologie voor gegevensaccessie binnen databasesystemen. Dit is een krachtig hulpmiddel voor gegevensbeheer, waarbij via een declaratieve taal query's worden geconstrueerd die vervolgens worden omgezet in SQL-query's en naar de database server worden verzonden voor uitvoering. In ons geval begrijpen we onder databasesysteem MS SQL Server.

Maar, LINQ-query's worden niet omgezet in optimaal geschreven SQL-query's, die een ervaren DBA met alle optimalisatie nuances zou kunnen schrijven. SQL-query's:

optimale verbindingen (JOIN) en het filteren van resultaten (WAAR)
er zijn diverse nuances bij het gebruik van verbindingen en groepsvoorwaarden.
Er zijn veel variaties in het vervangen van voorwaarden IN en een werkende opdracht krijgen. EXISTSen NOT IN, naar EXISTS
tussenopslag van resultaten via tijdelijke tabellen, CTE's, tabelvariabelen
het gebruik van de clausule (OPTION) met specificaties en tabelhint WITH (…)
het gebruik van indexeerbare weergaven, als een van de middelen om overtollige gegevenslezingen tijdens selecties te vermijden.

De belangrijkste knelpunten in de prestatie van de resulterende SQL-query's bij compilatie LINQ-query's zijn:

de consolidatie van het gehele gegevensselectiemechanisme in één query
dubbelingen van identieke codeblokken, wat uiteindelijk leidt tot meervoudige onnodige gegevenslezingen
groepen van samengestelde voorwaarden (logische 'en' en 'of') — EN en OR, die samenkomen in complexe voorwaarden, leidt ertoe dat de optimizer, hoewel er geschikte niet-geclusterde indexen zijn op de benodigde velden, uiteindelijk toch begint met een scan over de clusterindex (INDEX SCAN) op de groepen voorwaarden.
heftige genestelde subquery's maken het zeer problematisch om SQL-instructies en het analyseplan van de queries van ontwikkelaars en DBA

Optimalisatiemethoden

Laten we nu overgaan naar de optimalisatiemethoden.

1) Extra indexering

Het is het beste om filters op de belangrijkste selectietabellen te bekijken, aangezien de hele query vaak om één of twee belangrijke tabellen (aanvragen-mensen-operaties) en een standaardset voorwaarden (IsClosed, Canceled, Enabled, Status) is opgebouwd. Het is belangrijk om voor de ontdekte selecties bijbehorende indexen te maken.

Deze oplossing is zinvol wanneer de selectie op deze velden de terug te geven set van de query aanzienlijk beperkt.

Bijvoorbeeld, we hebben 500.000 aanvragen. Echter, er zijn nog maar 2000 actieve aanvragen. Dan zal de correct gekozen index ons bevrijden van INDEX SCAN de grote tabel en ons in staat stellen snel gegevens door een niet-geclusterd index op te halen.

Een gebrek aan indexen kan ook worden vastgesteld via hints in de uitvoeringsplannen of door het verzamelen van statistieken van systeemweergaven. MS SQL Server:

Alle gegevens van de weergaven bevatten informatie over ontbrekende indexen, met uitzondering van ruimtelijke indexen.

Echter, indexen en caching zijn vaak methoden om de gevolgen van slecht geschreven LINQ-query's en SQL-query's.

Zoals de harde praktijk van het leven aantoont, is het voor bedrijven vaak belangrijk om bedrijfsfunctionaliteit binnen bepaalde deadlines te implementeren. Daarom worden zware queries vaak naar de achtergrond verschoven met caching.

Dit is gedeeltelijk gerechtvaardigd, aangezien de gebruiker niet altijd de meest actuele gegevens nodig heeft en er een aanvaardbaar responstijdniveau van de gebruikersinterface is.

Deze benadering maakt het mogelijk om bedrijfsverzoeken aan te pakken, maar verlaagt uiteindelijk de prestaties van het informatiesysteem, simpelweg door het uitstellen van probleemoplossingen.

Daarnaast is het belangrijk om te onthouden dat, tijdens het zoeken naar de benodigde nieuwe indexen, de voorstellen MS SQL voor optimalisatie mogelijk incorrect kunnen zijn, onder andere onder de volgende voorwaarden:

als er al indexen bestaan met een vergelijkbare set velden
als velden in de tabel niet geïndexeerd kunnen worden vanwege indexeringsbeperkingen (hierover wordt uitgebreider beschreven) hier).

2) Attributen samenvoegen tot één nieuw attribuut

Soms kunnen bepaalde velden uit één tabel, waarop een groep voorwaarden berust, worden vervangen door het inbrengen van één nieuw veld.

Dit is vooral relevant voor statusvelden, die meestal van het type boolean of integer zijn.

Voorbeeld:

IsClosed = 0 EN Canceled = 0 EN Enabled = 0 wordt vervangen door Status = 1.

Hier wordt een geheel getal attribuut Status ingevoerd, dat wordt gegarandeerd door deze statussen in de tabel in te vullen. Vervolgens wordt deze nieuwe attribuut geïndexeerd.

Dit is een fundamentele oplossing voor het prestatieprobleem, want we vragen data op zonder overtollige berekeningen.

3) Materialisatie van de weergave

Helaas, in LINQ-query's kunnen tijdelijke tabellen, CTE's en tabelvariabelen niet direct worden gebruikt.

Echter, er is nog een andere manier om te optimaliseren in dit geval - dat zijn indexeerbare weergaven.

De groep voorwaarden (uit het bovenstaande voorbeeld) IsClosed = 0 EN Canceled = 0 EN Enabled = 0 (of een set van andere soortgelijke voorwaarden) wordt een goede optie om ze in een indexeerbare weergave te gebruiken, waarbij een klein gegevensdeel van een grote hoeveelheid wordt gecached.

Maar er zijn een aantal beperkingen bij het materialiseren van een weergave:

onderzoeksopdrachten, uitspraken EXISTS moeten worden vervangen door het gebruik van JOIN
kunnen geen uitspraken worden gebruikt. UNION, UNION ALL, UITZONDERING, INTERSECT
kunnen geen tabelhint en uitspraken worden gebruikt. OPTION
er is geen mogelijkheid om met lussen te werken.
het is onmogelijk om gegevens uit verschillende tabellen in één weergave te tonen.

Het is belangrijk om te onthouden dat de echte voordelen van het gebruik van een indexeerbare weergave eigenlijk alleen kunnen worden verkregen bij het indexeren ervan.

Maar bij het aanroepen van de weergave kunnen deze indices mogelijk niet worden gebruikt, en om ze expliciet te gebruiken, moet men opgeven MET (NOEXPAND).

Aangezien in LINQ-query's tabelhints niet kunnen worden gedefinieerd, moet er een andere weergave worden gemaakt - een 'wrapper' van de volgende soort:

CREATE VIEW NAAM_weergave AS SELECT * FROM MAT_VIEW MET (NOEXPAND);

4) Gebruik van tabel functies

Vaak vormen in LINQ-query's grote blokken subquery's of blokken die gebruikmaken van weergaven met een complexe structuur, een uiteindelijke query met een zeer complexe en niet optimale uitvoeringsstructuur.

De belangrijkste voordelen van het gebruik van tabel functies in LINQ-query's:

De mogelijkheid, net als bij weergaven, om te gebruiken en aan te geven als object, maar men kan een set van invoerparameters doorgeven:
FROM FUNCTION(@param1, @param2 …)
uiteindelijk kan men flexibele gegevensselectie bereiken.
Bij het gebruik van een tabel functie zijn er niet zulke strikte beperkingen als bij de eerder beschreven indexeerbare weergaven:
1. Tabel hints:
  door LINQ Het is niet mogelijk om aan te geven welke indexen moeten worden gebruikt en het niveau van gegevensisolatie bij de aanvraag te bepalen.
  Maar in de functie zijn deze mogelijkheden er.
  Met de functie kan een vrij constant uitvoeringsplan worden bereikt, waarin de regels voor het omgaan met indexen en de niveaus van gegevensisolatie zijn gedefinieerd.
2. Het gebruik van de functie stelt, in vergelijking met indexeerbare weergaven, in staat om:
  - een complexe logica voor gegevensselectie (zelfs tot het gebruik van loops)
  - gegevensselecties uit verschillende tabellen
  - usage UNION en EXISTS
Het voorstel OPTION is zeer nuttig wanneer we parallelisme moeten beheren. OPTION(MAXDOP N), de volgorde van het uitvoeringsplan. Bijvoorbeeld:
- je kunt een gedwongen wederopbouw van het uitvoeringsplan aangeven. OPTION (RECOMPILE)
- je kunt aangeven dat het noodzakelijk is om de volgorde van de join, zoals vermeld in de aanvraag, gedwongen te laten gebruiken door het uitvoeringsplan. OPTION (FORCE ORDER)
Meer details over OPTION is beschreven. hier.
Het gebruik van de smalste en vereiste gegevenssnede:
Er is geen noodzaak om grote datasets in caches te houden (zoals bij indexeerbare weergaven) waaruit je vervolgens de gegevens nog moet filteren op parameter.
Bijvoorbeeld, er is een tabel waarvan het filter WAAR drie velden gebruikt (a, b, c) Voor alle aanvragen is er een constante voorwaarde..
a = 0 en b = 0. Echter, de aanvraag voor het veld.
is meer variabel. c Laten we aannemen dat de voorwaarde
ons inderdaad helpt om de vereiste set te beperken tot duizenden records, maar de voorwaarde op Echter, de aanvraag voor het veld beperkt onze selectie tot honderden records. met Hier kan een tabel-functie een betere optie blijken te zijn.
Bovendien is een tabel-functie meer voorspelbaar en consistenter qua uitvoeringstijd.
Laten we een implementatievoorbeeld bekijken aan de hand van de database Questions.

Voorbeelden

Er is een aanvraag

, die meerdere tabellen verbindt en gebruik maakt van één weergave (OperativeQuestions), waarin de verbondenheid via email (door SELECT) met "Actieve aanvragen" ([OperativeQuestions]) wordt gecontroleerd: EXISTSAanvraag nr. 1

Verzoek nr. 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])
));

De weergave heeft een vrij complexe structuur: er zijn subqueryverbindingen en sortering. UNIEK, wat over het algemeen een behoorlijk resource-intensieve operatie is.

De selectie uit OperativeQuestions omvat ongeveer tienduizend records.

Het belangrijkste probleem met deze query is dat voor records uit de externe query een interne subquery op de weergave [OperativeQuestions] wordt uitgevoerd, die voor [Email] = @p__linq__0 de uitvoer van de geselecteerde gegevens moet beperken (via EXISTS) tot enkele honderden records.

Het lijkt misschien dat de subquery één keer de records voor [Email] = @p__linq__0 moet berekenen, en vervolgens deze paar honderd records moeten worden samengevoegd op basis van Id met Questions, en de query snel zal zijn.

In werkelijkheid vindt echter een opeenvolgende verbinding van alle tabellen plaats: zowel het controleren van de overeenstemming van Id Questions met Id uit OperativeQuestions als het filteren op Email.

In wezen werkt de query met alle tientallen duizenden records van OperativeQuestions, terwijl alleen de gewenste gegevens op basis van Email nodig zijn.

De tekst van de weergave OperativeQuestions:

Query Nr. 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));

De oorspronkelijke mapping van de weergave in DbContext (EF Core 2)

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

Oorspronkelijke LINQ-query

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

In dit specifieke geval wordt een oplossing voor dit probleem overwogen zonder infrastructuurveranderingen, zonder een aparte tabel met kant-en-klare resultaten (“Actieve vragen”) waarvoor een mechanisme voor vullen en bijhouden van gegevens nodig zou zijn.

Hoewel dit een goede oplossing is, is er ook een andere optie voor het optimaliseren van deze taak.

Het belangrijkste doel is om registraties te cachen op [Email] = @p__linq__0 vanuit de view OperativeQuestions.

We introduceren de tabelfunctie [dbo].[OperativeQuestionsUserMail] in de database.

Door Email als invoerparameter door te geven, ontvangen we een tabel met waarden terug:

Query Nr. 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

Hier wordt een tabel met waarden teruggegeven met een vooraf gedefinieerde datastructuur.

Om de queries naar OperativeQuestionsUserMail optimaal te laten zijn, met optimale queryplannen, is een strikte structuur nodig, en niet RETURNS TABLE AS RETURN…

In dit geval wordt de gezochte Query 1 omgevormd tot Query 4:

Query Nr. 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]);

Mapping van de view en functie in 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})");
}

Eindresultaat LINQ-query

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

De uitvoeringstijd is verlaagd van 200-800 ms naar 2-20 ms, en zo verder, dat is dus tientallen keren sneller.

Gemiddeld genomen kregen we in plaats van 350 ms 8 ms.

Een van de duidelijke voordelen is ook:

algemene afname van de leeslast,
betekenisvolle vermindering van de kans op blokkeringen
vermindering van de gemiddelde blokkeertijd tot acceptabele waarden

Uitslag

Optimalisatie en finetuning van database-aanroepen MS SQL door LINQ is een taak die opgelost kan worden.

In dit werk zijn nauwkeurigheid en volgorde zeer belangrijk.

Aan het begin van het proces:

moet je de gegevens controleren waarmee de query werkt (waarden, geselecteerde datatype)
deze gegevens correct indexeren
de juistheid van de verbindingsvoorwaarden tussen tabellen controleren

Bij de volgende iteratie van optimalisatie worden geïdentificeerd:

de basis van de query en de belangrijkste filter van de query bepaald
herhalende vergelijkbare blokken van de query en de overlap van voorwaarden geanalyseerd
in SSMS of een andere GUI voor SQL Server de zelf geoptimaliseerd SQL-query (extractie van een tussentijdse gegevensopslag, bouwen van de resultaatquery met behulp van deze opslag (meerdere kunnen zijn))
in de laatste fase, uitgaande van het resultaat SQL-query, wordt de structuur opnieuw opgebouwd van de LINQ-query

Uiteindelijk moet de verkregen LINQ-query qua structuur identiek zijn aan de geconstateerde optimale SQL-query uit punt 3.