🥇 Dulliau ar gyfer optimeiddio ymholiadau LINQ yn C#.NET

Cyflwyniad

В Mae'r erthygl hon yn ystyriwyd rhai dulliau optimeiddio Ymholiadau LINQ.
Yma rydym hefyd yn cyflwyno rhai mwy o ddulliau o optimeiddio cod sy'n gysylltiedig â Ymholiadau LINQ.

Mae'n hysbys bod LINQMae (Iaith-Integredig Ymholiad) yn iaith syml a chyfleus ar gyfer cwestiynu ffynhonnell ddata.

А LINQ i SQL yn dechnoleg ar gyfer cyrchu data mewn DBMS. Mae hwn yn arf pwerus ar gyfer gweithio gyda data, lle mae ymholiadau'n cael eu llunio trwy iaith ddatganiadol, a fydd wedyn yn cael eu trosi'n Ymholiadau SQL platfform a'i anfon at weinydd y gronfa ddata i'w weithredu. Yn ein hachos ni, wrth DBMS rydym yn ei olygu Gweinydd MS SQL.

Fodd bynnag, Ymholiadau LINQ nad ydynt yn cael eu trosi'n rhai sydd wedi'u hysgrifennu orau Ymholiadau SQL, y gallai DBA profiadol ei ysgrifennu gyda'r holl arlliwiau o optimeiddio Ymholiadau SQL:

cysylltiadau gorau posibl (YMUNO) a hidlo'r canlyniadau (LLE)
llawer o arlliwiau wrth ddefnyddio cysylltiadau ac amodau grŵp
llawer o amrywiadau mewn amodau disodli IN ar YN BODOLIи DIM MEWN, <> ymlaen YN BODOLI
cadw canlyniadau yn y canol trwy dablau dros dro, CTE, newidynnau tabl
defnydd o frawddeg (OPSIWN) gyda chyfarwyddiadau ac awgrymiadau bwrdd GYDA (...)
defnyddio golygfeydd mynegrifol fel un o'r ffyrdd o gael gwared ar ddarlleniadau data diangen yn ystod detholiadau

Prif dagfeydd perfformiad y canlyniad Ymholiadau SQL wrth lunio Ymholiadau LINQ yw:

cydgrynhoi'r mecanwaith dethol data cyfan mewn un cais
dyblygu blociau o god union yr un fath, sydd yn y pen draw yn arwain at ddarlleniadau data diangen lluosog
grwpiau o amodau aml-gydran (rhesymegol “a” a “neu”) - AC и OR, gan gyfuno i amodau cymhleth, yn arwain at y ffaith bod yr optimizer, gyda mynegeion di-glwstwr addas ar gyfer y meysydd angenrheidiol, yn y pen draw yn dechrau sganio yn erbyn y mynegai clystyrog (SGAN MYNEGAI) gan grwpiau o gyflyrau
mae nythu dwfn o subqueries yn gwneud dosrannu yn broblematig iawn Datganiadau SQL a dadansoddiad o'r cynllun ymholiad ar ran datblygwyr a DBA

Dulliau optimeiddio

Nawr, gadewch i ni symud yn uniongyrchol i ddulliau optimeiddio.

1) Mynegeio ychwanegol

Mae'n well ystyried hidlwyr ar y prif dablau dethol, oherwydd yn aml iawn mae'r ymholiad cyfan wedi'i adeiladu o amgylch un neu ddau brif dabl (cymwysiadau-gweithrediadau pobl) a chyda set safonol o amodau (IsClosed, Canslo, Galluogi, Statws). Mae'n bwysig creu mynegeion priodol ar gyfer y samplau a nodwyd.

Mae'r datrysiad hwn yn gwneud synnwyr wrth ddewis y meysydd hyn yn cyfyngu'n sylweddol ar y set a ddychwelwyd i'r ymholiad.

Er enghraifft, mae gennym 500000 o geisiadau. Fodd bynnag, dim ond 2000 o geisiadau gweithredol sydd. Yna bydd mynegai a ddewiswyd yn gywir yn ein harbed rhag SGAN MYNEGAI ar fwrdd mawr a bydd yn caniatáu ichi ddewis data yn gyflym trwy fynegai nad yw'n glwstwr.

Hefyd, gellir nodi'r diffyg mynegeion trwy awgrymiadau ar gyfer dosrannu cynlluniau ymholiad neu gasglu ystadegau golwg system Gweinydd MS SQL:

