ProHoster > Blog > Administratioun > Methoden fir Optimisatioun vun LINQ Ufroen am C#.NET

Methoden fir Optimisatioun vun LINQ Ufroen am C#.NET

Aféierung

В dësen Artikel e puer Optimisatiounsmethoden goufen berücksichtegt LINQ Ufroen.
Hei presentéiere mir e puer méi Approche fir Code Optimiséierung am Zesummenhang mat LINQ Ufroen.

Bekannt LINQ(Language-Integrated Query) ass eng einfach a praktesch Sprooch fir eng Datequell ze froen.

А LINQ zu SQL ass eng Technologie fir Zougang zu Daten an engem DBMS. Dëst ass e mächtegt Tool fir mat Daten ze schaffen, wou Ufroen duerch eng deklarativ Sprooch konstruéiert ginn, déi dann an ëmgewandelt gëtt SQL Ufroen Plattform an op den Datebankserver fir Ausféierung geschéckt. An eisem Fall menge mir mat DBMS MS SQL Server.

Awer, LINQ Ufroen sinn net an optimal geschriwen ëmgerechent SQL Ufroen, déi en erfuerene DBA mat allen Nuancen vun der Optimisatioun schreiwen konnt SQL Ufroen:

  1. optimal Verbindungen (JOIN) a filtert d'Resultater (WOU)
  2. vill Nuancen am Gebrauch vun Verbindungen a Grupp Konditiounen
  3. vill Variatiounen an ersetzen Konditiounen IN op EXISTERENи NET IN, <> op EXISTEREN
  4. Tëschenzäit Caching vu Resultater iwwer temporär Dëscher, CTE, Tabellvariablen
  5. Benotzung vum Saz (OPTION) mat Instruktiounen an Dësch Hiweiser MAT (...)
  6. indexéiert Usiichten als ee vun de Mëttele benotzt fir iwwerflësseg Dateliesungen während Selektiounen lass ze ginn

D'Haaptrei Leeschtung Flaschenhals vun der doraus resultéierend SQL Ufroen beim Zesummesetzung LINQ Ufroen sinn:

  1. Konsolidéierung vum ganzen Dateselektiounsmechanismus an enger Ufro
  2. identesch Blocke vu Code duplizéieren, wat schlussendlech zu multiple onnéideg Donnéeën liesen féiert
  3. Gruppe vu Multi-Komponentebedéngungen (logesch "an" an "oder") - AN и OR, a komplexe Bedéngungen ze kombinéieren, féiert zu der Tatsaach datt den Optimizer, mat passenden net-clusteréierten Indexen fir déi néideg Felder, schlussendlech ufänkt géint de clustered Index ze scannen (INDEX SCAN) no Gruppe vu Bedéngungen
  4. déif Nesting vun subqueries mécht Parsing ganz problematesch SQL Aussoen an Analyse vun der Ufro plangen op der Säit vun Entwéckler an DBA

Optimiséierungsmethoden

Loosst eis elo direkt op Optimisatiounsmethoden plënneren.

1) Zousätzlech Indexéierung

Et ass am beschten Filteren op den Haaptauswiel Dëscher ze berücksichtegen, well ganz dacks ass d'ganz Ufro ronderëm een ​​oder zwee Haapttabellen gebaut (Applikatiounen-Leit-Operatiounen) a mat enger Standardset vu Konditiounen (IsClosed, Annuléiert, Enabled, Status). Et ass wichteg entspriechend Indizes fir déi identifizéiert Proben ze kreéieren.

Dës Léisung mécht sënnvoll wann Dir dës Felder auswielt, limitéiert de zréckgesate Set op d'Ufro bedeitend.

Zum Beispill hu mir 500000 Uwendungen. Wéi och ëmmer, et ginn nëmmen 2000 aktiv Uwendungen. Da wäert e korrekt ausgewielten Index eis retten INDEX SCAN op engem groussen Dësch a wäert erlaben Iech séier Daten duerch eng Net-clustered Index ze wielen.

Och de Mangel un Indexen kann duerch Ufroe identifizéiert ginn fir Ufro Pläng ze analyséieren oder System View Statistiken ze sammelen MS SQL Server:

  1. sys.dm_db_missing_index_groups
  2. sys.dm_db_missing_index_group_stats
  3. sys.dm_db_missing_index_details

