ProHoster > Blogi > Haldamine > MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris

MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris

LINQ tuli .NET-i uue vĂ”imsana andmehalduse keele. LINQ to SQL vĂ”imaldab selle osana suhelda andmebaasidega, nĂ€iteks Entity Frameworki kaudu, ĂŒsna mugavalt. Siiski, seda kasutades unustavad arendajad tihti vaadata, millist SQL-pĂ€ringut genereerib queryable provider, teie puhul — Entity Framework.

Vaatame kaht pÔhiaspekti nÀite abil.
Selleks loome SQL Serveris andmebaasi Test ning seal loome kaks tabelit jÀrgmise pÀringuga:

Tabelite loomine

USE [TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Ref](
	[ID] [int] NOT NULL,
	[ID2] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Ref] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Ref] ADD  CONSTRAINT [DF_Ref_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

USE [TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
	[ID] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Ref_ID] [int] NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL,
	[Ref_ID2] [int] NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_Ref_ID]  DEFAULT ((0)) FOR [Ref_ID]
GO

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

NĂŒĂŒd tĂ€idame tabeli Ref jĂ€rgmise skripti kĂ€ivitamisega:

Tabeli Ref tÀitmine

USE [TEST]
GO

DECLARE @ind INT=1;

WHILE(@ind<1200000)
BEGIN
	INSERT INTO [dbo].[Ref]
           ([ID]
           ,[ID2]
           ,[Name])
    SELECT
           @ind
           ,@ind
           ,CAST(@ind AS NVARCHAR(255));

	SET @ind=@ind+1;
END 
GO

Samuti tÀidame tabeli Customer jÀrgmise skripti abil:

Tabeli Customer tÀitmine

KASUTA [TEST]
GO

DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;

WHILE(@ind<=12000000)
BEGIN
	IF(@ind%3=0) SET @ind_ref=1;
	ELSE IF (@ind%5=0) SET @ind_ref=2;
	ELSE IF (@ind%7=0) SET @ind_ref=3;
	ELSE IF (@ind=0) SET @ind_ref=4;
	ELSE IF (@ind=0) SET @ind_ref=5;
	ELSE IF (@ind=0) SET @ind_ref=6;
	ELSE IF (@ind=0) SET @ind_ref=7;
	ELSE IF (@ind=0) SET @ind_ref=8;
	ELSE IF (@ind=0) SET @ind_ref=9;
	ELSE IF (@ind=0) SET @ind_ref=10;
	ELSE IF (@ind=0) SET @ind_ref=11;
	ELSE SET @ind_ref=@ind90000;
	
	INSERT INTO [dbo].[Customer]
	 ([ID]
	 ,[Name]
	 ,[Ref_ID]
	 ,[Ref_ID2])
	 SELECT
	 @ind,
	 CAST(@ind AS NVARCHAR(255)),
	 @ind_ref,
	 @ind_ref;


	SET @ind=@ind+1;
END
GO

Nii saime kaks tabelit, milles ĂŒhes on ĂŒle 1 miljoni andmerea ja teises ĂŒle 10 miljoni andmerea.

NĂŒĂŒd on Visual Studio-s vajalik luua testprojekt Visual C# Console App (.NET Framework):

MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris

Edasi tuleb andmebaasiga suhtlemiseks lisada Entity Frameworki teek.
Selle lisamiseks klĂ”psame projektil parema hiireklahviga ja valime kontekstimenĂŒĂŒst Manage NuGet Packages:

MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris

SeejĂ€rel sisestame avanevas NuGet-pakettide haldamise aknas otsinguvĂ€ljale sĂ”na „Entity Framework” ja valime paketi Entity Framework ning installime selle:

MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris

JÀrgmisena peame faili App.config pÀrast elemendi configSections sulgemist lisama jÀrgmise ploki:

connectionString'i tuleb sisestada ĂŒhenduse string.

NĂŒĂŒd loome kolmes eraldi failis 3 liidest:

  1. Liidese IBaseEntityID rakendus
    namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

  2. Liidese IBaseEntityName rakendus
    namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

  3. Liidese IBaseNameInsertUTCDate rakendus
    namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

Ja eraldi failis loome pĂ”hiklasi BaseEntity meie kahe entiteedi jaoks, kuhu kuuluvad ĂŒhised vĂ€ljad:

PÔhiklassi BaseEntity rakendus

namespace TestLINQ
{
    public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public DateTime InsertUTCDate { get; set; }
    }
}