Mae'r holl ddata gweld yn cynnwys gwybodaeth am fynegeion coll, ac eithrio mynegeion gofodol.

Fodd bynnag, mae mynegeion a caching yn aml yn ddulliau o frwydro yn erbyn canlyniadau ysgrifennu'n wael Ymholiadau LINQ и Ymholiadau SQL.

Fel y dengys arfer llym bywyd, mae'n aml yn bwysig i fusnes weithredu nodweddion busnes erbyn terfynau amser penodol. Ac felly, mae ceisiadau trwm yn aml yn cael eu trosglwyddo i'r cefndir gyda caching.

Gellir cyfiawnhau hyn yn rhannol, gan nad oes angen y data diweddaraf ar y defnyddiwr bob amser a bod lefel dderbyniol o ymatebolrwydd y rhyngwyneb defnyddiwr.

Mae'r dull hwn yn caniatáu datrys anghenion busnes, ond yn y pen draw mae'n lleihau perfformiad y system wybodaeth trwy ohirio atebion i broblemau yn unig.

Mae'n werth cofio hefyd, yn y broses o chwilio am y mynegeion angenrheidiol i'w hychwanegu, awgrymiadau MS SQL gall optimeiddio fod yn anghywir, gan gynnwys o dan yr amodau canlynol:

os oes mynegeion eisoes gyda set debyg o feysydd
os na ellir mynegeio'r meysydd yn y tabl oherwydd cyfyngiadau mynegeio (disgrifir yn fanylach yma).

2) Cyfuno priodoleddau yn un nodwedd newydd

Weithiau gellir disodli rhai meysydd o un bwrdd, sy'n gweithredu fel sail i grŵp o amodau, trwy gyflwyno un maes newydd.

Mae hyn yn arbennig o wir ar gyfer meysydd statws, sydd fel arfer naill ai'n ddarnau neu'n gyfanrif o ran math.

Enghraifft:

IsClosed = 0 AC Wedi'i Ganslo = 0 AC Wedi'i Galluogi = 0 yn cael ei ddisodli gan Statws = 1.

Dyma lle mae priodoledd Statws cyfanrif yn cael ei gyflwyno i sicrhau bod y statwsau hyn yn cael eu poblogi yn y tabl. Nesaf, mae'r nodwedd newydd hon wedi'i mynegeio.

Mae hwn yn ateb sylfaenol i'r broblem perfformiad, oherwydd Rydym yn cyrchu data heb gyfrifiadau diangen.

3) Materoli'r olygfa

Yn anffodus yn Ymholiadau LINQ Ni ellir defnyddio tablau dros dro, CTEs, a newidynnau tabl yn uniongyrchol.

Fodd bynnag, mae ffordd arall i wneud y gorau ar gyfer yr achos hwn - safbwyntiau mynegeio.

Grŵp cyflwr (o'r enghraifft uchod) IsClosed = 0 AC Wedi'i Ganslo = 0 AC Wedi'i Galluogi = 0 (neu set o amodau tebyg eraill) yn dod yn opsiwn da i'w defnyddio mewn golwg wedi'i fynegeio, gan storio darn bach o ddata o set fawr.

Ond mae yna nifer o gyfyngiadau wrth wireddu golygfa:

defnydd o subqueries, cymalau YN BODOLI dylid eu disodli gan ddefnyddio YMUNO
ni allwch ddefnyddio brawddegau UNION, UNDEB POB UN, EITHRIAD, DIDDORDEB
Ni allwch ddefnyddio awgrymiadau tabl a chymalau OPSIWN
dim posibilrwydd i weithio gyda beiciau
Mae'n amhosibl dangos data mewn un olwg o wahanol dablau

Mae'n bwysig cofio mai dim ond trwy ei fynegeio y gellir cyflawni gwir fudd defnyddio gwedd wedi'i fynegeio.

Ond wrth alw golwg, ni ellir defnyddio'r mynegeion hyn, ac i'w defnyddio'n benodol, rhaid i chi nodi GYDA(NOEXPAND).

Ers yn Ymholiadau LINQ Mae'n amhosib diffinio awgrymiadau tabl, felly mae'n rhaid i chi greu cynrychiolaeth arall - "lapiwr" o'r ffurf ganlynol:

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

4) Defnyddio swyddogaethau bwrdd

Yn aml mewn Ymholiadau LINQ Mae blociau mawr o subqueries neu flociau sy'n defnyddio golygfeydd gyda strwythur cymhleth yn ffurfio ymholiad terfynol gyda strwythur gweithredu cymhleth iawn ac is-optimaidd.