All Vue Daten enthält Informatiounen iwwer vermësst Index, mat Ausnam vun raimlech Index.

Wéi och ëmmer, Indexen an Caching sinn dacks Methoden fir d'Konsequenze vu schlecht geschriwen ze bekämpfen LINQ Ufroen и SQL Ufroen.

Wéi déi haart Praxis vum Liewen weist, ass et dacks wichteg fir e Geschäft Geschäftsfeatures no gewësse Frist ze implementéieren. An dofir ginn schwéier Ufroen dacks mam Caching op den Hannergrond transferéiert.

Dëst ass deelweis gerechtfäerdegt, well de Benotzer net ëmmer déi lescht Donnéeën brauch an et ass en akzeptablen Niveau vun der Reaktiounsfäegkeet vun der User-Interface.

Dës Approche erlaabt d'Geschäftsbedürfnisser ze léisen, awer schlussendlech reduzéiert d'Leeschtung vum Informatiounssystem duerch einfach Léisunge fir Probleemer ze verzögeren.

Et ass och derwäert ze erënneren datt am Prozess vun der Sich no den néidegen Indexen fir ze addéieren, Suggestiounen MSSQL Optimisatioun kann falsch sinn, och ënner de folgende Konditiounen:

  1. wann et schonn Indizes mat enger ähnlecher Formatioun vun Felder
  2. wann d'Felder an der Tabell net indexéiert kënne ginn wéinst Indexéierungsbeschränkungen (méi detailléiert beschriwwen hei).

2) Fusioun Attributer an een neit Attribut

Heiansdo kënnen e puer Felder aus engem Dësch, déi als Basis fir eng Grupp vu Bedéngungen déngen, ersat ginn andeems en neit Feld agefouert gëtt.

Dëst ass virun allem wouer fir Status Felder, déi normalerweis entweder Bit oder Ganzt an Typ sinn.

Beispill:

IsClosed = 0 AN Annuléiert = 0 AN Aktivéiert = 0 gëtt ersat duerch Status = 1.

Dëst ass wou den Integer Status Attribut agefouert gëtt fir sécherzestellen datt dës Statusen an der Tabell populéiert sinn. Als nächst gëtt dësen neien Attribut indexéiert.

Dëst ass eng fundamental Léisung fir d'Leeschtungsproblem, well Mir Zougang zu Daten ouni onnéideg Berechnungen.

3) Materialiséierung vun der Vue

Leider, an LINQ Ufroen Temporär Dëscher, CTEs an Tabellvariablen kënnen net direkt benotzt ginn.

Wéi och ëmmer, et gëtt eng aner Manéier fir dëse Fall ze optimiséieren - indexéiert Meenungen.

Konditiounsgrupp (vum Beispill hei uewen) IsClosed = 0 AN Annuléiert = 0 AN Aktivéiert = 0 (oder e Set vun aneren ähnlechen Konditiounen) gëtt eng gutt Optioun fir se an enger indexéierter Vue ze benotzen, e klengen Deel vun Daten aus engem grousse Set cache.

Awer et ginn eng Zuel vu Restriktiounen wann Dir eng Vue materialiséiert:

  1. Benotzung vun subqueries, Klauselen EXISTEREN soll duerch benotzen ersat ginn JOIN
  2. Dir kënnt keng Sätz benotzen UNIOUN, UNION ALL, Ausnam, INTERSEKTIOUN
  3. Dir kënnt net Dësch Hiweiser a Klauselen benotzen OPTION
  4. keng Méiglechkeet mat Zyklen ze schaffen
  5. Et ass onméiglech Daten an enger Vue aus verschiddenen Dëscher ze weisen

Et ass wichteg ze erënneren datt de richtege Virdeel vun der Benotzung vun enger indexéierter Vue nëmmen erreecht ka ginn andeems se se tatsächlech indexéiert.

Awer wann Dir eng Vue rufft, kënnen dës Indexer net benotzt ginn, a fir se explizit ze benotzen, musst Dir spezifizéieren MAT (NOEXPAND).

Zënter an LINQ Ufroen Et ass onméiglech fir Dësch Hiweiser ze definéieren, also musst Dir eng aner Representatioun erstellen - e "Wrapper" vun der folgender Form:

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

4) Benotzt Dësch Funktiounen

