🥇Mètodes per optimitzar consultes LINQ en C#.NET

Introducció

В aquest article es van considerar alguns mètodes d'optimització Consultes LINQ.
Aquí també presentem alguns enfocaments més relacionats amb l'optimització de codi Consultes LINQ.

Se sap que LINQ(Language-Integrated Query) és un llenguatge senzill i convenient per consultar una font de dades.

А LINQ a SQL és una tecnologia per accedir a dades en un DBMS. Aquesta és una potent eina per treballar amb dades, on les consultes es construeixen mitjançant un llenguatge declaratiu, que després es convertirà en Consultes SQL plataforma i enviat al servidor de bases de dades per a l'execució. En el nostre cas, per DBMS ens referim MS SQL Server.

No obstant això, Consultes LINQ no es converteixen en escrits de manera òptima Consultes SQL, que un DBA experimentat podria escriure amb tots els matisos d'optimització Consultes SQL:

connexions òptimes (JOIN) i filtrant els resultats (WHERE)
molts matisos en l'ús de connexions i condicions de grup
moltes variacions en les condicions de substitució IN en EXISTEIXи NO EN, <> activat EXISTEIX
memòria cau intermèdia de resultats mitjançant taules temporals, CTE, variables de taula
ús de la frase (OPCIÓ) amb instruccions i consells de taula AMB (...)
utilitzant vistes indexades com un dels mitjans per desfer-se de les lectures de dades redundants durant les seleccions

Els principals colls d'ampolla de rendiment del resultat Consultes SQL en compilar Consultes LINQ són:

consolidació de tot el mecanisme de selecció de dades en una sol·licitud
duplicar blocs de codi idèntics, cosa que en última instància condueix a múltiples lectures de dades innecessàries
grups de condicions multicomponent ("i" i "o" lògics) - I и OR, combinant-se en condicions complexes, condueix al fet que l'optimitzador, amb índexs no agrupats adequats per als camps necessaris, finalment comença a escanejar contra l'índex agrupat (ESCANY D'ÍNDEX) per grups de condicions
La nidificació profunda de subconsultes fa que l'anàlisi sigui molt problemàtic Sentències SQL i anàlisi del pla de consultes per part dels desenvolupadors i DBA

Mètodes d’optimització

Ara passem directament als mètodes d'optimització.

1) Indexació addicional

El millor és tenir en compte els filtres a les taules de selecció principals, ja que molt sovint tota la consulta es construeix al voltant d'una o dues taules principals (aplicacions-persones-operacions) i amb un conjunt estàndard de condicions (IsClosed, Canceled, Enabled, Status). És important crear índexs adequats per a les mostres identificades.

Aquesta solució té sentit quan la selecció d'aquests camps limita significativament el conjunt retornat a la consulta.

Per exemple, tenim 500000 sol·licituds. Tanmateix, només hi ha 2000 aplicacions actives. Aleshores, un índex seleccionat correctament ens estalviarà ESCANY D'ÍNDEX en una taula gran i us permetrà seleccionar ràpidament dades mitjançant un índex no agrupat.

A més, la manca d'índexs es pot identificar mitjançant indicacions per analitzar plans de consultes o recopilar estadístiques de visualització del sistema MS SQL Server:

Totes les dades de visualització contenen informació sobre els índexs que falten, a excepció dels índexs espacials.

No obstant això, els índexs i la memòria cau són sovint mètodes per combatre les conseqüències de mal escrit Consultes LINQ и Consultes SQL.

Com mostra la dura pràctica de la vida, sovint és important que una empresa implementi les funcions empresarials en determinats terminis. I, per tant, les consultes pesades sovint es transfereixen a un segon pla amb la memòria cau.

Això està en part justificat, ja que l'usuari no sempre necessita les dades més recents i hi ha un nivell acceptable de resposta de la interfície d'usuari.

Aquest enfocament permet resoldre les necessitats del negoci, però en última instància redueix el rendiment del sistema d'informació simplement retardant les solucions als problemes.

També val la pena recordar que en el procés de cerca dels índexs necessaris per afegir, suggeriments MS SQL l'optimització pot ser incorrecta, fins i tot en les condicions següents:

si ja hi ha índexs amb un conjunt similar de camps
si els camps de la taula no es poden indexar a causa de restriccions d'indexació (descrites amb més detall aquí).

2) Fusionar atributs en un atribut nou

De vegades, alguns camps d'una taula, que serveixen de base per a un grup de condicions, es poden substituir introduint un camp nou.