Manteision Allweddol Defnyddio Swyddogaethau Tabl yn Ymholiadau LINQ:

Y gallu, fel yn achos golygfeydd, i gael ei ddefnyddio a'i nodi fel gwrthrych, ond gallwch chi basio set o baramedrau mewnbwn:
O SWYDDOGAETH (@param1, @param2 ...)
O ganlyniad, gellir cyflawni samplu data hyblyg
Yn achos defnyddio swyddogaeth bwrdd, nid oes unrhyw gyfyngiadau mor gryf ag yn achos safbwyntiau mynegeiedig a ddisgrifir uchod:
1. Awgrymiadau tabl:
  drwy LINQ Ni allwch nodi pa fynegai y dylid eu defnyddio a phennu lefel ynysu data wrth ymholi.
  Ond mae gan y swyddogaeth y galluoedd hyn.
  Gyda'r swyddogaeth, gallwch gyflawni cynllun ymholiad gweithredu eithaf cyson, lle mae rheolau ar gyfer gweithio gyda mynegeion a lefelau ynysu data yn cael eu diffinio
2. Mae defnyddio'r swyddogaeth yn caniatáu, o'i gymharu â golygfeydd mynegedig, i gael:
  - rhesymeg samplu data cymhleth (hyd yn oed gan ddefnyddio dolenni)
  - nôl data o lawer o dablau gwahanol
  - y defnydd o UNION и YN BODOLI
Cynnig OPSIWN yn ddefnyddiol iawn pan fydd angen i ni ddarparu rheolaeth arian cyfred OPTION(MAXDOP N), trefn y cynllun gweithredu ymholiad. Er enghraifft:
- gallwch nodi ail-greu gorfodol o'r cynllun ymholiad OPSIWN (AIL-grynhoi)
- gallwch nodi a ddylid gorfodi cynllun yr ymholiad i ddefnyddio'r drefn uno a nodir yn yr ymholiad OPSIWN (GORCHYMYN HEDDLU)
Mwy o fanylion am OPSIWN a ddisgrifiwyd yma.
Gan ddefnyddio'r darn data culaf a mwyaf gofynnol:
Nid oes angen storio setiau data mawr mewn caches (fel sy'n wir gyda golygfeydd mynegeio), y mae angen i chi hidlo'r data yn ôl paramedr ohonynt o hyd.
Er enghraifft, mae tabl y mae ei hidlydd LLE defnyddir tri maes (a, b, c).

Yn gonfensiynol, mae gan bob cais gyflwr cyson a = 0 a b = 0.

Fodd bynnag, mae'r cais am y maes c yn fwy amrywiol.

Gadewch y cyflwr a = 0 a b = 0 Mae'n wir yn ein helpu i gyfyngu ar y set canlyniadol gofynnol i filoedd o gofnodion, ond mae'r amod ar с yn cyfyngu'r detholiad i gant o gofnodion.

Yma efallai y bydd swyddogaeth y tabl yn opsiwn gwell.

Hefyd, mae swyddogaeth bwrdd yn fwy rhagweladwy a chyson o ran amser gweithredu.

Примеры

Edrychwn ar weithrediad enghreifftiol gan ddefnyddio'r gronfa ddata Cwestiynau fel enghraifft.

Mae cais SELECT, sy'n cyfuno sawl tabl ac yn defnyddio un olwg (OperativeQuestions), lle mae cysylltiad yn cael ei wirio trwy e-bost (trwy YN BODOLI) i “Ymholiadau Gweithredol” ([Cwestiynau Gweithredol]):

Cais Rhif 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])
));

Mae gan yr olygfa strwythur eithaf cymhleth: mae ganddo uniadau subquery ac mae'n defnyddio didoli DISTINCT, sydd yn gyffredinol yn weithrediad eithaf dwys o ran adnoddau.

Mae sampl o OperativeQuestions tua deng mil o gofnodion.

Y brif broblem gyda'r ymholiad hwn yw ar gyfer y cofnodion o'r ymholiad allanol, gweithredir subquery mewnol ar yr olwg [OperativeQuestions], a ddylai ar gyfer [Email] = @p__linq__0 ganiatáu i ni gyfyngu'r dewis allbwn (drwy YN BODOLI) hyd at gannoedd o gofnodion.

A gall ymddangos y dylai'r subquery gyfrifo'r cofnodion unwaith gan [Email] = @p__linq__0, ac yna dylai'r cwpl o gannoedd hyn o gofnodion gael eu cysylltu gan Id â Questions, a bydd yr ymholiad yn gyflym.

