🥇Methoden voor het optimaliseren van LINQ-query's in C#.NET

Introductie

В dit artikel enkele optimalisatiemethoden werden overwogen LINQ-query's.
Hier presenteren we nog enkele benaderingen van code-optimalisatie gerelateerd aan LINQ-query's.

Het is bekend dat LINQ(Language-Integrated Query) is een eenvoudige en handige taal voor het bevragen van een gegevensbron.

А LINQ naar SQL is een technologie voor toegang tot gegevens in een DBMS. Dit is een krachtig hulpmiddel voor het werken met gegevens, waarbij zoekopdrachten worden opgebouwd via een declaratieve taal, die vervolgens wordt omgezet in SQL-query's platform en ter uitvoering naar de databaseserver verzonden. In ons geval bedoelen we met DBMS MS SQL Server.

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

optimale verbindingen (AANMELDEN) en het filteren van de resultaten (WAAR)
veel nuances in het gebruik van verbindingen en groepsvoorwaarden
veel variaties in vervangingsomstandigheden IN op BESTAATи NIET IN, <> aan BESTAAT
tussentijdse caching van resultaten via tijdelijke tabellen, CTE, tabelvariabelen
gebruik van zin (OPTIE) met instructies en tafeltips MET (...)
het gebruik van geïndexeerde weergaven als een van de manieren om overtollige gegevenslezingen tijdens selecties te verwijderen

De belangrijkste prestatieknelpunten van het resultaat SQL-query's bij het compileren LINQ-query's Ze zijn:

consolidatie van het volledige gegevensselectiemechanisme in één verzoek
het dupliceren van identieke codeblokken, wat uiteindelijk leidt tot meerdere onnodige gegevenslezingen
groepen van uit meerdere componenten bestaande voorwaarden (logisch “en” en “of”) - EN и OR, gecombineerd tot complexe omstandigheden, leidt ertoe dat de optimizer, die geschikte niet-geclusterde indexen heeft voor de noodzakelijke velden, uiteindelijk begint te scannen tegen de geclusterde index (INDEXSCAN) door groepen voorwaarden
Het diep nesten van subquery's maakt het parseren zeer problematisch SQL-instructies en analyse van het queryplan door ontwikkelaars en DBA

Optimalisatiemethoden

Laten we nu direct naar de optimalisatiemethoden gaan.

1) Aanvullende indexering

Het is het beste om filters op de hoofdselectietabellen te overwegen, omdat de hele query vaak is opgebouwd rond een of twee hoofdtabellen (applicaties-mensen-bewerkingen) en met een standaardset voorwaarden (IsClosed, Cancelled, Enabled, Status). Het is belangrijk om geschikte indices te creëren voor de geïdentificeerde monsters.

Deze oplossing is zinvol wanneer het selecteren van deze velden de geretourneerde set aanzienlijk beperkt tot de query.

We hebben bijvoorbeeld 500000 aanvragen. Er zijn echter slechts 2000 actieve applicaties. Dan zal een correct geselecteerde index ons hiervan redden INDEXSCAN op een grote tafel en stelt u in staat snel gegevens te selecteren via een niet-geclusterde index.

Het ontbreken van indexen kan ook worden geïdentificeerd via aanwijzingen voor het parseren van queryplannen of het verzamelen van systeemweergavestatistieken MS SQL Server:

Alle weergavegegevens bevatten informatie over ontbrekende indexen, met uitzondering van ruimtelijke indexen.

Indexen en caching zijn echter vaak methoden om de gevolgen van slecht geschreven teksten tegen te gaan LINQ-query's и SQL-query's.

Zoals de harde praktijk van het leven laat zien, is het vaak belangrijk voor een bedrijf om zakelijke kenmerken binnen bepaalde deadlines te implementeren. En daarom worden zware verzoeken vaak met caching naar de achtergrond overgebracht.

Dit is deels terecht, aangezien de gebruiker niet altijd de nieuwste gegevens nodig heeft en er een acceptabel niveau van responsiviteit van de gebruikersinterface is.

Deze aanpak maakt het mogelijk bedrijfsbehoeften op te lossen, maar vermindert uiteindelijk de prestaties van het informatiesysteem door simpelweg oplossingen voor problemen uit te stellen.

Het is ook de moeite waard om te onthouden dat u tijdens het zoeken naar de nodige indexen suggesties moet toevoegen MS SQL optimalisatie kan onjuist zijn, ook onder de volgende omstandigheden:

als er al indexen zijn met een vergelijkbare set velden
als de velden in de tabel niet kunnen worden geïndexeerd vanwege indexeringsbeperkingen (in meer detail beschreven hier).

2) Attributen samenvoegen tot één nieuw attribuut

Soms kunnen enkele velden uit één tabel, die als basis dienen voor een groep voorwaarden, worden vervangen door de introductie van één nieuw veld.