Això és especialment cert per als camps d'estat, que solen ser de tipus bit o enter.

Exemple:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 és substituït per Estat = 1.

Aquí és on s'introdueix l'atribut d'estat sencer per garantir que aquests estats s'omplen a la taula. A continuació, s'indexa aquest nou atribut.

Aquesta és una solució fonamental al problema de rendiment, perquè accedim a les dades sense càlculs innecessaris.

3) Materialització de la vista

Malauradament a Consultes LINQ Les taules temporals, els CTE i les variables de taula no es poden utilitzar directament.

Tanmateix, hi ha una altra manera d'optimitzar per a aquest cas: les visualitzacions indexades.

Grup de condicions (de l'exemple anterior) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (o un conjunt d'altres condicions similars) es converteix en una bona opció per utilitzar-les en una vista indexada, guardant a la memòria cau una petita porció de dades d'un conjunt gran.

Però hi ha una sèrie de restriccions a l'hora de materialitzar una vista:

ús de subconsultes, clàusules EXISTEIX s'ha de substituir per l'ús JOIN
no pots utilitzar frases UNIÓ, UNIÓ TOTS, EXCEPTION, INTERSECTA
No podeu utilitzar suggeriments i clàusules de taula OPCIÓ
no hi ha possibilitat de treballar amb cicles
És impossible mostrar dades en una vista de taules diferents

És important recordar que el benefici real d'utilitzar una vista indexada només es pot aconseguir indexant-la.

Però quan es crida a una vista, aquests índexs poden no ser utilitzats, i per utilitzar-los de manera explícita, cal especificar AMB (NO EXPANDIR).

Des de l'any Consultes LINQ És impossible definir suggeriments de taula, de manera que heu de crear una altra representació: un "embolcall" de la forma següent:

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

4) Ús de funcions de taula

Sovint a Consultes LINQ Grans blocs de subconsultes o blocs que utilitzen vistes amb una estructura complexa formen una consulta final amb una estructura d'execució molt complexa i subòptima.

Beneficis clau de l'ús de funcions de taula a Consultes LINQ:

La capacitat, com en el cas de les vistes, de ser utilitzat i especificat com a objecte, però podeu passar un conjunt de paràmetres d'entrada:
FROM FUNCTION(@param1, @param2...)
Com a resultat, es pot aconseguir un mostreig de dades flexible
En el cas d'utilitzar una funció de taula, no hi ha restriccions tan fortes com en el cas de les vistes indexades descrites anteriorment:
1. Suggeriments de la taula:
  a través d' LINQ No podeu especificar quins índexs s'han d'utilitzar ni determinar el nivell d'aïllament de dades quan feu una consulta.
  Però la funció té aquestes capacitats.
  Amb la funció, podeu aconseguir un pla de consultes d'execució força constant, on es defineixen regles per treballar amb índexs i nivells d'aïllament de dades.
2. L'ús de la funció permet, en comparació amb les vistes indexades, obtenir:
  - lògica de mostreig de dades complexa (fins i tot utilitzant bucles)
  - obtenint dades de moltes taules diferents
  - l'ús de UNIÓ и EXISTEIX
Oferta OPCIÓ molt útil quan necessitem proporcionar control de concurrència OPCIÓ (MAXDOP N), l'ordre del pla d'execució de la consulta. Per exemple:
- podeu especificar una recreació forçada del pla de consulta OPCIÓ (RECOMPILAR)
- podeu especificar si voleu forçar el pla de consulta a utilitzar l'ordre d'unió especificat a la consulta OPCIÓ (FORÇA L'ORDRE)
Més detalls sobre OPCIÓ descrit aquí.
Utilitzant la secció de dades més estreta i necessària:
No cal emmagatzemar grans conjunts de dades a la memòria cau (com és el cas de les vistes indexades), des dels quals encara cal filtrar les dades per paràmetre.
Per exemple, hi ha una taula el filtre de la qual WHERE s'utilitzen tres camps (a, b, c).

Convencionalment, totes les peticions tenen una condició constant a = 0 i b = 0.

No obstant això, la petició del camp c més variable.

Deixa la condició a = 0 i b = 0 Realment ens ajuda a limitar el conjunt resultant requerit a milers de registres, però la condició està activa с redueix la selecció a cent registres.

Aquí la funció de taula pot ser una millor opció.

A més, una funció de taula és més previsible i coherent en el temps d'execució.

Примеры

Vegem un exemple d'implementació utilitzant la base de dades de preguntes com a exemple.

Hi ha una petició SELECT, que combina diverses taules i utilitza una vista (OperativeQuestions), en la qual es verifica l'afiliació per correu electrònic (mitjançant EXISTEIX) a "Preguntes operatives":

Sol·licitud núm. 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])
));

