🥇Fomba fanatsarana ny fangatahana LINQ amin'ny C#.NET

fampidirana

В ity lahatsoratra ity noheverina ny fomba fanatsarana sasany Fanontaniana LINQ.
Eto izahay dia manolotra fomba fiasa hafa amin'ny fanatsarana kaody mifandraika amin'ny Fanontaniana LINQ.

Fantatra izany LINQ(Language-Integrated Query) dia fiteny tsotra sy mety amin'ny fangatahana loharano angona.

А LINQ mankany SQL dia teknôlôjia ahafahana miditra angon-drakitra ao anaty DBMS. Izy io dia fitaovana mahery vaika amin'ny fiasana amin'ny angon-drakitra, izay amboarina amin'ny alalan'ny fiteny fanambarana ny fanontaniana, izay havadika ho lasa SQL fanontaniana sehatra ary alefa any amin'ny mpizara database ho an'ny famonoana. Amin'ny tranga misy antsika, ny DBMS no tiana holazaina MS SQL Server.

Na izany aza, Fanontaniana LINQ tsy avadika ho soratra tsara indrindra SQL fanontaniana, izay azon'ny DBA efa za-draharaha manoratra miaraka amin'ireo nuance rehetra momba ny fanatsarana SQL fanontaniana:

fifandraisana tsara indrindra (JOIN) ary sivana ny vokatra (AIZA)
nuances maro amin'ny fampiasana fifandraisana sy ny fepetran'ny vondrona
fiovana maro amin'ny fanoloana fepetra IN amin'ny MISYи TSY IN, <> ny MISY
caching manelanelana ny valiny amin'ny alàlan'ny tabilao vonjimaika, CTE, faribolan'ny latabatra
fampiasana fehezanteny (SAFIDY) miaraka amin'ny toromarika sy soso-kevitra latabatra WITH (...)
amin'ny fampiasana fomba fijery voatanisa ho toy ny iray amin'ireo fomba hanesorana ny famakiana angon-drakitra miverimberina mandritra ny fifantenana

Ny zava-bita lehibe bottlenecks ny vokatra SQL fanontaniana rehefa manangona Fanontaniana LINQ dia:

fanamafisam-peo ny rafitra fifantenana angon-drakitra manontolo amin'ny fangatahana iray
famadihana ireo sakana mitovy amin'ny kaody, izay mitarika amin'ny famakiana angon-drakitra maro tsy ilaina
vondrona misy fepetra maromaro (lojika "ary" ary "na") - SY и OR, mitambatra amin'ny toe-javatra sarotra, dia mitarika ho amin'ny zava-misy fa ny optimizer, manana tondro tsy mivondrona sahaza eo amin'ny saha ilaina, dia manomboka mijery amin'ny farany fanondroana clustered (INDEX SCAN) amin'ny vondron'ny fepetra
Ny fanatodizan'ny subqueries lalina dia miteraka olana be SQL fanambarana ary ny famakafakana ny drafi-panontaniana avy amin'ny mpamorona sy DBA

Optimization fomba

Andeha isika hifindra mivantana amin'ny fomba fanatsarana.

1) Fanondroana fanampiny

Ny tsara indrindra dia ny mandinika sivana eo amin'ny latabatra mifantina lehibe, satria matetika ny fangatahana manontolo dia miorina amin'ny latabatra lehibe iray na roa (applications-people-operations) ary miaraka amin'ny fepetra mahazatra (IsClosed, Cancelled, Enabled, Status). Zava-dehibe ny mamorona indices sahaza ho an'ireo santionany fantatra.

Ity vahaolana ity dia misy dikany rehefa misafidy ireo saha ireo dia mametra tanteraka ny averina amin'ny fangatahana.

Ohatra, manana fangatahana 500000 izahay. Na izany aza, tsy misy afa-tsy 2000 ny fampiharana mavitrika. Avy eo ny fanondroana voafantina tsara dia hamonjy antsika amin'ny INDEX SCAN eo amin'ny latabatra lehibe ary hamela anao hisafidy haingana angona amin'ny alàlan'ny fanondroana tsy mivondrona.

Ary koa, ny tsy fahampian'ny fanondroana dia azo fantarina amin'ny alàlan'ny bitsika amin'ny famakafakana drafitry ny fangatahana na fanangonana antontan'isa fijerena rafitra. MS SQL Server:

Ny angon'ny fijerena rehetra dia misy fampahafantarana momba ireo tondro tsy hita, afa-tsy ny tondro spatial.

