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

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) сыноо долбоорун түзүшүңүз керек:

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

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

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

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

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

Андан кийин, 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 менен оптималдаштыруу тесттерин өткөрүү үчүн даяр чечим алдык:

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

Эми 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 секунддан ашкандыктан, аткаруу ката менен бузулат:

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

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

MS SQL Server үчүн C#.NETте 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 секунд:
    MS SQL Server үчүн C#.NETте LINQ сурамдарын оптималдаштыруунун кээ бир аспектилери
  2. INNER JOIN-UNION үчүн орточо аткаруу убактысы 24 секунддан аз:
    MS SQL Server үчүн C#.NETте LINQ сурамдарын оптималдаштыруунун кээ бир аспектилери

    3. .
    Ошондой эле бул репозиторийде, Пландар папкасында ЖЕ шарттары менен суроо-талаптарды аткаруу пландары бар.

Source: www.habr.com

Комментарий кошуу