Mewn gwirionedd, mae cysylltiad dilyniannol rhwng yr holl dablau: gwirio cyfatebiaeth Cwestiynau Id ag Id o OperativeQuestions, a hidlo trwy E-bost.

Mewn gwirionedd, mae'r cais yn gweithio gyda phob un o'r degau o filoedd o gofnodion ActiveQuestions, ond dim ond y data o ddiddordeb sydd ei angen trwy E-bost.

Testun gweld Cwestiynau Gweithredol:

Cais Rhif 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));

Mapio gwedd gychwynnol yn 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");
    }
}

Ymholiad LINQ cychwynnol

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

Yn yr achos penodol hwn, rydym yn ystyried ateb i’r broblem hon heb newidiadau seilwaith, heb gyflwyno tabl ar wahân gyda chanlyniadau parod (“Ymholiadau Gweithredol”), a fyddai’n gofyn am fecanwaith ar gyfer ei lenwi â data a’i gadw’n gyfredol. .

Er bod hwn yn ateb da, mae opsiwn arall i wneud y gorau o'r broblem hon.

Y prif bwrpas yw cecio cofnodion gan [Email] = @p__linq__0 o'r olwg OperativeQuestions.

Cyflwyno'r swyddogaeth tabl [dbo].[OperativeQuestionsUserMail] i'r gronfa ddata.

Trwy anfon E-bost fel paramedr mewnbwn, rydym yn cael tabl o werthoedd yn ôl:

Cais Rhif 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

Mae hyn yn dychwelyd tabl o werthoedd gyda strwythur data wedi'i ddiffinio ymlaen llaw.

Er mwyn i ymholiadau i OperativeQuestionsUserMail fod yn optimaidd a chael y cynlluniau ymholiad optimaidd, mae angen strwythur llym, ac nid TABL YN DYCHWELYD FEL DYCHWELYD...

Yn yr achos hwn, mae'r Ymholiad 1 gofynnol yn cael ei drawsnewid yn Ymholiad 4:

Cais Rhif 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]);

Mapio golygfeydd a swyddogaethau yn 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})");
}

Ymholiad LINQ terfynol

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

Mae trefn yr amser cyflawni wedi gostwng o 200-800 ms, i 2-20 ms, ac ati, h.y. ddegau o weithiau yn gyflymach.

Os byddwn yn ei gymryd yn fwy cyffredin, yna yn lle 350 ms fe gawsom 8 ms.

O'r manteision amlwg a gawn hefyd:

gostyngiad cyffredinol yn y llwyth darllen,
gostyngiad sylweddol yn y tebygolrwydd o rwystro
lleihau'r amser blocio cyfartalog i werthoedd derbyniol

Allbwn

Optimeiddio a mireinio galwadau cronfa ddata MS SQL drwy LINQ yn broblem y gellir ei datrys.

Mae astudrwydd a chysondeb yn bwysig iawn yn y gwaith hwn.

Ar ddechrau'r broses:

mae angen gwirio'r data y mae'r cais yn gweithio ag ef (gwerthoedd, mathau o ddata dethol)
cynnal mynegeio priodol o'r data hwn
gwirio cywirdeb yr amodau uno rhwng tablau

Mae'r iteriad optimeiddio nesaf yn datgelu:

sail y cais ac yn diffinio'r prif hidlydd cais
ailadrodd blociau ymholiad tebyg a dadansoddi croestoriad amodau
mewn SSMS neu GUI arall ar gyfer SQL Gweinyddwr yn optimeiddio ei hun Ymholiad SQL (dyrannu storfa ddata ganolraddol, adeiladu'r ymholiad canlyniadol gan ddefnyddio'r storfa hon (efallai y bydd sawl un))
yn y cam olaf, gan gymryd y canlyniad fel sail Ymholiad SQL, mae'r strwythur yn cael ei ailadeiladu ymholiad LINQ

Y canlyniad ymholiad LINQ dod yn union yr un fath o ran strwythur â'r optimaidd a nodwyd Ymholiad SQL o bwynt 3.

Cydnabyddiaethau

Diolch yn fawr i gydweithwyr swyddgemws и alex_ozr o'r cwmni Fortis am gymorth i baratoi'r deunydd hwn.

Ffynhonnell: hab.com

Dulliau ar gyfer optimeiddio ymholiadau LINQ yn C#.NET