Na izany aza, ny fanondroana sy ny caching dia matetika fomba hiadiana amin'ny vokatry ny fanoratana ratsy Fanontaniana LINQ и SQL fanontaniana.

Araka ny asehon'ny fanao henjana eo amin'ny fiainana dia zava-dehibe ho an'ny orinasa iray ny mampihatra ireo endri-javatra momba ny fandraharahana amin'ny fe-potoana sasany. Ary noho izany, matetika ny fangatahana mavesatra dia afindra any ambadika miaraka amin'ny caching.

Marina amin'ny ampahany izany, satria tsy mila ny angon-drakitra farany foana ny mpampiasa ary misy ny haavon'ny fandraisan'ny mpampiasa interface tsara.

Ity fomba fiasa ity dia mamela ny famahana ny filan'ny orinasa, saingy amin'ny farany dia mampihena ny fahombiazan'ny rafitra fampahalalam-baovao amin'ny alàlan'ny fanemorana fotsiny ny vahaolana amin'ny olana.

Tsara ihany koa ny mitadidy fa eo am-pikarohana ireo indeksa ilaina hanampiana, soso-kevitra MS SQL Mety ho diso ny fanatsarana, ao anatin'izany ireto fepetra manaraka ireto:

raha toa ka efa misy index miaraka amin'ny sehatra mitovy
raha toa ka tsy azo soratana ny saha ao amin'ny tabilao noho ny famerana ny fanondroana (voalaza amin'ny antsipiriany bebe kokoa eto).

2) Mampitambatra ireo toetra ho toetra vaovao iray

Indraindray ny saha sasany avy amin'ny latabatra iray, izay fototry ny vondron'ny fepetra, dia azo soloina amin'ny fampidirana saha vaovao.

Marina indrindra izany ho an'ny sahan'ny status, izay mazàna dia bitika na integer amin'ny karazana.

ohatra:

IsClosed = 0 ARY Foana = 0 ARY Enabled = 0 nosoloin'i Status = 1.

Eo no ampidirina ny toetran'ny Status integer mba hahazoana antoka fa voapetraka ao anaty latabatra ireo sata ireo. Manaraka izany, ity toetra vaovao ity dia indexed.

Vahaolana fototra amin'ny olana momba ny fampandehanana izany, satria miditra amin'ny angona tsy misy kajikajy tsy ilaina.

3) Fanamafisana ny fomba fijery

Indrisy fa in Fanontaniana LINQ Tsy azo ampiasaina mivantana ny tabilao vonjimaika, CTE, ary ny faribolan'ny latabatra.

Na izany aza, misy fomba iray hafa hanamafisana an'ity tranga ity - indexed view.

Vondrona fepetra (avy amin'ny ohatra etsy ambony) IsClosed = 0 ARY Foana = 0 ARY Enabled = 0 (na andiana fepetra hafa mitovy amin'izany) dia lasa safidy tsara hampiasana azy ireo amin'ny fomba fijery indexed, mitahiry ampahany kely amin'ny angona avy amin'ny andiany lehibe.

Saingy misy fameperana maromaro rehefa manatanteraka ny fomba fijery:

mampiasa subqueries, clauses MISY tokony hosoloina amin'ny fampiasana JOIN
tsy afaka mampiasa fehezanteny ianao Union, UNION REHETRA, afa-tsy, INTERSECT
Tsy afaka mampiasa soso-kevitra sy fehezan-teny ianao SAFIDY
tsy azo atao ny miasa amin'ny cycles
Tsy azo atao ny mampiseho angona amin'ny fomba fijery iray avy amin'ny tabilao samihafa

Zava-dehibe ny mitadidy fa ny tena tombony amin'ny fampiasana indexed view dia tsy ho tratra raha tsy amin'ny tena indexing azy.

Saingy rehefa miantso fijery dia tsy azo ampiasaina ireo fanondroana ireo, ary raha hampiasa azy ireo mazava ianao dia tsy maintsy mamaritra MISY (NOEXPAND).

Hatramin'ny Fanontaniana LINQ Tsy azo atao ny mamaritra ny soso-kevitry ny latabatra, noho izany dia tsy maintsy mamorona solontena hafa ianao - "wrapper" amin'ny endrika manaraka:

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

4) Mampiasa asa latabatra

Matetika amin'ny Fanontaniana LINQ Vondrona lehibe amin'ny subqueries na sakana mampiasa fomba fijery miaraka amin'ny rafitra sarotra dia mamorona fanontaniana farany miaraka amin'ny rafitra famonoana tena sarotra sy ambany indrindra.

Tombontsoa lehibe amin'ny fampiasana ny Functions Table in Fanontaniana LINQ:

