🥇 LINQ kontsultak C#.NET-en optimizatzearen alderdi batzuk MS SQL Serverrako

LINQ-ek .NET sartu zuen datuak manipulatzeko lengoaia berri indartsu gisa. LINQ to SQL horren zati gisa, DBMS batekin nahiko eroso komunikatzeko aukera ematen dizu, adibidez, Entity Framework erabiliz. Hala eta guztiz ere, sarritan erabiliz, garatzaileek ahaztu egiten dute kontsulta daitekeen hornitzaileak, zure kasuan Entity Framework-ek, zer nolako SQL kontsulta sortuko duen.

Ikus ditzagun bi puntu nagusi adibide bat erabiliz.
Horretarako, sortu Test datu-base bat SQL Server-en eta sortu bi taula bertan kontsulta hau erabiliz:

Taulak sortzea

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

Orain bete dezagun Erref taula script hau exekutatuz:

Erref taula betetzea

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

Era berean, bete dezagun Bezeroaren taula script hau erabiliz:

Bezeroen taula betetzea

USE [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%11=0) SET @ind_ref=4;
	ELSE IF (@ind%13=0) SET @ind_ref=5;
	ELSE IF (@ind%17=0) SET @ind_ref=6;
	ELSE IF (@ind%19=0) SET @ind_ref=7;
	ELSE IF (@ind%23=0) SET @ind_ref=8;
	ELSE IF (@ind%29=0) SET @ind_ref=9;
	ELSE IF (@ind%31=0) SET @ind_ref=10;
	ELSE IF (@ind%37=0) SET @ind_ref=11;
	ELSE SET @ind_ref=@ind%1190000;
	
	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

Horrela, bi taula jaso ditugu, horietako batek milioi bat datu-errenkada baino gehiago ditu, eta besteak 1 milioi errenkada baino gehiago ditu.

Orain Visual Studio-n probako Visual C# Console App (.NET Framework) proiektu bat sortu behar duzu:

Ondoren, liburutegi bat gehitu behar duzu Entity Framework-ek datu-basearekin elkarreragiteko.
Gehitzeko, egin klik eskuineko botoiarekin proiektuan eta hautatu Kudeatu NuGet paketeak laster-menuan:

Ondoren, agertzen den NuGet paketeak kudeatzeko leihoan, idatzi "Entity Framework" hitza bilaketa-leihoan eta hautatu Entity Framework paketea eta instalatu:

Ondoren, App.config fitxategian, configSections elementua itxi ondoren, bloke hau gehitu behar duzu:

<connectionStrings>
    <add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

connectionString-en konexio-katea sartu behar duzu.

Orain sor ditzagun 3 interfaze fitxategi bereizietan:

IBaseEntityID interfazea ezartzea

namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

IBaseEntityName interfazearen ezarpena

namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

IBaseNameInsertUTCDate interfazearen ezarpena

namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

Eta aparteko fitxategi batean BaseEntity oinarrizko klase bat sortuko dugu gure bi entitateentzat, eremu komunak barne hartuko dituena:

BaseEntity oinarrizko klasearen inplementazioa

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

Ondoren, gure bi entitateak fitxategi bereizietan sortuko ditugu:

Erref klasearen ezarpena

using System.ComponentModel.DataAnnotations.Schema;

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

Bezero klasearen ezarpena

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; }
    }
}

Orain sortu dezagun UserContext testuingurua fitxategi bereizi batean:

UserContex klasearen inplementazioa

using System.Data.Entity;

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

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

LINQ to SQL-rekin optimizazio-probak egiteko prest dagoen irtenbide bat jaso dugu EF bidez MS SQL Server-erako:

Orain sartu hurrengo kodea Program.cs fitxategian:

Program.cs fitxategia

using System;
using System.Collections.Generic;
using System.Linq;

namespace TestLINQ
{
    class Program
    {
        static void Main(string[] args)
        {
            using (UserContext db = new UserContext())
            {
                var dblog = new List<string>();
                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();
            }
        }
    }
}

Jarraian, abiarazi dezagun gure proiektua.

Lanaren amaieran, honako hauek bistaratuko dira kontsolan:

Sortutako SQL kontsulta

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

Hau da, oro har, LINQ kontsultak SQL kontsulta bat sortu zuen MS SQL Server DBMS-rako.

Orain alda dezagun AND baldintza ORra LINQ kontsultan:

LINQ kontsulta

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 };

Eta abiarazi dezagun berriro gure aplikazioa.

Exekuzioa errore batekin huts egingo da komandoa exekutatzeko denbora 30 segundotik gorakoa dela eta:

LINQ-ek sortutako kontsultari begiratzen badiozu:

, orduan ziurta dezakezu hautaketa bi multzoren (taulen) produktu cartesiarren bidez egiten dela:

Sortutako SQL kontsulta

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]

Berridatzi dezagun LINQ kontsulta honela:

LINQ kontsulta optimizatua

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

Ondoren, SQL kontsulta hau lortuko dugu:

SQL kontsulta

SELECT 
    [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]

Ala ere, LINQ kontsultetan elkartze-baldintza bakarra egon daiteke, beraz, hemen kontsulta baliokide bat egin daiteke baldintza bakoitzerako bi kontsulta erabiliz eta gero Union bidez konbinatuz errenkaden artean bikoiztuak kentzeko.
Bai, orokorrean kontsultak ez dira baliokideak izango, errenkada bikoiztu osoak itzul daitezkeela kontuan hartuta. Hala ere, bizitza errealean, lerro bikoiztu osoak ez dira behar eta jendea horiek kentzen saiatzen da.

Orain aldera ditzagun bi kontsulta hauen exekuzio-planak:

CROSS JOIN-en batez besteko exekuzio-denbora 195 segundokoa da:
INNER JOIN-UNION-en batez besteko exekuzio-denbora 24 segundo baino txikiagoa da:

Emaitzetan ikus dezakezunez, milioika erregistro dituzten bi tauletarako, optimizatutako LINQ kontsulta optimizatu gabekoa baino askoz ere azkarragoa da.

Baldintzetan ETA duen aukerarako, formako LINQ kontsulta bat:

LINQ kontsulta

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 };

SQL kontsulta zuzena ia beti sortuko da, batez beste segundo batean exekutatzen dena:

Baita LINQ to Objects manipulazioetarako ere kontsulta baten ordez:

LINQ kontsulta (1. aukera)

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 };

honelako kontsulta bat erabil dezakezu:

LINQ kontsulta (2. aukera)

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 };

non:

Bi array definitzea

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" } };

, eta Para mota honela definitzen da:

Para Mota Definizioa

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

Horrela, MS SQL Server-erako LINQ kontsultak optimizatzeko alderdi batzuk aztertu ditugu.

Zoritxarrez, .NET garatzaile adituek eta nagusiek ere ahazten dute erabiltzen dituzten argibideek eszena atzean zer egiten duten ulertu behar dutela. Bestela, konfiguratzaile bihurtzen dira eta erloju-bonba bat jar dezakete etorkizunean, bai software-soluzioa eskalatzerakoan, bai kanpoko ingurune-baldintzetan aldaketa txikiekin.

Berrikuspen labur bat ere egin zen Hemen.

Proba egiteko iturriak - proiektua bera, TEST datu-basean taulak sortzea eta taula hauek datuez betetzea kokatzen dira. Hemen.
Biltegi honetan ere, Planak karpetan, EDO baldintzekin kontsultak egiteko planak daude.

Iturria: www.habr.com

C#.NET-en LINQ kontsultak optimizatzeko alderdi batzuk MS SQL Server-erako

Gehitu iruzkin berria Idatzi