MS SQL Server үчүн C#.NETте LINQ сурамдарын оптималдаштыруунун кээ бир аспектилери

LINQ .NET'ке кубаттуу жаңы маалыматтарды манипуляциялоо тили катары кирди. LINQ to SQL анын бир бөлүгү катары, мисалы, Entity Framework аркылуу DBMS менен абдан ыңгайлуу байланышууга мүмкүндүк берет. Бирок, аны көп колдонуп, иштеп чыгуучулар суралуучу провайдер, сиздин жагдайыңызда Entity Framework кандай SQL суроосун жаратаарын кароону унутуп калышат.

Мисал аркылуу эки негизги ойду карап көрөлү.
Бул үчүн, SQL серверинде Test маалымат базасын түзүңүз жана анда төмөнкү суроону колдонуу менен эки таблицаны түзүңүз:

Таблицаларды түзүү

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

Эми төмөнкү скриптти иштетүү менен Ref таблицасын толтуралы:

Ref таблицасын толтуруу

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

Келгиле, төмөнкү скрипт менен Кардар таблицасын толтуралы:

Кардар таблицасын толтуруу

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

Ошентип, биз эки таблицаны алдык, алардын биринде 1 миллиондон ашык маалымат бар, экинчисинде 10 миллиондон ашык маалымат бар.

Эми Visual Studio'до сиз Visual C# Console App (.NET Framework) сыноо долбоорун түзүшүңүз керек:

Андан кийин, маалымат базасы менен иштешүү үчүн Entity Framework үчүн китепкана кошуу керек.
Аны кошуу үчүн долбоорду оң баскыч менен чыкылдатып, контексттик менюдан NuGet пакеттерин башкарууну тандаңыз:

Андан кийин, пайда болгон NuGet пакетин башкаруу терезесине издөө терезесине "Entity Framework" сөзүн киргизиңиз жана Entity Framework пакетин тандап, аны орнотуңуз:

Андан кийин, App.config файлында configSections элементин жапкандан кийин, төмөнкү блокту кошуу керек:

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

ConnectionString'де сиз байланыш сабын киргизишиңиз керек.

Эми өзүнчө файлдарда 3 интерфейсти түзөлү:

1. IBaseEntityID интерфейсин ишке ашыруу

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

2. IBaseEntityName интерфейсин ишке ашыруу

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

3. IBaseNameInsertUTCDate интерфейсин ишке ашыруу

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

Ал эми өзүнчө файлда биз эки объектибиз үчүн BaseEntity базалык классын түзөбүз, ал жалпы талааларды камтыйт:

BaseEntity базалык классын ишке ашыруу

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

Андан кийин, биз эки объектибизди өзүнчө файлдарда түзөбүз:

1. Ref классын ишке ашыруу

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

2. Кардар классын ишке ашыруу

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

Эми өзүнчө файлда UserContext контекстин түзөлү:

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

Биз MS SQL Server үчүн EF аркылуу LINQ to SQL менен оптималдаштыруу тесттерин өткөрүү үчүн даяр чечим алдык:

Эми Program.cs файлына төмөнкү кодду киргизиңиз:

program.cs файлы

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

Эми биздин долбоорубузду ишке киргизели.

Иштин аягында консолдо төмөнкүлөр көрсөтүлөт:

Түзүлгөн SQL суроо

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

Башкача айтканда, жалпысынан LINQ суроосу MS SQL Server DBMS үчүн SQL суроосун абдан жакшы жаратты.

Эми LINQ сурамында ЖАНА шартын ЖЕ кылып өзгөртөлү:

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

Жана биздин тиркемени кайра ишке киргизели.

Буйруктун аткаруу убактысы 30 секунддан ашкандыктан, аткаруу ката менен бузулат:

LINQ тарабынан түзүлгөн суроону карасаңыз:

, анда тандоо эки топтомдун (таблицалардын) декарттык продуктусу аркылуу ишке ашканына ынанууга болот:

Түзүлгөн SQL суроо

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]

LINQ суроосун төмөнкүдөй кайра жазалы:

Оптимизацияланган 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 });

Андан кийин биз төмөнкү SQL суроосун алабыз:

SQL суроо

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]

Тилекке каршы, LINQ сурамдарында бир гана кошулуу шарты болушу мүмкүн, андыктан бул жерде ар бир шарт үчүн эки суроону колдонуу менен эквиваленттүү суроону түзүүгө болот, анан аларды Биримдик аркылуу бириктирип, саптар арасындагы кайталанмаларды алып салса болот.
Ооба, толук кайталанган саптар кайтарылышы мүмкүн экенин эске алуу менен, сурамдар жалпысынан эквиваленттүү эмес болот. Бирок, чыныгы жашоодо толук кайталанган саптардын кереги жок жана адамдар алардан арылууга аракет кылышат.

Эми бул эки суроонун аткаруу пландарын салыштырып көрөлү:

1. CROSS JOIN үчүн орточо аткаруу убактысы 195 секунд:
   
2. INNER JOIN-UNION үчүн орточо аткаруу убактысы 24 секунддан аз:
   

Натыйжалардан көрүнүп тургандай, миллиондогон жазуулары бар эки таблица үчүн оптималдаштырылган LINQ суроосу оптимизацияланбаганга караганда бир нече эсе ылдамыраак.

Шарттарда ЖАНА менен опция үчүн форманын LINQ сурамы:

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

Туура SQL сурамы дээрлик ар дайым түзүлөт, ал орточо эсеп менен 1 секундада иштейт:

Ошондой эле төмөнкүдөй суроонун ордуна LINQ to Objects манипуляциялары үчүн:

LINQ сурамы (1-вариант)

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

сыяктуу суроону колдоно аласыз:

LINQ сурамы (2-вариант)

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

мында:

Эки массивди аныктоо

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

, жана Para түрү төмөнкүдөй аныкталат:

Para Type Definition

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

Ошентип, биз MS SQL серверине LINQ сурамдарын оптималдаштыруунун кээ бир аспектилерин карап чыктык.

Тилекке каршы, атүгүл тажрыйбалуу жана алдыңкы .NET иштеп чыгуучулары, алар колдонгон көрсөтмөлөр көшөгө артында эмне кылып жатканын түшүнүшү керек экенин унутуп коюшат. Болбосо, алар конфигуратор болуп калышат жана келечекте программалык камсыздоону масштабдоодо да, тышкы экологиялык шарттарда кичине өзгөрүүлөр менен да убакыт бомбасын орното алышат.

Ошондой эле кыскача кароо жүргүзүлдү бул жерде.

Тесттин булактары - долбоордун өзү, TEST маалыматтар базасында таблицаларды түзүү, ошондой эле бул таблицаларды маалыматтар менен толтуруу бул жерде.
Ошондой эле бул репозиторийде, Пландар папкасында ЖЕ шарттары менен суроо-талаптарды аткаруу пландары бар.

Source: www.habr.com