Ny fahaiza-manao, toy ny amin'ny fomba fijery, dia ampiasaina sy voatondro ho zavatra, fa afaka mandalo andiana masontsivana fampidirana ianao:
FROM FUNCTION(@param1, @param2 ...)
Vokatr'izany dia azo atao ny santionany amin'ny angon-drakitra miovaova
Raha mampiasa asa latabatra, dia tsy misy fameperana mafy toy ny amin'ny fomba fijery voatanisa voalaza etsy ambony:
1. Soso-kevitra amin'ny latabatra:
  ny alalan ' LINQ Tsy azonao atao ny mamaritra hoe inona ny fanondroana tokony hampiasaina sy hamaritana ny haavon'ny fitokana-monina rehefa manontany.
  Saingy manana ireo fahaiza-manao ireo ny asa.
  Miaraka amin'ny fiasa, azonao atao ny manatratra drafi-panontaniana momba ny fanatanterahana tsy tapaka, izay mamaritra ny fitsipika momba ny fiasana amin'ny fanondroana sy ny haavon'ny fitokana-monina.
2. Ny fampiasana ny fiasa dia mamela, raha ampitahaina amin'ny fomba fijery indexed, hahazoana:
  - lojika sampling data sarotra (na dia mampiasa loops aza)
  - maka angona avy amin'ny tabilao maro samihafa
  - ny fampiasana ny Union и MISY
tolotra SAFIDY tena ilaina rehefa mila manome fanaraha-maso concurrency isika SAFIDY (MAXDOP N), ny filaharan'ny drafitra fanatanterahana fanontaniana. Ohatra:
- azonao atao ny mamaritra ny famoronana an-tery ny drafitry ny fangatahana SAFIDY (RECOMPILE)
- azonao atao ny mamaritra raha hanery ny drafitry ny fangatahana hampiasa ny filaharana iombonana voalaza ao amin'ny fangatahana SAFIDY (FORCE ORDER)
antsipiriany bebe kokoa momba ny SAFIDY voalaza eto.
Mampiasa ny ampahany tery indrindra sy ilaina indrindra:
Tsy ilaina ny mitahiry angon-drakitra lehibe ao amin'ny caches (toy ny tranga amin'ny fomba fijery indexed), izay mbola mila manivana ny angona amin'ny parameter.
Ohatra, misy latabatra misy sivana AIZA faritra telo no ampiasaina (a, b, c).

Raha ny mahazatra, ny fangatahana rehetra dia manana fepetra tsy tapaka a = 0 ary b = 0.

Na izany aza, ny fangatahana an-tsaha c miovaova kokoa.

Avelao ny fepetra a = 0 ary b = 0 Tena manampy antsika hametra ny vokatra takiana napetraka ho an'arivony firaketana an-tsoratra, fa ny fepetra с mampihena ny fifantenana amin'ny firaketana an-jatony.

Eto dia mety ho safidy tsara kokoa ny fiasan'ny latabatra.

Ary koa, ny fiasan'ny latabatra dia azo vinavinaina kokoa sy mifanaraka amin'ny fotoana fanatanterahana.

ohatra

Andeha hojerentsika ny fampiharana ohatra amin'ny fampiasana ny angon-drakitra Fanontaniana ho ohatra.

Misy ny fangatahana SELECT, izay manambatra latabatra maromaro ary mampiasa fijery iray (OperativeQuestions), izay manamarina ny fifandraisana amin'ny mailaka (amin'ny alàlan'ny MISY) mankany amin'ny "Fanontaniana momba ny fandidiana":

Fangatahana No. 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])
));

Ny fomba fijery dia manana rafitra somary saro-takarina: manana subquery mitambatra ary mampiasa fanasokajiana MIAVAKA.

Santionany avy amin'ny OperativeQuestions dia rakitra iray alina eo ho eo.

Ny olana lehibe amin'ity fanontaniana ity dia ny hoe ho an'ny firaketana avy amin'ny fangatahana ivelany, dia misy subquery anatiny tanterahana amin'ny fijery [OperativeQuestions], izay tokony ho an'ny [Email] = @p__linq__0 dia mamela antsika hametra ny fifantenana vokatra (amin'ny alàlan'ny MISY) hatramin'ny firaketana an-jatony.

Ary mety ho toa kajikajy indray mandeha amin'ny alàlan'ny [Email] = @p__linq__0 ny subquery, ary avy eo dia tokony ampifandraisina amin'ny Id miaraka amin'ny Questions ireo rakitra an-jatony roa ireo, ary ho haingana ny fangatahana.