Dit geldt vooral voor statusvelden, die doorgaans van het type bit of geheel getal zijn.

Voorbeeld:

IsGesloten = 0 EN Geannuleerd = 0 EN Ingeschakeld = 0 is vervangen door Staat = 1.

Hier wordt het integer Status-attribuut geïntroduceerd om ervoor te zorgen dat deze statussen in de tabel worden ingevuld. Vervolgens wordt dit nieuwe attribuut geïndexeerd.

Dit is een fundamentele oplossing voor het prestatieprobleem, omdat we toegang hebben tot gegevens zonder onnodige berekeningen.

3) Materialisering van het uitzicht

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

Er is echter een andere manier om voor dit geval te optimaliseren: geïndexeerde weergaven.

Conditiegroep (uit het bovenstaande voorbeeld) IsGesloten = 0 EN Geannuleerd = 0 EN Ingeschakeld = 0 (of een reeks andere soortgelijke voorwaarden) wordt een goede optie om ze in een geïndexeerde weergave te gebruiken, waarbij een klein stukje gegevens uit een grote set in de cache wordt opgeslagen.

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

gebruik van subquery's, clausules BESTAAT moet worden vervangen door gebruik AANMELDEN
je kunt geen zinnen gebruiken UNION, UNIE ALLEN, UITZONDERING, SNIJDEN
U kunt geen tabelhints en -clausules gebruiken OPTIE
geen mogelijkheid om met cycli te werken
Het is onmogelijk om gegevens uit verschillende tabellen in één weergave weer te geven

Het is belangrijk om te onthouden dat het echte voordeel van het gebruik van een geïndexeerde weergave alleen kan worden bereikt door deze daadwerkelijk te indexeren.

Maar bij het aanroepen van een view mogen deze indexen niet worden gebruikt, en als u ze expliciet wilt gebruiken, moet u dit opgeven MET(NOEXPAND).

Omdat in LINQ-query's Het is onmogelijk om tabelhints te definiëren, dus je moet een andere representatie maken - een "wrapper" van de volgende vorm:

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

4) Tabelfuncties gebruiken

Vaak binnen LINQ-query's Grote blokken subquery's of blokken die views met een complexe structuur gebruiken, vormen een uiteindelijke query met een zeer complexe en suboptimale uitvoeringsstructuur.

Belangrijkste voordelen van het gebruik van tabelfuncties in LINQ-query's:

De mogelijkheid om, net als bij weergaven, als object te worden gebruikt en gespecificeerd, maar u kunt een reeks invoerparameters doorgeven:
VAN FUNCTIE(@param1, @param2 ...)
Als gevolg hiervan kan een flexibele gegevensbemonstering worden bereikt
In het geval van het gebruik van een tabelfunctie zijn er niet zulke sterke beperkingen als in het geval van de hierboven beschreven geïndexeerde weergaven:
1. Tabeltips:
  door LINQ U kunt bij het uitvoeren van query's niet opgeven welke indexen moeten worden gebruikt en het gegevensisolatieniveau bepalen.
  Maar de functie heeft deze mogelijkheden.
  Met de functie kunt u een redelijk constant uitvoeringsplan voor query's realiseren, waarin regels voor het werken met indexen en gegevensisolatieniveaus worden gedefinieerd
2. Met behulp van de functie kunt u, in vergelijking met geïndexeerde weergaven, het volgende verkrijgen:
  - complexe databemonsteringslogica (zelfs met behulp van lussen)
  - gegevens ophalen uit veel verschillende tabellen
  - het gebruik van UNION и BESTAAT
Voorstel OPTIE erg handig als we gelijktijdigheidscontrole moeten bieden OPTIE(MAXDOP N), de volgorde van het query-uitvoeringsplan. Bijvoorbeeld:
- u kunt een geforceerd opnieuw maken van het queryplan opgeven OPTIE (HERCOMPILEEREN)
- U kunt opgeven of het queryplan moet worden afgedwongen om de joinvolgorde te gebruiken die in de query is opgegeven OPTIE (ORDER FORCEREN)
Meer details over OPTIE beschreven hier.
Met behulp van het smalste en meest vereiste gegevenssegment:
Het is niet nodig om grote datasets in caches op te slaan (zoals het geval is bij geïndexeerde weergaven), waaruit u de gegevens nog steeds op parameter moet filteren.
Er is bijvoorbeeld een tabel waarvan filter WAAR Er worden drie velden gebruikt (a,b,c).

Conventioneel hebben alle verzoeken een constante voorwaarde a = 0 en b = 0.

Echter, het verzoek om het veld c meer variabel.

Laat de voorwaarde a = 0 en b = 0 Het helpt ons echt om de vereiste resulterende set te beperken tot duizenden records, maar de voorwaarde blijft behouden с beperkt de selectie tot honderd records.

