🥇LINQ მოთხოვნების ოპტიმიზაციის ზოგიერთი ასპექტი C#.NET-ში MS SQL სერვერისთვის

LINQ შევიდა .NET-ში, როგორც მონაცემთა მანიპულირების ახალი მძლავრი ენა. LINQ to SQL, როგორც მისი ნაწილი, საშუალებას გაძლევთ საკმაოდ მოხერხებულად დაუკავშირდეთ DBMS-თან, მაგალითად, Entity Framework-ის გამოყენებით. თუმცა, მისი საკმაოდ ხშირად გამოყენებით, დეველოპერებს ავიწყდებათ დაათვალიერონ, რა სახის SQL მოთხოვნას გამოიმუშავებს მოთხოვნილი პროვაიდერი, თქვენს შემთხვევაში Entity Framework.

განვიხილოთ ორი ძირითადი პუნქტი მაგალითის გამოყენებით.
ამისათვის შექმენით ტესტის მონაცემთა ბაზა 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 ცხრილი შემდეგი სკრიპტის გაშვებით:

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-ისთვის მონაცემთა ბაზასთან ურთიერთობისთვის.
მის დასამატებლად დააწკაპუნეთ პროექტზე მარჯვენა ღილაკით და კონტექსტური მენიუდან აირჩიეთ Manage NuGet Packages:

შემდეგ, 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 ინტერფეისი ცალკეულ ფაილებში:

IBaseEntityID ინტერფეისის დანერგვა

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

IBaseEntityName ინტერფეისის დანერგვა

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

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

შემდეგი, ჩვენ შევქმნით ჩვენს ორ ერთეულს ცალკეულ ფაილებში:

Ref კლასის განხორციელება

using System.ComponentModel.DataAnnotations.Schema;

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

მომხმარებელთა კლასის განხორციელება

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

ჩვენ მივიღეთ მზა გადაწყვეტა ოპტიმიზაციის ტესტების ჩასატარებლად LINQ to SQL მეშვეობით EF MS 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 მოთხოვნამ საკმაოდ კარგად შექმნა SQL მოთხოვნა MS SQL Server DBMS-ზე.

ახლა მოდით შევცვალოთ AND პირობა OR-ით 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 შეკითხვებში შეიძლება იყოს მხოლოდ ერთი შეერთების პირობა, ასე რომ, აქ შესაძლებელია ექვივალენტური შეკითხვის გაკეთება თითოეული პირობისთვის ორი მოთხოვნის გამოყენებით და შემდეგ მათი გაერთიანება კავშირის მეშვეობით, რათა ამოიღოთ დუბლიკატები მწკრივებს შორის.
დიახ, მოთხოვნები ზოგადად არაეკვივალენტური იქნება, იმის გათვალისწინებით, რომ შეიძლება დაბრუნდეს სრული დუბლიკატი რიგები. თუმცა, რეალურ ცხოვრებაში, სრული დუბლიკატი ხაზები არ არის საჭირო და ხალხი ცდილობს მათ მოშორებას.

ახლა შევადაროთ ამ ორი მოთხოვნის შესრულების გეგმები:

CROSS JOIN-ისთვის შესრულების საშუალო დროა 195 წამი:
INNER JOIN-UNION-ისთვის შესრულების საშუალო დრო 24 წამზე ნაკლებია:

როგორც შედეგებიდან ხედავთ, მილიონობით ჩანაწერის მქონე ორი ცხრილისთვის, ოპტიმიზებული LINQ მოთხოვნა ბევრჯერ უფრო სწრაფია, ვიდრე არაოპტიმიზებული.

AND-ის ოფციისთვის პირობებში, ფორმის 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" } };

და პარას ტიპი განისაზღვრება შემდეგნაირად:

პარას ტიპის განმარტება

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

ამრიგად, ჩვენ განვიხილეთ ზოგიერთი ასპექტი LINQ მოთხოვნების ოპტიმიზაციისას MS SQL სერვერზე.

სამწუხაროდ, გამოცდილ და წამყვან .NET-ის დეველოპერებსაც კი ავიწყდებათ, რომ მათ უნდა გაიგონ, რას აკეთებს მათ მიერ გამოყენებული ინსტრუქციები კულისებში. წინააღმდეგ შემთხვევაში, ისინი ხდებიან კონფიგურატორები და მომავალში შეუძლიათ დროის ბომბის დადგმა, როგორც პროგრამული გადაწყვეტის სკალირებისას, ასევე გარე გარემო პირობების უმნიშვნელო ცვლილებებით.

ასევე ჩატარდა მოკლე მიმოხილვა აქ.

განთავსებულია ტესტის წყაროები - თავად პროექტი, ცხრილების შექმნა TEST მონაცემთა ბაზაში, ასევე ამ ცხრილების მონაცემებით შევსება. აქ.
ასევე ამ საცავში, Plans საქაღალდეში, არის გეგმები მოთხოვნების შესრულებისთვის OR პირობებით.

წყარო: www.habr.com

C#.NET-ში LINQ მოთხოვნების ოპტიმიზაციის ზოგიერთი ასპექტი MS SQL სერვერისთვის

ახალი კომენტარის დამატება

C#.NET-ში LINQ მოთხოვნების ოპტიმიზაციის ზოგიერთი ასპექტი MS SQL სერვერისთვის

ახალი კომენტარის დამატება პასუხის გასაუქმებლად

ახალი კომენტარის დამატება