Oft an LINQ Ufroen Grouss Blocke vu Subqueries oder Blocks mat Vue mat enger komplexer Struktur bilden eng final Ufro mat enger ganz komplexer an suboptimaler Ausféierungsstruktur.

Schlëssel Virdeeler vun Benotzung vun Dësch Funktiounen an LINQ Ufroen:

  1. D'Kapazitéit, wéi am Fall vu Meenungen, fir als Objet ze benotzen a spezifizéiert ze ginn, awer Dir kënnt eng Rei vun Inputparameter passéieren:
    VUN FUNCTION(@param1, @param2 ...)
    Als Resultat kann flexibel Dateprobe erreecht ginn
  2. Am Fall vun der Benotzung vun enger Tabellfunktioun ginn et keng sou staark Restriktiounen wéi am Fall vun indexéierten Usiichten hei uewen beschriwwen:
    1. Dësch Hiweiser:
      duerch LINQ Dir kënnt net spezifizéieren wéi eng Indizes solle benotzt ginn a bestëmmen den Dateisolatiounsniveau beim Ufro.
      Awer d'Funktioun huet dës Fäegkeeten.
      Mat der Funktioun kënnt Dir e zimmlech konstante Ausféierungsufroplang erreechen, wou d'Regele fir mat Indexen an Datenisolatiounsniveauen ze schaffen definéiert sinn
    2. D'Benotzung vun der Funktioun erlaabt, am Verglach mat indexéierten Usiichten, ze kréien:
      • komplex Dateprobelogik (och mat Loops)
      • Daten aus ville verschiddenen Dëscher sichen
      • der Notzung vun UNIOUN и EXISTEREN

  3. Offer OPTION ganz nëtzlech wa mir d'Konkurrenzkontrolle mussen ubidden OPTION (MAXDOP N), d'Uerdnung vum Ufro-Ausféierungsplang. Zum Beispill:
    • Dir kënnt eng forcéiert Neischafung vum Ufroplang uginn OPTION (RECOMPILE)
    • Dir kënnt uginn ob den Ufroplang gezwongen ass fir d'Joint-Uerdnung ze benotzen, déi an der Ufro spezifizéiert ass OPTION (FORCE ORDER)

    Méi Detailer iwwer OPTION beschriwwen hei.

  4. Mat der schmuelsten an am meeschte erfuerderlechen Dateschnitt:
    Et ass kee Besoin fir grouss Datesets a Cache ze späicheren (wéi dat de Fall ass mat indexéierten Usiichten), aus deenen Dir nach ëmmer d'Donnéeën no Parameter filtere musst.
    Zum Beispill gëtt et en Dësch deem säi Filter WOU dräi Felder ginn benotzt (a, b, c).

    Konventionell hunn all Ufroen e konstante Conditioun a = 0 a b = 0.

    Wéi och ëmmer, d'Demande fir den Terrain c méi variabel.

    Loosst d'Konditioun a = 0 a b = 0 Et hëlleft eis wierklech déi erfuerderlech resultéierend Set op Dausende vu Rekorder ze limitéieren, awer d'Konditioun op с schmuel d'Auswiel op honnert records.

    Hei kann d'Tabellfunktioun eng besser Optioun sinn.

    Och eng Tabellfunktioun ass méi prévisibel a konsequent an der Ausféierungszäit.

Beispiller

Loosst eis eng Beispillimplementatioun kucken mat der Froen Datebank als Beispill.

Et gëtt eng Demande beruflecher Organisatioun, déi e puer Dëscher kombinéiert an eng Vue benotzt (OperativeQuestions), an där d'Bezéiung per E-Mail gepréift gëtt (via EXISTEREN) op "Operative Froen":

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

D'Vue huet eng zimlech komplex Struktur: et huet Subquery Join a benotzt Sortéierung DISTINCT, wat am Allgemengen eng zimlech ressourceintensiv Operatioun ass.

Eng Probe vun OperativeQuestions ass ongeféier zéngdausend records.

Den Haaptproblem mat dëser Ufro ass datt fir d'Records vun der baussenzeger Ufro eng intern Ënnerquery op der [OperativeQuestions] Vue ausgefouert gëtt, wat fir [Email] = @p__linq__0 et erlaabt eis d'Ausgabauswiel ze limitéieren (via EXISTEREN) bis zu honnerte vun records.

