Ilang aspeto ng pag-optimize ng mga query sa LINQ sa C#.NET para sa MS SQL Server

Ipinasok ng LINQ ang .NET bilang isang malakas na bagong wika sa pagmamanipula ng data. Ang LINQ sa SQL bilang bahagi nito ay nagbibigay-daan sa iyong makipag-usap nang maginhawa sa isang DBMS gamit, halimbawa, Entity Framework. Gayunpaman, madalas itong ginagamit, nalilimutan ng mga developer na tingnan kung anong uri ng query sa SQL ang bubuo ng na-query na provider, sa iyong kaso Entity Framework.

Tingnan natin ang dalawang pangunahing punto gamit ang isang halimbawa.
Upang gawin ito, lumikha ng isang Test database sa SQL Server, at lumikha ng dalawang talahanayan dito gamit ang sumusunod na query:

Paglikha ng mga talahanayan

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

Ngayon, punan natin ang talahanayan ng Ref sa pamamagitan ng pagpapatakbo ng sumusunod na script:

Pinuno ang Ref table

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

Punan natin ang talahanayan ng Customer gamit ang sumusunod na script:

Populating ang Customer table

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

Kaya, nakatanggap kami ng dalawang talahanayan, ang isa ay may higit sa 1 milyong mga hilera ng data, at ang isa ay may higit sa 10 milyong mga hilera ng data.

Ngayon sa Visual Studio kailangan mong lumikha ng isang pagsubok na Visual C# Console App (.NET Framework) na proyekto:

Susunod, kailangan mong magdagdag ng library para sa Entity Framework upang makipag-ugnayan sa database.
Upang idagdag ito, mag-right-click sa proyekto at piliin ang Manage NuGet Packages mula sa menu ng konteksto:

Pagkatapos, sa lalabas na window ng pamamahala ng package ng NuGet, ipasok ang salitang "Entity Framework" sa window ng paghahanap at piliin ang Entity Framework package at i-install ito:

Susunod, sa App.config file, pagkatapos isara ang elemento ng configSections, kailangan mong idagdag ang sumusunod na bloke:

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

Sa connectionString kailangan mong ipasok ang connection string.

Ngayon gumawa tayo ng 3 mga interface sa magkahiwalay na mga file:

1. Pagpapatupad ng interface ng IBaseEntityID

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

2. Pagpapatupad ng interface ng IBaseEntityName

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

3. Pagpapatupad ng interface ng IBaseNameInsertUTCDate

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

At sa isang hiwalay na file gagawa kami ng base class na BaseEntity para sa aming dalawang entity, na magsasama ng mga karaniwang field:

Pagpapatupad ng base class na BaseEntity

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

Susunod, gagawa kami ng aming dalawang entity sa magkahiwalay na mga file:

1. Pagpapatupad ng klase ng Ref

   using System.ComponentModel.DataAnnotations.Schema;
   
   namespace TestLINQ
   {
       [Table("Ref")]
       public class Ref : BaseEntity
       {
           public int ID2 { get; set; }
       }
   }
   

2. Pagpapatupad ng klase ng Customer

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

Ngayon, gumawa tayo ng konteksto ng UserContext sa isang hiwalay na file:

Pagpapatupad ng klase ng UserContex

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

Nakatanggap kami ng handa na solusyon para sa pagsasagawa ng mga pagsubok sa pag-optimize gamit ang LINQ hanggang SQL sa pamamagitan ng EF para sa MS SQL Server:

Ngayon ipasok ang sumusunod na code sa Program.cs file:

Program.cs file

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

Susunod, ilunsad natin ang ating proyekto.

Sa pagtatapos ng trabaho, ang mga sumusunod ay ipapakita sa console:

Binuo ng SQL Query

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

Iyon ay, sa pangkalahatan, ang LINQ query ay nakabuo ng isang SQL query sa MS SQL Server DBMS nang maayos.

Ngayon, baguhin natin ang kondisyon ng AND sa OR sa query ng LINQ:

Tanong ng LINQ

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

At muli nating ilunsad ang ating aplikasyon.

Mag-crash ang execution na may error dahil sa oras ng execution ng command na lampas sa 30 segundo:

Kung titingnan mo ang query na nabuo ng LINQ:

, pagkatapos ay maaari mong tiyakin na ang pagpili ay nangyayari sa pamamagitan ng produkto ng Cartesian ng dalawang set (mga talahanayan):

Binuo ng SQL Query

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]

Isulat muli natin ang query ng LINQ tulad ng sumusunod:

Na-optimize na query sa LINQ

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

Pagkatapos makuha namin ang sumusunod na query sa SQL:

SQL query

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]

Sa kasamaang palad, sa mga query ng LINQ ay maaari lamang magkaroon ng isang kondisyon ng pagsali, kaya dito posible na gumawa ng isang katumbas na query gamit ang dalawang query para sa bawat kundisyon at pagkatapos ay pagsasama-samahin ang mga ito sa pamamagitan ng Union upang alisin ang mga duplicate sa mga hilera.
Oo, ang mga query sa pangkalahatan ay hindi katumbas, na isinasaalang-alang na ang kumpletong duplicate na mga hilera ay maaaring ibalik. Gayunpaman, sa totoong buhay, hindi kailangan ang kumpletong mga duplicate na linya at sinisikap ng mga tao na alisin ang mga ito.

Ngayon ihambing natin ang mga plano sa pagpapatupad ng dalawang query na ito:

1. para sa CROSS JOIN ang average na oras ng pagpapatupad ay 195 segundo:
   
2. para sa INNER JOIN-UNION ang average na oras ng pagpapatupad ay mas mababa sa 24 segundo:
   

Tulad ng nakikita mo mula sa mga resulta, para sa dalawang talahanayan na may milyun-milyong talaan, ang na-optimize na query sa LINQ ay maraming beses na mas mabilis kaysa sa hindi na-optimize.

Para sa opsyon na may AT sa mga kundisyon, isang query sa LINQ ng form:

Tanong ng LINQ

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

Ang tamang SQL query ay halos palaging mabubuo, na tatakbo sa average sa humigit-kumulang 1 segundo:

Gayundin para sa LINQ to Objects manipulations sa halip na isang query tulad ng:

LINQ query (1st option)

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

maaari kang gumamit ng isang query tulad ng:

LINQ query (2st option)

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

kung saan:

Pagtukoy sa dalawang array

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

, at ang uri ng Para ay tinukoy bilang sumusunod:

Kahulugan ng Uri ng Para

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

Kaya, sinuri namin ang ilang aspeto sa pag-optimize ng mga query sa LINQ sa MS SQL Server.

Sa kasamaang palad, kahit na may karanasan at nangungunang mga developer ng .NET ay nakakalimutan na kailangan nilang maunawaan kung ano ang ginagawa ng mga tagubiling ginagamit nila sa likod ng mga eksena. Kung hindi man, sila ay nagiging mga configurator at maaaring magtanim ng isang time bomb sa hinaharap kapwa kapag sinusuri ang solusyon sa software at may maliliit na pagbabago sa mga panlabas na kondisyon sa kapaligiran.

Isang maikling pagsusuri din ang isinagawa dito.

Ang mga mapagkukunan para sa pagsubok - ang proyekto mismo, ang paglikha ng mga talahanayan sa database ng TEST, pati na rin ang pagpuno sa mga talahanayan na ito ng data ay matatagpuan dito.
Gayundin sa repositoryong ito, sa folder ng Mga Plano, may mga plano para sa pagpapatupad ng mga query na may mga kundisyon ng O.

Pinagmulan: www.habr.com