🥇Njira zokometsera mafunso a LINQ mu C#.NET

Mau oyamba

В nkhaniyi njira zina zokometsera zinaganiziridwa LINQ mafunso.
Apa tikuwonetsanso njira zina zowonjezera ma code okhudzana ndi LINQ mafunso.

Zimadziwika kuti Zamgululi(Language-Integrated Query) ndi chilankhulo chosavuta komanso chosavuta kufunsa komwe kumachokera deta.

А LINQ kupita ku SQL ndi teknoloji yopezera deta mu DBMS. Ichi ndi chida champhamvu chogwirira ntchito ndi data, pomwe mafunso amapangidwa kudzera muchilankhulo chofotokozera, chomwe chimasinthidwa kukhala Mafunso a SQL nsanja ndikutumizidwa ku seva ya database kuti ichitike. Kwa ife, ndi DBMS tikutanthauza MS SQL Server.

Komabe, LINQ mafunso sanasinthidwe kukhala olembedwa bwino lomwe Mafunso a SQL, yomwe DBA yodziwa bwino imatha kulemba ndi mitundu yonse ya kukhathamiritsa Mafunso a SQL:

kulumikizana bwino (ONANI) ndikusefa zotsatira (KUMENE)
ma nuances ambiri pakugwiritsa ntchito kulumikizana ndi magulu
zosiyanasiyana zambiri m'malo mikhalidwe IN pa ALIPOи OSATI MWA<> pa ALIPO
kusungidwa kwapakati pazotsatira kudzera pamatebulo osakhalitsa, CTE, zosintha zamatebulo
kugwiritsa ntchito mawu (KUCHITA) ndi malangizo ndi malangizo patebulo NDI (...)
kugwiritsa ntchito mawonedwe a indexed monga njira imodzi yochotsera kuwerengera kosafunikira pakusankha

Waukulu ntchito botolo la chifukwa Mafunso a SQL pokonza LINQ mafunso Ali:

kuphatikiza njira yonse yosankha deta mu pempho limodzi
kubwereza midadada yofanana ya ma code, zomwe pamapeto pake zimatsogolera ku kuwerenga kochuluka kosafunikira
magulu amitundu yambiri (zomveka "ndi" ndi "kapena") - AND и OR, kuphatikiza m'mikhalidwe yovuta, imatsogolera ku mfundo yakuti optimizer, pokhala ndi ma index oyenerera osagwirizana ndi magawo ofunikira, pamapeto pake amayamba kusanthula molingana ndi clustered index (INDEX SCAN) ndi magulu amikhalidwe
Kuyika zisa zakuya za subqueries kumapangitsa kuyika kukhala kovuta kwambiri Mawu a SQL ndi kusanthula dongosolo lamafunso kumbali ya omanga ndi DBA

Njira zowonjezera

Tsopano tiyeni kusuntha mwachindunji kukhathamiritsa njira.

1) Zolozera zowonjezera

Ndi bwino kuganizira zosefera pamatebulo akuluakulu osankhidwa, chifukwa nthawi zambiri funso lonse limamangidwa mozungulira tebulo lalikulu limodzi kapena awiri (ntchito za anthu) komanso zokhazikika (IsClosed, Canceled, Ebled, Status). Ndikofunika kupanga zizindikiro zoyenera za zitsanzo zomwe zadziwika.

Yankho ili ndilomveka posankha minda iyi imalepheretsa kwambiri zomwe zabwezedwa ku funso.

Mwachitsanzo, tili ndi mapulogalamu 500000. Komabe, pali mapulogalamu 2000 okha omwe akugwira ntchito. Kenako index yosankhidwa bwino idzatipulumutsa INDEX SCAN patebulo lalikulu ndipo ikulolani kuti musankhe mwachangu deta kudzera mu index yosagwirizana.

Komanso, kusowa kwa indexes kumatha kuzindikirika kudzera muzambiri zofotokozera mapulani amafunso kapena kusonkhanitsa ziwerengero zamawonekedwe adongosolo. MS SQL Server:

Mawonedwe onse ali ndi chidziwitso chosowa, kupatula ma index a malo.

Komabe, ma index ndi caching nthawi zambiri ndi njira zothanirana ndi zotsatira za kusalemba bwino LINQ mafunso и Mafunso a SQL.

