ProHoster > Блог > басқарма > MS SQL Server үшін C#.NET жүйесінде LINQ сұрауларын оңтайландырудың кейбір аспектілері

MS SQL Server үшін C#.NET жүйесінде LINQ сұрауларын оңтайландырудың кейбір аспектілері

LINQ .NET жүйесін қуатты жаңа деректерді өңдеу тілі ретінде енгізді. LINQ to SQL оның бөлігі ретінде, мысалы, Entity Framework көмегімен ДҚБЖ-мен өте ыңғайлы байланысуға мүмкіндік береді. Дегенмен, оны жиі пайдалана отырып, әзірлеушілер сұранысқа ие провайдер, сіздің жағдайда Entity Framework, қандай SQL сұрауын жасайтынын қарауды ұмытып кетеді.

Мысал арқылы екі негізгі ойды қарастырайық.
Ол үшін SQL Server серверінде сынақ деректер базасын жасаңыз және келесі сұрауды пайдаланып онда екі кесте жасаңыз:

Кестелерді жасау

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 кестесін толтырайық:

Анықтама кестесін толтыру

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 серверіне арналған 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 ДҚБЖ үшін 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 сұрауларын оңтайландырудың кейбір аспектілері

Нәтижелерден көріп отырғаныңыздай, миллиондаған жазбалары бар екі кесте үшін оңтайландырылған 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 секундта орындалады:

MS SQL Server үшін C#.NET жүйесінде LINQ сұрауларын оңтайландырудың кейбір аспектілері
Сондай-ақ келесідей сұраудың орнына 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 анықтамасы

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

Осылайша, MS SQL серверіне LINQ сұрауларын оңтайландырудың кейбір аспектілерін қарастырдық.

Өкінішке орай, тіпті тәжірибелі және жетекші .NET әзірлеушілері қолданатын нұсқаулардың сахна артында не істейтінін түсіну керек екенін ұмытып кетеді. Әйтпесе, олар конфигураторға айналады және болашақта бағдарламалық шешімді масштабтау кезінде де, сыртқы орта жағдайларындағы шамалы өзгерістермен де уақыт бомбасын қоя алады.

Сондай-ақ қысқаша шолу жасалды осында.

Сынақ көздері – жобаның өзі, TEST мәліметтер базасында кестелерді құру, сонымен қатар осы кестелерді деректермен толтыру орналасқан. осында.
Сондай-ақ осы репозиторийде Жоспарлар қалтасында НЕМЕСЕ шарттары бар сұрауларды орындау жоспарлары бар.

Ақпарат көзі: www.habr.com

пікір қалдыру