SeejÀrel loome eraldi failidesse meie kaks entiteeti:

  1. Klassi Ref rakendus
    using System.ComponentModel.DataAnnotations.Schema;

namespace TestLINQ
{
    [Table("Ref")]
    public class Ref : BaseEntity
    {
        public int ID2 { get; set; }
    }
}

  2. Klassi Customer rakendus
    using System.ComponentModel.DataAnnotations.Schema;

namespace TestLINQ
{
    [Table("Customer")]
    public class Customer: BaseEntity
    {
        public int Ref_ID { get; set; }
        public int Ref_ID2 { get; set; }
    }
}

NĂŒĂŒd loome eraldi failis konteksti UserContext:

Klassi UserContext rakendus

using System.Data.Entity;

namespace TestLINQ
{
    public class UserContext : DbContext
    {
        public UserContext()
            : base("DbConnection")
        {
            Database.SetInitializer(null);
        }

        public DbSet Customer { get; set; }
        public DbSet Ref { get; set; }
    }
}

Oleme saanud valmis lahenduse testide lÀbiviimiseks optimeerimise osas LINQ to SQL kaudu EF jaoks MS SQL Serveris:

MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris

NĂŒĂŒd sisestame faili Program.cs jĂ€rgmise koodi:

Fail Program.cs

kasutades System;
kasutades System.Collections.Generic;
kasutades System.Linq;

(nimi TestLINQ
{
    klass Program
    {
        staatiline void Main(string[] args)
        {
            kasutades (UserContext db = new UserContext())
            {
                var dblog = new List();
                db.Database.Log = dblog.Add;

                var query = from e1 in db.Customer
                            from e2 in db.Ref
                            where (e1.Ref_ID == e2.ID)
                                 && (e1.Ref_ID2 == e2.ID2)
                            select new { Data1 = e1.Name, Data2 = e2.Name };

                var result = query.Take(1000).ToList();

                Console.WriteLine(dblog[1]);

                Console.ReadKey();
            }
        }
    }
}

JÀtkame meie projekti kÀivitamist.

Töö lÔpetamisel prinditakse konsoolile:

Genereeritud SQL-pÀring

SELECT TOP (1000) 
    [Extent1].[Ref_ID] AS [Ref_ID], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Customer] AS [Extent1]
    INNER JOIN [dbo].[Ref] AS [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) AND ([Extent1].[Ref_ID2] = [Extent2].[ID2])

See tĂ€hendab, et kokkuvĂ”ttes genereeris LINQ pĂ€ring ĂŒsna korraliku SQL-pĂ€ringu MS SQL Serveri andmebaasile.

NĂŒĂŒd muutke LINQ-pĂ€ringus JAH tingimus EI asemel.

LINQ-pÀring

var query = from e1 in db.Customer
                            from e2 in db.Ref
                            where (e1.Ref_ID == e2.ID)
                                || (e1.Ref_ID2 == e2.ID2)
                            select new { Data1 = e1.Name, Data2 = e2.Name };

Ja kÀivitame taas oma rakenduse.

TĂ€ideviimisel ilmneb viga, mis on seotud kĂ€su tĂ€itmisaja ĂŒletamisega 30 sekundi jooksul:

MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris

Kui vaatamata sellele, milline pÀring on LINQ-i poolt genereeritud.

MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris
, siis vÔib veenduda, et valik toimub kahe hulga (tabeli) kartesiaansest korrutamisest:

Genereeritud SQL-pÀring

SELECT TOP (1000) 
    [Extent1].[Ref_ID] AS [Ref_ID], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Customer] AS [Extent1]
    CROSS JOIN [dbo].[Ref] AS [Extent2]
    WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]

Üks vĂ”imalus on kirjutada LINQ-pĂ€ring jĂ€rgmiselt:

Optimeeritud LINQ-pÀring

var query = (from e1 in db.Customer
                   join e2 in db.Ref
                   on e1.Ref_ID equals e2.ID
                   select new { Data1 = e1.Name, Data2 = e2.Name }).Union(
                        from e1 in db.Customer
                        join e2 in db.Ref
                        on e1.Ref_ID2 equals e2.ID2
                        select new { Data1 = e1.Name, Data2 = e2.Name });

Siis saame jÀrgmise SQL-pÀringu:

SQL-pÀring