Monga momwe mchitidwe wovuta wa moyo umasonyezera, nthawi zambiri ndikofunikira kuti bizinesi ikwaniritse zochitika zabizinesi ndi nthawi zina. Ndipo chifukwa chake, zopempha zolemetsa nthawi zambiri zimasamutsidwa kumbuyo ndi caching.

Izi zili zomveka, chifukwa wogwiritsa ntchito safunikira nthawi zonse zomwe zachitika posachedwa ndipo pali mulingo wovomerezeka wa kulabadira kwa mawonekedwe.

Njirayi imalola kuthetsa zosowa zamabizinesi, koma pamapeto pake imachepetsa magwiridwe antchito a chidziwitso pongochedwetsa njira zothetsera mavuto.

Ndikoyeneranso kukumbukira kuti pofufuza ma index oyenera kuwonjezera, malingaliro MS SQL kukhathamiritsa kungakhale kolakwika, kuphatikiza pamikhalidwe iyi:

ngati pali ma index omwe ali ndi magawo ofanana
ngati magawo omwe ali patebulo sangathe kulembedwa chifukwa cha zoletsa zolozera (zofotokozedwa mwatsatanetsatane apa).

2) Kuphatikiza zikhumbo kukhala chinthu chatsopano chatsopano

Nthaŵi zina minda ina yochokera patebulo limodzi, yomwe imakhala maziko a kagulu ka mikhalidwe, ingasinthidwe ndi kuyambitsa gawo latsopano.

Izi ndi zowona makamaka m'magawo omwe ali ndi udindo, omwe nthawi zambiri amakhala ochepa kapena ochepa.

Chitsanzo:

IsClosed = 0 NDIPO Yaletsedwa = 0 NDIPO Yathandizidwa = 0 m'malo mwake udindo = 1.

Apa ndipamene chiwerengero cha chiwerengero cha Status chimayambitsidwa kuti zitsimikizire kuti ziwerengerozi zili patebulo. Chotsatira, chikhalidwe chatsopanochi chikuwonetsedwa.

Ili ndi yankho lofunikira ku vuto la magwiridwe antchito, chifukwa Timapeza data popanda kuwerengera kosafunikira.

3) Kusintha kwa mawonekedwe

Tsoka ilo, mu LINQ mafunso Matebulo osakhalitsa, CTEs, ndi zosintha zamatebulo sizingagwiritsidwe ntchito mwachindunji.

Komabe, pali njira ina yokwaniritsira nkhaniyi - mawonedwe a indexed.

Gulu la zinthu (kuchokera ku chitsanzo pamwambapa) IsClosed = 0 NDIPO Yaletsedwa = 0 NDIPO Yathandizidwa = 0 (kapena seti ya mikhalidwe ina yofananira) imakhala njira yabwino yoti mugwiritse ntchito poyang'ana indexed, kusungitsa kagawo kakang'ono ka data kuchokera pagulu lalikulu.

Koma pali zoletsa zingapo pakupanga mawonekedwe:

kugwiritsa ntchito ma subqueries, clauses ALIPO iyenera kusinthidwa ndikugwiritsa ntchito ONANI
simungagwiritse ntchito ziganizo UNION, Mgwirizano ONSE, KUKHALA, KUDZIPEREKA
Simungagwiritse ntchito zizindikiro za tebulo ndi ziganizo KUCHITA
palibe kuthekera kogwira ntchito mozungulira
Ndikosatheka kuwonetsa deta pamawonedwe amodzi kuchokera kumatebulo osiyanasiyana

Ndikofunikira kukumbukira kuti phindu lenileni la kugwiritsira ntchito kawonedwe ka indexed likhoza kupezedwa mwa kulondoleradi.

Koma poyimba mawonedwe, ma index awa sangagwiritsidwe ntchito, ndipo kuti muwagwiritse ntchito momveka bwino, muyenera kufotokoza NDI(NOEXPAND).

Kuyambira mu LINQ mafunso Ndikosatheka kufotokozera malingaliro a tebulo, chifukwa chake muyenera kupanga choyimira china - "wrapper" ya mawonekedwe otsatirawa:

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

4) Kugwiritsa ntchito tebulo

Nthawi zambiri mu LINQ mafunso Mipiringidzo ikuluikulu ya ma subqueries kapena midadada yogwiritsa ntchito mawonedwe okhala ndi mawonekedwe ovuta kwambiri imapanga funso lomaliza lomwe lili ndi mawonekedwe ovuta kwambiri komanso ocheperako.

