ProHoster > Блог > Gudanarwa > Hanyoyin inganta tambayoyin LINQ a cikin C#.NET

Hanyoyin inganta tambayoyin LINQ a cikin C#.NET

Gabatarwar

В wannan labarin an yi la'akari da wasu hanyoyin ingantawa Tambayoyin LINQ.
Anan kuma mun gabatar da wasu ƙarin hanyoyin inganta lamba masu alaƙa da su Tambayoyin LINQ.

An san wannan LINQ(Tambayoyin Haɗaɗɗen Harshe) harshe ne mai sauƙi kuma mai dacewa don neman tushen bayanai.

А LINQ zuwa SQL fasaha ce don samun damar bayanai a cikin DBMS. Wannan kayan aiki ne mai ƙarfi don aiki tare da bayanai, inda ake gina tambayoyi ta hanyar bayyana harshe, wanda za'a canza shi zuwa Tambayoyin SQL dandamali kuma aika zuwa uwar garken bayanai don aiwatarwa. A cikin yanayinmu, da DBMS muke nufi MS SQL Server.

Duk da haka, Tambayoyin LINQ ba a canza su zuwa mafi kyawun rubuce-rubuce Tambayoyin SQL, wanda ƙwararren DBA zai iya rubutawa tare da duk nuances na ingantawa Tambayoyin SQL:

  1. mafi kyawun haɗin gwiwa (JIIN) da tace sakamakon (INA)
  2. nuances da yawa a cikin amfani da haɗin gwiwa da yanayin rukuni
  3. bambance-bambancen da yawa a cikin maye gurbin yanayi IN a kan SAURARAи BA IN, <> kuma SAURARA
  4. matsakaita caching na sakamako ta hanyar tebur na wucin gadi, CTE, masu canjin tebur
  5. amfani da jumla (OPTION) tare da umarni da alamun tebur WITH (...)
  6. ta yin amfani da ra'ayoyi masu ma'ana a matsayin ɗaya daga cikin hanyoyin kawar da yawan karatun bayanai yayin zaɓe

Babban ƙwanƙolin aiki na sakamakon Tambayoyin SQL lokacin hadawa Tambayoyin LINQ su ne:

  1. ƙarfafa dukkan tsarin zaɓin bayanai a cikin buƙatu ɗaya
  2. kwafi nau'ikan tubalan code, wanda a ƙarshe yana haifar da karanta bayanan da ba dole ba
  3. ƙungiyoyin yanayi masu yawa (ma'ana "da" da "ko") - KUMA и OR, hadawa cikin hadaddun yanayi, yana kaiwa ga gaskiyar cewa mai ingantawa, yana da alamun da ba a haɗa su ba don filayen da ake buƙata, a ƙarshe ya fara yin bincike a kan maƙasudin clustered (SCANNIN INDEX) ta ƙungiyoyin yanayi
  4. zurfafa zurfafa bincike na subqueries yana haifar da matsala sosai Rahoton da aka ƙayyade na SQL da kuma nazarin tsarin tambaya a ɓangaren masu haɓakawa da DBA

Hanyoyin ingantawa

Yanzu bari mu matsa kai tsaye zuwa hanyoyin ingantawa.

1) Ƙarin ƙididdiga

Zai fi kyau a yi la'akari da masu tacewa a kan babban tebur ɗin zaɓi, tunda galibi ana gina duk tambayar a kusa da manyan teburi ɗaya ko biyu (aiki-aiki-mutane) kuma tare da daidaitaccen saiti na yanayi (An Rufe, An soke, An kunna, Matsayi). Yana da mahimmanci don ƙirƙirar ƙididdiga masu dacewa don samfuran da aka gano.

Wannan bayani yana da ma'ana yayin zabar waɗannan filayen yana iyakance saitin da aka dawo da shi sosai.

Misali, muna da aikace-aikace 500000. Koyaya, akwai kawai 2000 aikace-aikace masu aiki. Sa'an nan zaɓaɓɓen fihirisar da aka zaɓa daidai zai cece mu daga SCANNIN INDEX a kan babban tebur kuma zai ba ka damar zaɓar bayanai da sauri ta hanyar fihirisar da ba ta tari ba.

Hakanan, ana iya gano ƙarancin fihirisa ta hanyar faɗakarwa don tantance tsare-tsaren tambaya ko tattara ƙididdigar duba tsarin. MS SQL Server:

  1. sys.dm_db_missing_index_groups
  2. sys.dm_db_missing_index_group_stats
  3. sys.dm_db_missing_index_details