Hier is de tabelfunctie wellicht een betere optie.

Bovendien is een tabelfunctie voorspelbaarder en consistenter in uitvoeringstijd.

Примеры

Laten we een voorbeeldimplementatie bekijken met de database Vragen als voorbeeld.

Er is een verzoek SELECT, dat meerdere tabellen combineert en één weergave gebruikt (OperativeQuestions), waarin de aansluiting per e-mail wordt gecontroleerd (via BESTAAT) naar “Operatieve vragen”:

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 nogal complexe structuur: het heeft subquery-joins en maakt gebruik van sortering DISTINCT, wat over het algemeen een tamelijk arbeidsintensieve operatie is.

Een voorbeeld van OperativeQuestions bestaat uit ongeveer tienduizend records.

Het grootste probleem met deze query is dat voor de records uit de buitenste query een interne subquery wordt uitgevoerd in de weergave [OperativeQuestions], waardoor we voor [Email] = @p__linq__0 de uitvoerselectie kunnen beperken (via BESTAAT) tot honderden records.

En het lijkt misschien dat de subquery de records één keer moet berekenen met [E-mail] = @p__linq__0, en dan moeten deze paar honderd records door Id worden verbonden met Vragen, en de query zal snel zijn.

In feite is er een sequentiële verbinding tussen alle tabellen: het controleren van de correspondentie van Id-vragen met Id uit OperativeQuestions, en filteren per e-mail.

In feite werkt het verzoek met alle tienduizenden OperativeQuestions-records, maar via e-mail zijn alleen de relevante gegevens nodig.

Operatievragen bekijken tekst:

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

Initiële weergavetoewijzing 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");
    }
}

Initiële 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 overwegen we een oplossing voor dit probleem zonder infrastructurele veranderingen, zonder de introductie van een aparte tabel met kant-en-klare resultaten (“Active Queries”), waarvoor een mechanisme nodig zou zijn om deze met gegevens te vullen en up-to-date te houden .

Hoewel dit een goede oplossing is, is er nog een andere optie om dit probleem te optimaliseren.

Het belangrijkste doel is het cachen van vermeldingen met [E-mail] = @p__linq__0 vanuit de OperativeQuestions-weergave.

Introduceer de tabelfunctie [dbo].[OperativeQuestionsUserMail] in de database.

Door e-mail als invoerparameter te verzenden, krijgen we een tabel met waarden terug:

Verzoek 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

Dit retourneert een tabel met waarden met een vooraf gedefinieerde gegevensstructuur.

Om ervoor te zorgen dat query's naar OperativeQuestionsUserMail optimaal zijn en optimale queryplannen hebben, is een strikte structuur vereist, en niet RETOURTABEL ALS RETOUR...

In dit geval wordt de vereiste Query 1 omgezet in Query 4:

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

Weergaven en functies in kaart brengen 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})");
}

Laatste 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 volgorde van de uitvoeringstijd is gedaald van 200-800 ms naar 2-20 ms, enz., d.w.z. tientallen keren sneller.

Als we het meer gemiddeld nemen, krijgen we in plaats van 350 ms 8 ms.

Van de voor de hand liggende voordelen krijgen we ook:

algemene vermindering van de leesbelasting,
aanzienlijke vermindering van de kans op blokkering
het terugbrengen van de gemiddelde blokkeertijd tot aanvaardbare waarden

Uitgang

Optimalisatie en afstemming van databaseoproepen MS SQL door LINQ is een probleem dat opgelost kan worden.

Oplettendheid en consistentie zijn erg belangrijk in dit werk.

Aan het begin van het proces:

het is noodzakelijk om de gegevens te controleren waarmee het verzoek werkt (waarden, geselecteerde gegevenstypen)
zorg voor een goede indexering van deze gegevens
controleer de juistheid van de samenvoegingsvoorwaarden tussen tabellen

De volgende optimalisatie-iteratie onthult:

basis van het verzoek en definieert het hoofdverzoekfilter
het herhalen van soortgelijke zoekblokken en het analyseren van de kruising van voorwaarden
in SSMS of andere GUI voor SQL Server optimaliseert zichzelf SQL-query (een tussenliggende gegevensopslag toewijzen, de resulterende query opbouwen met behulp van deze opslag (er kunnen er meerdere zijn))
in de laatste fase, waarbij het resultaat als basis wordt genomen SQL-query, wordt de structuur herbouwd LINQ-query

Het resultaat LINQ-query moet qua structuur identiek worden aan het geïdentificeerde optimaal SQL-query vanaf punt 3.

Dankbetuigingen

Veel dank aan collega's baangemw и alex_ozr van het bedrijf Fortis voor hulp bij het voorbereiden van dit materiaal.

Bron: www.habr.com

Methoden voor het optimaliseren van LINQ-query's in C#.NET