Ubwino Waikulu Wogwiritsa Ntchito Table Functions mu LINQ mafunso:

Kutha, monga momwe zilili ndi mawonedwe, kugwiritsidwa ntchito ndikufotokozedwa ngati chinthu, koma mutha kudutsa magawo angapo olowera:
KUCHOKERA KU FUNCTION(@param1, @param2 ...)
Zotsatira zake, sampuli zosinthika za data zitha kukwaniritsidwa
Pankhani yogwiritsa ntchito tebulo, palibe zoletsa zamphamvu monga momwe zilili ndi malingaliro omwe afotokozedwa pamwambapa:
1. Malangizo patebulo:
  через Zamgululi Simungatchule kuti ndi ma index ati omwe akuyenera kugwiritsidwa ntchito ndikuzindikira mulingo wolekanitsa deta mukamafunsa.
  Koma ntchitoyi ili ndi mphamvu izi.
  Ndi ntchitoyi, mutha kukwaniritsa dongosolo lamafunso nthawi zonse, pomwe malamulo ogwirira ntchito ndi ma index ndi magawo azipatula amatanthauziridwa.
2. Kugwiritsa ntchito ntchitoyi kumalola, poyerekeza ndi ma indexed, kupeza:
  - logic yovuta ya zitsanzo (ngakhale kugwiritsa ntchito malupu)
  - kutengera deta kuchokera kumatebulo osiyanasiyana
  - ntchito UNION и ALIPO
Zopereka KUCHITA zothandiza kwambiri pamene tikufuna kupereka concurrency control ZOCHITA(MAXDOP N), dongosolo la dongosolo la mafunso. Mwachitsanzo:
- mukhoza kufotokoza mokakamiza kulenganso dongosolo lafunso ZOCHITA (RECOMPILE)
- mutha kufotokoza ngati mungakakamize dongosolo lafunso kugwiritsa ntchito dongosolo lojowina lomwe lafotokozedwa mufunso OPTION (FORCE ORDER)
Zambiri za KUCHITA anafotokoza apa.
Kugwiritsa ntchito kagawo kakang'ono kwambiri komanso kofunikira kwambiri:
Palibe chifukwa chosungira ma data akulu mu cache (monga momwe zimakhalira ndi ma indexed indexed), pomwe mukufunikabe kusefa deta ndi parameter.
Mwachitsanzo, pali tebulo amene fyuluta KUMENE magawo atatu amagwiritsidwa ntchito (a, b, c).

Conventionally, zopempha zonse ndi chikhalidwe nthawi zonse a = 0 ndi b = 0.

Komabe, pempho la munda c zambiri zosinthika.

Lolani chikhalidwe a = 0 ndi b = 0 Zimatithandiza kuchepetsa zomwe zimafunikira kukhala zolemba masauzande ambiri, koma momwe zimakhalira с amachepetsa kusankhidwa mpaka zolemba zana.

Pano ntchito ya tebulo ikhoza kukhala njira yabwinoko.

Komanso, ntchito ya tebulo imakhala yodziwikiratu komanso yosasinthasintha panthawi yochita.

zitsanzo

Tiyeni tiwone chitsanzo chokhazikitsa pogwiritsa ntchito nkhokwe ya Mafunso monga chitsanzo.

Pali pempho SANKHANI, yomwe imaphatikiza matebulo angapo ndikugwiritsa ntchito mawonekedwe amodzi (OperativeQuestions), momwe kuyanjana kumafufuzidwa ndi imelo (kudzera ALIPO) mpaka "Mafunso Othandizira":

Pempho nambala 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])
));

Mawonekedwewa ali ndi mawonekedwe ovuta: ali ndi ma subquery amalumikizana ndikugwiritsa ntchito kusanja DISTINCT, yomwe nthawi zambiri imakhala yogwiritsa ntchito zinthu zambiri.

Chitsanzo chochokera ku OperativeQuestions chili pafupi ndi zolemba zikwi khumi.

Vuto lalikulu ndi funsoli ndilakuti pazolemba zochokera ku funso lakunja, gawo lamkati limayikidwa pa [OperativeQuestions] view, zomwe ziyenera kwa [Email] = @p__linq__0 kutilola kuti tichepetse kusankha (kudzera ALIPO) mpaka mazana a zolemba.