Raha ny marina, misy fifandraisana misesy ny latabatra rehetra: manamarina ny taratasin'ny Id Questions miaraka amin'ny Id avy amin'ny OperativeQuestions, ary ny sivana amin'ny mailaka.

Raha ny marina, ny fangatahana dia miasa miaraka amin'ireo firaketana OperativeQuestions an'aliny rehetra, fa ny angon-drakitra mahaliana ihany no ilaina amin'ny alàlan'ny mailaka.

OperativeQuestions mijery lahatsoratra:

Fangatahana No. 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));

Sarintany fijery voalohany amin'ny 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");
    }
}

Fanontaniana LINQ voalohany

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

Amin'ity tranga manokana ity dia mandinika vahaolana amin'ity olana ity tsy misy fanovana fotodrafitrasa izahay, nefa tsy mampiditra latabatra misaraka miaraka amin'ny valiny efa vita ("Fanontaniana mavitrika"), izay mitaky mekanika hamenoana azy amin'ny angon-drakitra sy fitazonana azy ho vaovao. .

Na dia vahaolana tsara aza izany, dia misy safidy hafa hanatsarana ity olana ity.

Ny tena tanjona dia ny hametaka ny fidirana amin'ny alàlan'ny [Email] = @p__linq__0 avy amin'ny fomba fijery OperativeQuestions.

Ampidiro ao anaty angon-drakitra ny fiasan'ny latabatra [dbo].[OperativeQuestionsUserMail].

Amin'ny alàlan'ny fandefasana mailaka ho toy ny mari-pamantarana fampidirana, dia mahazo tabilao misy sanda isika:

Fangatahana No. 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

Mamerina tabilao misy soatoavina misy rafitra angon-drakitra efa voafaritra mialoha izany.

Mba hahatonga ny fanontaniana amin'ny OperativeQuestionsUserMail ho tsara indrindra sy hanana drafitra fangatahana tsara indrindra dia ilaina ny rafitra henjana, fa tsy MIVERINA TABLE HO RETURNE...

Amin'ity tranga ity, ny fangatahana 1 ilaina dia avadika ho Query 4:

Fangatahana No. 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 ny fijery sy ny fiasa ao amin'ny 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})");
}

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

Nidina avy amin'ny 200-800 ms ny filaharan'ny fotoana famonoana, ho 2-20 ms, sns, izany hoe avo folo heny haingana kokoa.

Raha raisina amin'ny antonony kokoa izany, dia 350 ms raha tokony ho 8 ms.

Avy amin'ny tombony mazava dia azontsika ihany koa:

fampihenana ankapobe ny enta-mamaky,
fihenam-bidy lehibe amin'ny mety hisian'ny fanakanana
fampihenana ny salan'isa fotoana fanakanana ho sanda azo ekena

famaranana

Optimization sy fanitsiana tsara ny antso an-databatra MS SQL ny alalan ' LINQ dia olana azo vahana.

Tena zava-dehibe amin'ity asa ity ny fiheverana sy ny tsy fitoviana.

Amin'ny fiandohan'ny dingana:

ilaina ny manamarina ny angon-drakitra izay iasan'ny fangatahana (soatoavina, karazana data voafantina)
manaova fanondroana araka ny tokony ho izy an'io data io
jereo ny fahamarinan'ny fepetra mampifandray eo amin'ny latabatra

Ny fanavaozana optimization manaraka dia manambara:

fototry ny fangatahana ary mamaritra ny sivana fangatahana lehibe
mamerimberina sakana fanontaniana mitovy ary mamakafaka ny fihaonan'ny fepetra
amin'ny SSMS na GUI hafa ho an'ny SQL Server optimizes ny tenany SQL fanontaniana (manokana fitehirizana angon-drakitra mpanelanelana, manamboatra ny valin'ny fanontaniana amin'ny fampiasana ity fitahirizana ity (mety misy maromaro))
amin'ny dingana farany, maka ho fototry ny vokatra SQL fanontaniana, amboarina indray ny rafitra Fanontaniana LINQ

Ny vokany Fanontaniana LINQ tokony hitovy amin'ny rafitra amin'ny tsara indrindra fantatra SQL fanontaniana avy amin'ny teboka 3.

fankatelemana

Misaotra betsaka ny mpiara-miasa jobgemws и alex_ozr avy amin'ny orinasa Fortis ho fanampiana amin'ny fanomanana ity fitaovana ity.

Source: www.habr.com

Fomba hanatsarana ny fangatahana LINQ amin'ny C#.NET