An et kann schéngen datt d'Ënnerquery d'Records eemol duerch [E-Mail] = @p__linq__0 berechnen soll, an da sollten dës puer honnert records vun Id mat Froen verbonne sinn, an d'Ufro wäert séier sinn.

Tatsächlech gëtt et eng sequenziell Verbindung vun all Dëscher: Iwwerpréift d'Korrespondenz vun Id Froen mat Id vun OperativeQuestions, a filtert per E-Mail.

Tatsächlech funktionnéiert d'Ufro mat all Zéngdausende vun OperativeQuestions records, awer nëmmen d'Daten vun Interesse sinn iwwer E-Mail gebraucht.

Operative Questions View Text:

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

Initial View Mapping am 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");
    }
}

Éischt LINQ Ufro

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

An dësem spezifesche Fall betruechte mir eng Léisung fir dëse Problem ouni infrastrukturell Ännerungen, ouni eng separat Tabell mat fäerdege Resultater aféieren ("Aktiv Queries"), déi e Mechanismus erfuerdert fir se mat Daten auszefëllen an um neiste Stand ze halen .

Och wann dëst eng gutt Léisung ass, gëtt et eng aner Optioun fir dëse Problem ze optimiséieren.

Den Haaptzweck ass Entréen duerch [E-Mail] = @p__linq__0 aus der OperativeQuestions Vue ze cache.

D'Tabellfunktioun [dbo].[OperativeQuestionsUserMail] an d'Datebank aféieren.

Andeems Dir E-Mail als Inputparameter schéckt, kréie mir eng Tabelle vu Wäerter zréck:

Demande 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

Dëst gëtt eng Tabelle vu Wäerter mat enger virdefinéierter Datestruktur zréck.

Fir Ufroen op OperativeQuestionsUserMail optimal ze sinn an optimal Ufropläng ze hunn, ass eng strikt Struktur erfuerderlech, an net RETOURTABLE ALS RETOUR...

An dësem Fall gëtt déi erfuerderlech Query 1 an Query 4 ëmgewandelt:

Demande 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 Meenungen a Funktiounen am 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})");
}

Finale LINQ Ufro

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

D'Uerdnung vun der Ausféierungszäit ass vun 200-800 ms erofgaang, op 2-20 ms, etc., dh zéngmol méi séier.

Wa mir et méi duerchschnëttlech huelen, dann hu mir amplaz 350 ms 8 ms.

Vun den offensichtleche Virdeeler kréien mir och:

  1. allgemeng Reduktioun vun der Liesbelaaschtung,
  2. bedeitend Reduktioun vun der Wahrscheinlechkeet vu Blockéierung
  3. reduzéieren déi duerchschnëttlech Spärzäit op akzeptabel Wäerter

Konklusioun

Optimiséierung a Feinstemmung vun Datebank Uriff MSSQL duerch LINQ ass e Problem dee geléist ka ginn.

Opgepasst a Konsistenz si ganz wichteg an dëser Aarbecht.

Am Ufank vum Prozess:

  1. et ass néideg d'Donnéeën ze kontrolléieren mat deenen d'Ufro funktionnéiert (Wäerter, ausgewielten Datentypen)
  2. eng korrekt Indexéierung vun dësen Donnéeë maachen
  3. kontrolléiert d'Korrektheet vun de Verbindungsbedéngungen tëscht Dëscher

Déi nächst Optimiséierungsiteratioun verroden:

  1. Basis vun der Ufro an definéiert den Haaptfrofilter
  2. widderhuelen ähnlech Ufroblocken an analyséieren d'Kräizung vu Konditiounen
  3. an SSMS oder aner GUI fir SQL Server optimiséiert sech SQL Ufro (eng Zwëschendatenspäicherung allocéieren, déi resultéierend Ufro opbauen mat dëser Späichere (et kann e puer sinn))
  4. op der leschter Etapp, huelen als Basis déi doraus resultéierend SQL Ufro, gëtt d'Struktur nei opgebaut LINQ Ufro

Déi resultéierend LINQ Ufro soll an der Struktur identesch ginn dem identifizéierten optimal SQL Ufro vum punkt 3.

Unerkennungen

E grousse Merci un d'Kollegen jobgemws и alex_ozr vun der Firma Fortis fir Hëllef bei der Virbereedung vun dësem Material.

Source: will.com

Setzt e Commentaire