La vista té una estructura força complexa: conté juntes de subconsultes i utilitza l'ordenació DISTINCT, que en general és una operació força intensiva en recursos.

Una mostra d'OperativeQuestions és d'uns deu mil registres.

El principal problema d'aquesta consulta és que per als registres de la consulta externa, s'executa una subconsulta interna a la vista [OperativeQuestions], que hauria de permetre, per a [Correu electrònic] = @p__linq__0 limitar la selecció de sortida (mitjançant EXISTEIX) fins a centenars de registres.

I pot semblar que la subconsulta hauria de calcular els registres una vegada per [Correu electrònic] = @p__linq__0, i després aquests parell de centenars de registres s'haurien de connectar mitjançant Id with Questions, i la consulta serà ràpida.

De fet, hi ha una connexió seqüencial de totes les taules: comprovant la correspondència de les preguntes d'identificació amb l'identificador d'OperativeQuestions i filtrant per correu electrònic.

De fet, la sol·licitud funciona amb les desenes de milers de registres d'OperativeQuestions, però només es necessiten les dades d'interès via correu electrònic.

Operative Questions visualitza el text:

Sol·licitud núm. 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));

Mapeig de visualització inicial a 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");
    }
}

Consulta LINQ inicial

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

En aquest cas concret, estem considerant una solució a aquest problema sense canvis d'infraestructura, sense introduir una taula separada amb resultats ja fets (“Consultes actives”), que requeriria un mecanisme per omplir-lo de dades i mantenir-lo actualitzat. .

Tot i que aquesta és una bona solució, hi ha una altra opció per optimitzar aquest problema.

L'objectiu principal és emmagatzemar les entrades a la memòria cau per [Correu electrònic] = @p__linq__0 des de la vista OperativeQuestions.

Introduïu la funció de taula [dbo].[OperativeQuestionsUserMail] a la base de dades.

En enviar un correu electrònic com a paràmetre d'entrada, obtenim una taula de valors:

Sol·licitud núm. 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

Això retorna una taula de valors amb una estructura de dades predefinida.

Perquè les consultes a OperativeQuestionsUserMail siguin òptimes i tinguin plans de consulta òptims, cal una estructura estricta i no TAULA DE DEVOLUCIONS COM A Devolució...

En aquest cas, la consulta 1 necessària es converteix en la consulta 4:

Sol·licitud núm. 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]);

Mapeig de vistes i funcions a 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})");
}

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

L'ordre del temps d'execució ha baixat de 200-800 ms a 2-20 ms, etc., és a dir, desenes de vegades més ràpid.

Si ho prenem de manera més mitjana, llavors en comptes de 350 ms tenim 8 ms.

Dels avantatges evidents també obtenim:

reducció general de la càrrega de lectura,
reducció significativa de la probabilitat de bloqueig
reduint el temps mitjà de bloqueig a valors acceptables

Sortida

Optimització i ajust de les trucades a la base de dades MS SQL a través d' LINQ és un problema que es pot resoldre.

L'atenció i la coherència són molt importants en aquest treball.

A l'inici del procés:

cal comprovar les dades amb què funciona la sol·licitud (valors, tipus de dades seleccionats)
dur a terme una indexació adequada d'aquestes dades
comprovar la correcció de les condicions d'unió entre taules

La següent iteració d'optimització revela:

base de la sol·licitud i defineix el filtre de sol·licitud principal
repetint blocs de consulta similars i analitzant la intersecció de condicions
en SSMS o una altra GUI per SQL Server s'optimitza Consulta SQL (assignar un emmagatzematge de dades intermedi, crear la consulta resultant utilitzant aquest emmagatzematge (poden haver-hi diversos))
en l'última etapa, prenent com a base el resultat Consulta SQL, l'estructura s'està reconstruint Consulta LINQ

El resultant Consulta LINQ hauria de ser idèntica en estructura a l'òptim identificat Consulta SQL del punt 3.

Agraïments

Moltes gràcies als companys jobgemws и alex_ozr de l'empresa Fortis per obtenir ajuda per preparar aquest material.

Font: www.habr.com

Mètodes per optimitzar les consultes LINQ en C#.NET