В Dan l-artikolu ġew ikkunsidrati xi metodi ta' ottimizzazzjoni Mistoqsijiet LINQ.
Hawnhekk aħna wkoll nippreżentaw xi approċċi aktar għall-ottimizzazzjoni tal-kodiċi relatati Mistoqsijiet LINQ.
Huwa magħruf li l- LINQ(Mistoqsija Integrata bil-Lingwa) hija lingwa sempliċi u konvenjenti biex wieħed jistaqsi sors tad-dejta.
А LINQ għal SQL hija teknoloġija għall-aċċess tad-dejta f'DBMS. Din hija għodda qawwija biex taħdem mad-dejta, fejn il-mistoqsijiet huma mibnija permezz ta’ lingwa dikjarattiva, li mbagħad tiġi kkonvertita f’ Mistoqsijiet SQL pjattaforma u mibgħuta lis-server tad-database għall-eżekuzzjoni. Fil-każ tagħna, b'DBMS irridu nfissru MS SQL Server.
Madankollu, Mistoqsijiet LINQ ma jiġux konvertiti f’dawk miktuba bl-aħjar mod Mistoqsijiet SQL, li DBA b'esperjenza jista 'jikteb bl-sfumaturi kollha ta' ottimizzazzjoni Mistoqsijiet SQL:
konnessjonijiet ottimali (Jissieħbu) u l-iffiltrar tar-riżultati (FEJN)
ħafna sfumaturi fl-użu ta 'konnessjonijiet u kundizzjonijiet tal-grupp
ħafna varjazzjonijiet fil-kundizzjonijiet ta 'sostituzzjoni IN fuq EŻISTIи MHUX FI, <> fuq EŻISTI
caching intermedju tar-riżultati permezz ta' tabelli temporanji, CTE, varjabbli tat-tabella
użu tas-sentenza (GĦAŻLA) b'istruzzjonijiet u ħjiel ta' tabella MA (...)
tuża fehmiet indiċjati bħala wieħed mill-mezzi biex teħles mill-qari tad-dejta żejda waqt l-għażla
Il-konġestjonijiet ewlenin tal-prestazzjoni tar-riżultat Mistoqsijiet SQL meta tiġbor Mistoqsijiet LINQ huma:
il-konsolidazzjoni tal-mekkaniżmu kollu tal-għażla tad-dejta f'talba waħda
duplikazzjoni ta' blokki identiċi ta' kodiċi, li fl-aħħar mill-aħħar iwassal għal qari multipli ta' data bla bżonn
gruppi ta' kundizzjonijiet b'ħafna komponenti (loġika "u" u "jew") - U и OR, li tgħaqqad f'kundizzjonijiet kumplessi, iwassal għall-fatt li l-ottimizzatur, li jkollu indiċi mhux raggruppati adattati għall-oqsma meħtieġa, fl-aħħar mill-aħħar jibda jiskennja kontra l-indiċi raggruppat (SKAN TA' L-INDIĊI) minn gruppi ta' kundizzjonijiet
nesting fil-fond ta 'subqueries jagħmel parsing problematiku ħafna Dikjarazzjonijiet SQL u analiżi tal-pjan ta’ mistoqsija min-naħa tal-iżviluppaturi u DBA
Metodi ta' ottimizzazzjoni
Issa ejja nimxu direttament għall-metodi ta 'ottimizzazzjoni.
1) Indiċjar addizzjonali
L-aħjar huwa li tikkunsidra filtri fuq it-tabelli tal-għażla prinċipali, peress li ħafna drabi l-mistoqsija kollha hija mibnija madwar waħda jew żewġ tabelli prinċipali (applikazzjonijiet-nies-operazzjonijiet) u b'sett standard ta 'kundizzjonijiet (IsClosed, Cancelled, Enabled, Status). Huwa importanti li jinħolqu indiċijiet xierqa għall-kampjuni identifikati.
Din is-soluzzjoni tagħmel sens meta tagħżel dawn l-oqsma tillimita b'mod sinifikanti s-sett ritornat għall-mistoqsija.
Pereżempju, għandna 500000 applikazzjoni. Madankollu, hemm biss 2000 applikazzjoni attiva. Imbagħad indiċi magħżul b'mod korrett isalvana minn SKAN TA' L-INDIĊI fuq mejda kbira u tippermettilek tagħżel malajr id-dejta permezz ta’ indiċi mhux miġbur.
Barra minn hekk, in-nuqqas ta' indiċi jista' jiġi identifikat permezz ta' prompts għall-analiżi tal-pjanijiet ta' mistoqsija jew il-ġbir ta' statistika tal-vista tas-sistema MS SQL Server:
Id-dejta kollha tal-vista fiha informazzjoni dwar indiċijiet neqsin, bl-eċċezzjoni tal-indiċi spazjali.
Madankollu, l-indiċi u l-caching huma spiss metodi ta 'ġlieda kontra l-konsegwenzi ta' miktub ħażin Mistoqsijiet LINQ и Mistoqsijiet SQL.
Kif turi l-prattika ħarxa tal-ħajja, ħafna drabi huwa importanti għal negozju li jimplimenta karatteristiċi tan-negozju sa ċerti skadenzi. U għalhekk, talbiet kbar ħafna drabi jiġu trasferiti fl-isfond bil-caching.
Dan huwa parzjalment iġġustifikat, peress li l-utent mhux dejjem jeħtieġ l-aħħar dejta u hemm livell aċċettabbli ta' rispons tal-interface tal-utent.
Dan l-approċċ jippermetti li jissolvew il-ħtiġijiet tan-negozju, iżda fl-aħħar mill-aħħar inaqqas il-prestazzjoni tas-sistema tal-informazzjoni billi sempliċiment jittardja s-soluzzjonijiet għall-problemi.
Ta 'min jiftakar ukoll li fil-proċess ta' tiftix għall-indiċi meħtieġa biex iżżid, suġġerimenti MS SQL l-ottimizzazzjoni tista' tkun żbaljata, inkluż taħt il-kundizzjonijiet li ġejjin:
jekk diġà hemm indiċi b'sett simili ta' oqsma
jekk l-oqsma fit-tabella ma jistgħux jiġu indiċjati minħabba restrizzjonijiet ta’ indiċjar (deskritti f’aktar dettall hawn).
2) Għaqda ta' attributi f'attribut wieħed ġdid
Xi drabi xi oqsma minn tabella waħda, li jservu bħala bażi għal grupp ta 'kundizzjonijiet, jistgħu jiġu sostitwiti bl-introduzzjoni ta' qasam ġdid wieħed.
Dan jgħodd speċjalment għall-oqsma tal-istatus, li ġeneralment ikunu bit jew integer fit-tip.
Eżempju:
IsClosed = 0 AND Cancelled = 0 AND Enabled = 0 huwa mibdul bi Status = 1.
Dan huwa fejn l-attribut integer Status jiġi introdott biex jiġi żgurat li dawn l-istatus huma popolati fit-tabella. Sussegwentement, dan l-attribut ġdid huwa indiċjat.
Din hija soluzzjoni fundamentali għall-problema tal-prestazzjoni, għaliex Aħna naċċessaw dejta mingħajr kalkoli bla bżonn.
3) Il-materjalizzazzjoni tal-veduta
Sfortunatament fi Mistoqsijiet LINQ Tabelli temporanji, CTEs, u varjabbli tat-tabella ma jistgħux jintużaw direttament.
Madankollu, hemm mod ieħor biex jiġi ottimizzat għal dan il-każ - fehmiet indiċjati.
Grupp ta' kundizzjonijiet (mill-eżempju ta' hawn fuq) IsClosed = 0 AND Cancelled = 0 AND Enabled = 0 (jew sett ta 'kundizzjonijiet oħra simili) isir għażla tajba biex tużahom f'vista indiċjata, caching porzjon żgħir ta' dejta minn sett kbir.
Iżda hemm numru ta' restrizzjonijiet meta timmaterjalizza opinjoni:
użu ta' subqueries, klawsoli EŻISTI għandhom jiġu sostitwiti bl-użu Jissieħbu
ma tistax tuża sentenzi UNJONI, UNJONI KOLLHA, EĊĊEZZJONI, INTERSETT
Ma tistax tuża ħjiel ta' tabella u klawsoli GĦAŻLA
ebda possibbiltà li taħdem maċ-ċikli
Huwa impossibbli li turi d-dejta f'veduta waħda minn tabelli differenti
Huwa importanti li wieħed jiftakar li l-benefiċċju reali tal-użu ta 'veduta indiċjata jista' jinkiseb biss billi fil-fatt tiġi indiċjata.
Iżda meta ssejjaħ opinjoni, dawn l-indiċi ma jistgħux jintużaw, u biex tużahom b'mod espliċitu, trid tispeċifika B'(NOEXPAND).
Minn meta Mistoqsijiet LINQ Huwa impossibbli li tiddefinixxi ħjiel ta 'tabella, għalhekk trid toħloq rappreżentazzjoni oħra - "wrapper" tal-forma li ġejja:
CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);
4) L-użu tal-funzjonijiet tal-mejda
Ħafna drabi fil Mistoqsijiet LINQ Blokki kbar ta 'subqueries jew blokki li jużaw veduti bi struttura kumplessa jiffurmaw mistoqsija finali bi struttura ta' eżekuzzjoni kumplessa ħafna u subottimali.
Benefiċċji Ewlenin tal-Użu tal-Funzjonijiet tal-Tabella fi Mistoqsijiet LINQ:
Il-ħila, bħal fil-każ tal-fehmiet, li tintuża u tiġi speċifikata bħala oġġett, iżda tista 'tgħaddi sett ta' parametri ta 'input: MILL FUNZJONI(@param1, @param2 ...)
Bħala riżultat, jista 'jinkiseb kampjunar ta' data flessibbli
Fil-każ tal-użu ta' funzjoni ta' tabella, m'hemm l-ebda restrizzjonijiet qawwija bħal fil-każ ta' fehmiet indiċjati deskritti hawn fuq:
Ħjiel tat-tabella:
permezz LINQ Ma tistax tispeċifika liema indiċijiet għandhom jintużaw u tiddetermina l-livell ta' iżolament tad-dejta meta tagħmel mistoqsija.
Iżda l-funzjoni għandha dawn il-kapaċitajiet.
Bil-funzjoni, tista 'tikseb pjan ta' mistoqsija ta 'eżekuzzjoni pjuttost kostanti, fejn ir-regoli biex taħdem ma' indiċijiet u livelli ta 'iżolament tad-dejta huma definiti
L-użu tal-funzjoni jippermetti, meta mqabbel mal-veduti indiċjati, li tikseb:
loġika ta’ kampjunar ta’ data kumplessa (anke bl-użu ta’ loops)
iġib id-dejta minn ħafna tabelli differenti
l-użu ta ' UNJONI и EŻISTI
Offerta GĦAŻLA utli ħafna meta għandna bżonn nipprovdu kontroll tal-konkorrenza GĦAŻLA (MAXDOP N), l-ordni tal-pjan ta' eżekuzzjoni tal-mistoqsija. Pereżempju:
tista' tispeċifika ħolqien mill-ġdid sfurzat tal-pjan ta' mistoqsija GĦAŻLA (RIKOMPILA)
tista' tispeċifika jekk għandhiex iġiegħel lill-pjan ta' mistoqsija juża l-ordni ta' tingħaqad speċifikata fil-mistoqsija GĦAŻLA (ORDNI TAL-FURŻA)
Bl-użu tal-porzjon tad-dejta l-aktar dejqa u meħtieġa:
M'hemmx bżonn li taħżen settijiet ta' data kbar f'caches (kif inhu l-każ b'veduti indiċjati), li minnhom xorta trid tiffiltra d-data skont il-parametru.
Per eżempju, hemm tabella li l-filtru tagħha FEJN jintużaw tliet oqsma (a, b, c).
Konvenzjonalment, it-talbiet kollha għandhom kundizzjoni kostanti a = 0 u b = 0.
Madankollu, it-talba għall-qasam c aktar varjabbli.
Ħalli l-kundizzjoni a = 0 u b = 0 Huwa verament jgħinna biex jillimitaw is-sett li jirriżulta meħtieġ għal eluf ta 'rekords, iżda l-kundizzjoni fuq с jonqos l-għażla għal mitt rekord.
Ukoll, funzjoni tal-mejda hija aktar prevedibbli u konsistenti fil-ħin tal-eżekuzzjoni.
eżempji
Ejja nħarsu lejn eżempju ta' implimentazzjoni billi tuża d-database tal-Mistoqsijiet bħala eżempju.
Hemm talba SELECT, li tgħaqqad diversi tabelli u tuża veduta waħda (OperativeQuestions), li fiha l-affiljazzjoni tiġi ċċekkjata bl-email (permezz EŻISTI) għal “Mistoqsijiet Operattivi”:
Talba Nru 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])
));
Il-veduta għandha struttura pjuttost kumplessa: għandha subquery joins u tuża l-issortjar Distinti, li b'mod ġenerali hija operazzjoni pjuttost intensiva fir-riżorsi.
Kampjun minn OperativeQuestions huwa madwar għaxart elef rekord.
Il-problema ewlenija b'din il-mistoqsija hija li għar-rekords mill-mistoqsija ta' barra, tiġi esegwita subquery interna fuq il-veduta [OperativeQuestions], li għandha għal [Email] = @p__linq__0 nillimitaw l-għażla tal-output (permezz EŻISTI) sa mijiet ta' rekords.
U jista 'jidher li s-subquery għandha tikkalkula r-rekords darba minn [Email] = @p__linq__0, u mbagħad dawn il-koppja ta' mijiet ta 'rekords għandhom ikunu konnessi b'Id ma' Mistoqsijiet, u l-mistoqsija tkun veloċi.
Fil-fatt, hemm konnessjoni sekwenzjali tat-tabelli kollha: verifika tal-korrispondenza ta 'Mistoqsijiet Id ma' Id minn OperativeQuestions, u filtrazzjoni bl-Email.
Fil-fatt, it-talba taħdem ma 'l-għexieren ta' eluf ta 'rekords OperativeQuestions, iżda d-dejta ta' interess biss hija meħtieġa permezz tal-Email.
Operative Questions ara t-test:
Talba Nru 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));
F’dan il-każ partikolari, qed nikkunsidraw soluzzjoni għal din il-problema mingħajr bidliet infrastrutturali, mingħajr ma nintroduċu tabella separata b’riżultati lesti (“Mistoqsijiet Attivi”), li tkun teħtieġ mekkaniżmu biex timtela bid-dejta u tinżamm aġġornata. .
Għalkemm din hija soluzzjoni tajba, hemm għażla oħra biex din il-problema tiġi ottimizzata.
L-għan ewlieni huwa li l-entrati fil-cache minn [Email] = @p__linq__0 mill-vista OperativeQuestions.
Billi nibagħtu l-Email bħala parametru ta’ input, irridu lura tabella ta’ valuri:
Talba Nru 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
Dan jirritorna tabella ta 'valuri bi struttura ta' dejta predefinita.
Sabiex il-mistoqsijiet lil OperativeQuestionsUserMail ikunu ottimali u jkollhom pjanijiet ta' mistoqsijiet ottimali, hija meħtieġa struttura stretta, u mhux TABELLA RITORN BĦALA RITORN...
F'dan il-każ, il-Mistoqsija 1 meħtieġa tiġi kkonvertita f'Mistoqsija 4:
Talba Nru 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]);
Immappjar ta' fehmiet u funzjonijiet f'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})");
}
L-ordni tal-ħin ta 'eżekuzzjoni niżel minn 200-800 ms, għal 2-20 ms, eċċ., jiġifieri għexieren ta' drabi aktar malajr.
Jekk nieħduha b'mod aktar medju, allura minflok 350 ms sirna 8 ms.
Mill-vantaġġi ovvji nieħdu wkoll:
tnaqqis ġenerali fit-tagħbija tal-qari,
tnaqqis sinifikanti fil-probabbiltà ta 'imblukkar
it-tnaqqis tal-ħin medju tal-imblukkar għal valuri aċċettabbli
Output
Ottimizzazzjoni u rfinar tas-sejħiet tad-database MS SQL permezz LINQ hija problema li tista’ tissolva.
L-attenzjoni u l-konsistenza huma importanti ħafna f’dan ix-xogħol.
Fil-bidu tal-proċess:
huwa meħtieġ li tiċċekkja d-dejta li biha taħdem it-talba (valuri, tipi ta’ dejta magħżula)
twettaq indiċjar xieraq ta’ din id-dejta
iċċekkja l-korrettezza tal-kondizzjonijiet li jgħaqqdu bejn it-tabelli
L-iterazzjoni tal-ottimizzazzjoni li jmiss tiżvela:
bażi tat-talba u jiddefinixxi l-filtru prinċipali tat-talba
tirrepeti blokki ta 'mistoqsijiet simili u tanalizza l-intersezzjoni tal-kundizzjonijiet
f'SSMS jew GUI oħra għal SQL Server jottimizza lilu nnifsu Mistoqsija SQL (l-allokazzjoni ta' ħażna ta' data intermedja, il-bini tal-mistoqsija li tirriżulta bl-użu ta' din il-ħażna (jista' jkun hemm diversi))
fl-aħħar stadju, billi tieħu bħala bażi r-riżultat Mistoqsija SQL, l-istruttura qed tinbena mill-ġdid Mistoqsija LINQ
Ir-riżultat Mistoqsija LINQ għandhom isiru identiċi fl-istruttura għall-aħjar identifikat Mistoqsija SQL mill-punt 3.
Rikonoxximenti
Grazzi ħafna lill-kollegi jobgemws и alex_ozr mill-kumpanija Fortis għal għajnuna fil-preparazzjoni ta’ dan il-materjal.