Duk bayanan duba sun ƙunshi bayanai game da bacewar fihirisa, ban da fihirisar sararin samaniya.

Koyaya, fihirisa da caching galibi hanyoyin magance illar rashin rubutu Tambayoyin LINQ и Tambayoyin SQL.

Kamar yadda mummunan aikin rayuwa ya nuna, sau da yawa yana da mahimmanci ga kasuwanci don aiwatar da fasalin kasuwanci ta wasu ƙayyadaddun lokaci. Sabili da haka, yawancin buƙatun buƙatun ana canza su zuwa bango tare da caching.

Wannan wani bangare ne na barata, tunda mai amfani ba koyaushe yana buƙatar sabbin bayanai ba kuma akwai matakin karɓuwa na jin daɗin haɗin mai amfani.

Wannan tsarin yana ba da damar warware bukatun kasuwanci, amma a ƙarshe yana rage aikin tsarin bayanai ta hanyar jinkirta mafita ga matsaloli kawai.

Har ila yau, yana da daraja tunawa da cewa a cikin aiwatar da binciken da ake bukata don ƙarawa, shawarwari Bayanan MSSQL ingantawa na iya zama kuskure, gami da ƙarƙashin waɗannan sharuɗɗa:

  1. idan an riga an sami fihirisa masu irin wannan saitin filayen
  2. idan filayen da ke cikin tebur ba za a iya ƙididdige su ba saboda ƙayyadaddun ƙididdiga (an kwatanta su daki-daki a nan).

2) Haɗa halayen zuwa sabon sifa ɗaya

Wani lokaci wasu filaye daga tebur ɗaya, waɗanda ke zama tushen rukunin yanayi, ana iya maye gurbinsu ta hanyar gabatar da sabon filin guda.

Wannan gaskiya ne musamman ga filayen matsayi, waɗanda yawanci ko dai bit ko lamba a nau'in.

Alal misali:

An Rufe = 0 KUMA An soke = 0 DA An kunna = 0 an maye gurbinsa da Matsayi = 1.

Anan ne aka gabatar da sifa ta lamba don tabbatar da cewa an cika waɗannan ma'auni a cikin tebur. Bayan haka, wannan sabon sifa ana fidda shi.

Wannan shine ainihin mafita ga matsalar aiki, saboda Muna samun damar bayanai ba tare da lissafin da ba dole ba.

3) Materialization na gani

Abin takaici a Tambayoyin LINQ Ba za a iya amfani da tebur na wucin gadi, CTEs, da masu canjin tebur ba kai tsaye.

Duk da haka, akwai wata hanyar da za a inganta don wannan harka - ra'ayoyi masu ma'ana.

Ƙungiyar yanayi (daga misalin sama) An Rufe = 0 KUMA An soke = 0 DA An kunna = 0 (ko saitin wasu yanayi iri ɗaya) ya zama zaɓi mai kyau don amfani da su a cikin ra'ayi mai ƙididdiga, adana ƙaramin yanki na bayanai daga babban saiti.

Amma akwai hani da yawa lokacin da ake yin ra'ayi:

  1. amfani da subqueries, clauses SAURARA ya kamata a maye gurbinsu ta hanyar amfani JIIN
  2. ba za ku iya amfani da jimloli ba UNION, UNION DUK, RABEWA, HANKALI
  3. Ba za ku iya amfani da alamun tebur da jumla ba OPTION
  4. babu yiwuwar yin aiki tare da hawan keke
  5. Ba shi yiwuwa a nuna bayanai a cikin ra'ayi ɗaya daga tebur daban-daban

Yana da mahimmanci a tuna cewa ainihin fa'idar yin amfani da ra'ayi mai ƙididdiga za a iya samu ta hanyar ƙididdige shi kawai.

Amma lokacin kiran kallo, ƙila ba za a yi amfani da waɗannan fihirisar ba, kuma don amfani da su a sarari, dole ne a saka. TARE DA (NOEXPAND).

Tun daga ciki Tambayoyin LINQ Ba shi yiwuwa a ayyana alamun tebur, don haka dole ne ku ƙirƙiri wani wakilci - “nannade” na nau'i mai zuwa:

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

4) Amfani da ayyukan tebur