VALISELECT 
    [Limit1].[C1] AS [C1], 
    [Limit1].[C2] AS [C2], 
    [Limit1].[C3] AS [C3]
    FROM ( SELECT DISTINCT TOP (1000) 
        [UnionAll1].[C1] AS [C1], 
        [UnionAll1].[Name] AS [C2], 
        [UnionAll1].[Name1] AS [C3]
        FROM  (SELECT 
            1 AS [C1], 
            [Extent1].[Name] AS [Name], 
            [Extent2].[Name] AS [Name1]
            FROM  [dbo].[Customer] AS [Extent1]
            INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
        UNION ALL
            SELECT 
            1 AS [C1], 
            [Extent3].[Name] AS [Name], 
            [Extent4].[Name] AS [Name1]
            FROM  [dbo].[Customer] AS [Extent3]
            INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
    )  AS [Limit1]

Kahjuks LINQ-kĂŒsimustes vĂ”ib liitumise tingimus olla ainult ĂŒks, seega on vĂ”imalik teha ekvivalentne pĂ€ring kahe pĂ€ringuga, et iga tingimus eraldi ja seejĂ€rel ĂŒhendada need Unioniga, et eemaldada ridade vahelised duplikaadid.
Jah, pĂ€ringud on eriti juhul mitteekvivalentsed, kuna ka tĂ€ielikud duplikaadid vĂ”ivad tagasi tulla. Siiski, reaalses elus ei ole tĂ€ielikud duplikaadid vajalikud ja neist pĂŒĂŒtakse vabaneda.

VĂ”rdleme nĂŒĂŒd nende kahe pĂ€ringu tĂ€itmise plaane:

  1. CROSS JOINi keskmine tÀitmise aeg on 195 sek:
    MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris
  2. INNER JOIN-UNIONi keskmine tÀitmise aeg on alla 24 sek:
    MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris

Nagu nÀhtub tulemustest, töötab kahe miljoni kirje puhul optimeeritud LINQ-pÀring kordades kiiremini kui mitteoptimeeritud.

Jaotisega, kus on ja LINQ-pÀringu tingimus:

LINQ-pÀring

var query = from e1 in db.Customer
                            from e2 in db.Ref
                            where (e1.Ref_ID == e2.ID)
                                 && (e1.Ref_ID2 == e2.ID2)
                            select new { Data1 = e1.Name, Data2 = e2.Name };

peaaegu alati genereeritakse Ôige SQL-pÀring, mille tÀitmise keskmine on umbes 1 sek:

MÔned aspektid LINQ-pÀringute optimeerimisest C#.NET jaoks MS SQL Serveris
Samuti LINQ to Objects manipuleerimise korral vÔite kasutada jÀrgmist pÀringut:

LINQ-pÀring (1. variant)

var query = from e1 in seq1
                            from e2 in seq2
                            where (e1.Key1==e2.Key1)
                               && (e1.Key2==e2.Key2)
                            select new { Data1 = e1.Data, Data2 = e2.Data };

vÔite kasutada jÀrgnevat pÀringut:

LINQ-pÀring (2. variant)

var query = from e1 in seq1
                            join e2 in seq2
                            on new { e1.Key1, e1.Key2 } equals new { e2.Key1, e2.Key2 }
                            select new { Data1 = e1.Data, Data2 = e2.Data };

kus:

Kahes massiivi mÀÀratlemine

Para[] seq1 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 4, Data = "999" } };
Para[] seq2 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 5, Data = "999" } };

, ja tĂŒĂŒpi Para mÀÀratlemine on jĂ€rgmine:

TĂŒĂŒbi Para mÀÀratlemine

class Para
{
        public int Key1, Key2;
        public string Data;
}

Nii, me oleme vaadanud mÔningaid aspekte LINQ-pÀringute optimeerimisel MS SQL Serveri jaoks.

Kahjuks unustavad isegi kogenud ja juhtivad .NET-arendajad, et nad peavad aru saama, mida teevad nende kasutatavad kĂ€sud. Muul juhul muutuvad nad seadistajateks ja vĂ”ivad tulevikus, nii suurendades programmirakendust kui ka vĂ€ikesi keskkonna muutusi, kĂŒlvata aja pommi.

Samuti tehti vĂ€ikene ĂŒlevaade ja siin.

Allikad testimiseks - projekt, tabelite loomine andmebaasis TEST, ning nende tabelite andmetega tÀitmine on siin.
Samuti asub selles repositooriumis kaustas Plans pÀringute tÀitmise plaanid, millel on tingimused OR.

Allikas: habr.com

Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid | ProHoster