Ndipo zitha kuwoneka kuti gawo locheperako liyenera kuwerengera zolembazo kamodzi ndi [Imelo] = @p__linq__0, kenako ma rekodi mazana angapo akuyenera kulumikizidwa ndi Id ndi Mafunso, ndipo funso lidzakhala lachangu.

M'malo mwake, pali kulumikizana motsatizana pamagome onse: kuyang'ana makalata a Id Mafunso ndi Id kuchokera ku OperativeQuestions, ndikusefa ndi Imelo.

M'malo mwake, pempholi limagwira ntchito ndi makumi masauzande a zolemba za OperativeQuestions, koma ndizomwe zimafunikira chidwi kudzera pa Imelo.

OperativeQuestions onani mawu:

Pempho nambala 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));

Kujambula koyamba mu 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");
    }
}

Funso loyamba la LINQ

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

Pachifukwa ichi, tikuganizira njira yothetsera vutoli popanda kusintha kwachitukuko, popanda kuyambitsa tebulo lapadera lomwe liri ndi zotsatira zokonzeka ("Active Queries"), zomwe zingafune njira yodzaza ndi deta ndikuyisunga. .

Ngakhale ili ndi yankho labwino, pali njira ina yokwaniritsira vutoli.

Cholinga chachikulu ndikusunga zolembedwa ndi [Imelo] = @p__linq__0 kuchokera pakuwona kwa OperativeQuestions.

Tsegulani ntchito ya tebulo [dbo].[OperativeQuestionsUserMail] munkhokwe.

Potumiza Imelo ngati gawo lolowera, timapezanso mndandanda wamakhalidwe:

Pempho nambala 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

Izi zimabweretsanso tebulo lamtengo wapatali lomwe lili ndi deta yofotokozedweratu.

Kuti mafunso ku OperativeQuestionsUserMail akhale oyenera komanso kukhala ndi malingaliro oyenera amafunso, dongosolo lokhazikika limafunikira, osati KUBWERETSA TABLE MONGA KUBWERERA...

Pankhaniyi, funso 1 lofunikira limasinthidwa kukhala Query 4:

Pempho nambala 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]);

Kupanga mapu ndi ntchito mu 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})");
}

Funso lomaliza la 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();

Dongosolo la nthawi yophedwa latsika kuchokera ku 200-800 ms, mpaka 2-20 ms, ndi zina zotero, mwachitsanzo, makumi khumi mofulumira.

Ngati titenga pang'onopang'ono, ndiye kuti m'malo mwa 350 ms tili ndi 8 ms.

Kuchokera pazabwino zoonekeratu timapezanso:

kuchepetsa kuchuluka kwa kuwerenga,
kuchepetsa kwambiri mwayi wotsekereza
kuchepetsa nthawi yotsekereza pafupifupi ku zikhalidwe zovomerezeka

Pomaliza

Kukhathamiritsa ndi kukonza bwino mafoni a database MS SQL через Zamgululi ndi vuto lomwe lingathe kuthetsedwa.

Kusamala ndi kusasinthasintha ndizofunikira kwambiri pantchito iyi.

Pachiyambi cha ndondomekoyi:

ndikofunikira kuyang'ana zomwe pempho limagwira ntchito (makhalidwe, mitundu yosankhidwa ya data)
tsatirani ndondomeko yoyenera ya deta iyi
fufuzani kulondola kwa kujowina zinthu pakati pa matebulo

Kukonzekera kotereku kukuwonetsa:

maziko a pempho ndi kufotokoza chachikulu pempho fyuluta
kubwereza midadada yamafunso ofanana ndikusanthula mphambano yazikhalidwe
mu SSMS kapena GUI ina ya SQL Server imadzikonzekeretsa yokha SQL funso (kugawa zosungirako zapakatikati, kupanga funso pogwiritsa ntchito chosungirachi (pangakhale zingapo))
pa siteji yotsiriza, kutenga monga maziko zotsatira SQL funso, nyumbayo ikumangidwanso LINQ funso

Zotsatira zake LINQ funso ziyenera kukhala zofanana ndi zomwe zadziwika bwino SQL funso pa point 3.

Zothokoza

Zikomo kwambiri anzanga ntchito и alex_ozr kuchokera ku kampani Fortis kuti muthandizidwe pokonzekera nkhaniyi.

Source: www.habr.com

Njira zokometsera mafunso a LINQ mu C#.NET