Sau da yawa a cikin Tambayoyin LINQ Manya-manyan tubalan bincike ko tubalan ta yin amfani da ra'ayoyi tare da tsari mai rikitarwa suna samar da tambaya ta ƙarshe tare da tsarin aiwatarwa mai sarƙaƙƙiya kuma mafi ƙarancin ƙima.

Babban Fa'idodin Amfani da Ayyukan Tebura a Tambayoyin LINQ:

  1. Ƙarfin, kamar yadda yake a cikin yanayin ra'ayi, don amfani da ƙayyadaddun abu azaman abu, amma kuna iya wuce saitin sigogin shigarwa:
    DAGA AIKI(@param1, @param2 ...)
    A sakamakon haka, za a iya samun samfurin bayanai masu sassauƙa
  2. Game da yin amfani da aikin tebur, babu wasu ƙaƙƙarfan hani kamar na ra'ayi da aka kwatanta a sama:
    1. Alamun tebur:
      ta hanyar LINQ Ba za ku iya ƙididdige waɗanne fihirisa ya kamata a yi amfani da su ba kuma ku ƙayyade matakin keɓe bayanai lokacin tambaya.
      Amma aikin yana da waɗannan damar.
      Tare da aikin, zaku iya cimma daidaitaccen tsarin neman aiwatar da aiwatarwa, inda aka ayyana ƙa'idodin aiki tare da firikwensin da matakan keɓe bayanai.
    2. Amfani da aikin yana ba da damar, idan aka kwatanta da ra'ayoyin da aka lissafta, don samun:
      • hadaddun bayanan samfur dabaru (ko da amfani da madaukai)
      • tattara bayanai daga teburi daban-daban
      • da yin amfani da UNION и SAURARA

  3. Bayarwa OPTION yana da amfani sosai lokacin da muke buƙatar samar da kulawar haɗin gwiwa ZABI (MAXDOP N), odar tsarin aiwatar da tambaya. Misali:
    • za ka iya ƙirƙira tilasta sake ƙirƙirar shirin tambaya ZABI (SAKI)
    • za ka iya tantance ko za a tilasta shirin tambaya don amfani da odar haɗin kai da aka kayyade a cikin tambayar ZABI ( ODAR ARZIKI)

    Karin bayani game da OPTION aka bayyana a nan.

  4. Amfani da mafi ƙanƙanta kuma mafi yawan buƙatu yanki na bayanai:
    Babu buƙatar adana manyan bayanan bayanai a cikin caches (kamar yadda lamarin yake tare da ra'ayoyi masu ƙididdiga), wanda har yanzu kuna buƙatar tace bayanan ta hanyar sigina.
    Misali, akwai tebur wanda tacewa INA ana amfani da filayen guda uku (a, b, c).

    A al'ada, duk buƙatun suna da yanayin dindindin a = 0 da b = 0.

    Koyaya, buƙatar filin c mafi m.

    Bari yanayin a = 0 da b = 0 Yana taimaka mana da gaske don iyakance saitin sakamakon da ake buƙata zuwa dubunnan bayanan, amma yanayin yana kan с yana rage zaɓin zuwa rikodin rikodin ɗari.

    Anan aikin tebur na iya zama mafi kyawun zaɓi.

    Hakanan, aikin tebur ya fi tsinkaya kuma akai-akai a lokacin aiwatarwa.

misalai

Bari mu kalli aiwatar da misali ta amfani da bayanan Tambayoyi a matsayin misali.

Akwai bukata Select, wanda ke haɗa tebur da yawa kuma yana amfani da ra'ayi ɗaya (OperativeQuestions), wanda ake bincika alaƙa ta imel (ta hanyar imel). SAURARA) zuwa "Tambayoyin Aiki":

Neman 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])
));

Ra'ayi yana da tsari mai rikitarwa: yana da haɗin kai kuma yana amfani da rarrabuwa DISTINCT, wanda gabaɗaya aiki ne mai cike da albarkatu.

Samfurin daga OperativeQuestions shine kusan bayanai dubu goma.

Babban matsalar wannan tambayar ita ce don bayanan da ke fitowa daga tambayar waje, ana aiwatar da subquery na ciki akan kallon [OperativeQuestions], wanda ya kamata don [Email] = @p__linq__0 ya ba mu damar iyakance zaɓin fitarwa (ta. SAURARA) har zuwa daruruwan bayanai.

Kuma yana iya zama kamar mai binciken ya kamata ya lissafta bayanan sau ɗaya ta [Email] = @p__linq__0, sa'an nan kuma waɗannan guda ɗari biyu za a haɗa su da Id tare da Tambayoyi, kuma tambayar za ta yi sauri.

A haƙiƙa, akwai hanyar haɗin kai ta kowane tebur: bincika saƙonnin Tambayoyin Id tare da Id daga Tambayoyin Aiki, da tacewa ta Imel.

A zahiri, buƙatar tana aiki tare da duk dubun-dubatar bayanan Tambayoyi na Operative, amma kawai bayanan ban sha'awa ana buƙata ta Imel.

Tambayoyi masu aiki duba rubutu:

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

Taswirar kallon farko a cikin 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");
    }
}

Tambayar LINQ ta farko

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

A wannan yanayin, muna yin la'akari da mafita ga wannan matsala ba tare da sauye-sauye na kayan aiki ba, ba tare da gabatar da wani tebur na daban tare da sakamakon da aka shirya ("Queries Active"), wanda zai buƙaci hanyar da za a cika shi da bayanai da kuma ci gaba da sabuntawa. .

Kodayake wannan mafita ce mai kyau, akwai wani zaɓi don inganta wannan matsala.

Babban maƙasudin shine a adana shigarwar ta [Email] = @p__linq__0 daga duban Tambayoyi na Operative.

Gabatar da aikin tebur [dbo] [OperativeQuestionsUserMail] a cikin bayanan bayanai.

Ta hanyar aika imel a matsayin sigar shigarwa, muna dawo da tebur na ƙimar:

Neman 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

Wannan yana dawo da tebur na ƙima tare da ƙayyadaddun tsarin bayanai.

Domin tambayoyin OperativeQuestionsUserMail su zama mafi kyau kuma suna da ingantattun tsare-tsaren tambaya, ana buƙatar tsayayyen tsari, kuma ba MAYARWA TABUN KAMAR KOMA...

A wannan yanayin, tambayar da ake buƙata ta 1 tana jujjuya zuwa Tambaya 4:

Neman 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]);

Ra'ayoyin taswira da ayyuka a cikin 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})");
}

Tambayar LINQ ta ƙarshe

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

Tsarin lokacin aiwatarwa ya ragu daga 200-800 ms, zuwa 2-20 ms, da sauransu, watau sau goma cikin sauri.

Idan muka ɗauki fiye da matsakaici, to, maimakon 350 ms mun sami 8 ms.

Daga fa'idodin bayyane kuma muna samun:

  1. Gaba ɗaya rage nauyin karatu,
  2. gagarumin raguwa a cikin yiwuwar toshewa
  3. rage matsakaicin lokacin toshewa zuwa ƙimar karɓuwa

ƙarshe

Haɓakawa da daidaitawa na kiran bayanai Bayanan MSSQL ta hanyar LINQ matsala ce da za a iya magance ta.

Hankali da daidaito suna da mahimmanci a cikin wannan aikin.

A farkon tsari:

  1. Wajibi ne a bincika bayanan da buƙatun ke aiki da su (darajar, nau'ikan bayanan da aka zaɓa)
  2. gudanar da ingantaccen fihirisar wannan bayanai
  3. duba daidaiton yanayin haɗawa tsakanin tebur

Na gaba inganta haɓakawa yana bayyana:

  1. tushen buƙatun kuma yana ayyana babban tace buƙatun
  2. maimaita irin wannan tubalan tambaya da kuma nazarin mahadar yanayi
  3. a cikin SSMS ko wasu GUI don SQL Server inganta kanta Tambayar SQL (waɗanda matsakaiciyar ajiyar bayanai, gina sakamakon binciken ta amfani da wannan ma'adana (akwai da yawa))
  4. a mataki na ƙarshe, ɗaukar a matsayin tushen sakamakon Tambayar SQL, ana sake gina ginin Tambayar LINQ

Sakamakon Tambayar LINQ ya kamata ya zama iri ɗaya a cikin tsari zuwa wanda aka gano mafi kyau Tambayar SQL daga aya 3.

Godiya

Godiya da yawa ga abokan aiki jobgemws и alex_ozr daga kamfanin Fortis don taimako wajen shirya wannan kayan.

source: www.habr.com

Add a comment