🥇עטלעכע אַספּעקץ פון אָפּטימיזינג LINQ קוויריז אין C#.NET פֿאַר MS SQL Server

LINQ איז אריין אין .NET ווי אַ שטאַרק נייַע דאַטן מאַניפּיאַליישאַן שפּראַך. LINQ צו SQL ווי אַ טייל פון עס אַלאַוז איר צו יבערגעבן גאַנץ קאַנוויניאַנטלי מיט אַ DBMS ניצן, למשל, Entity Framework. אָבער, ניצן עס גאַנץ אָפט, דעוועלאָפּערס פאַרגעסן צו קוקן אין וואָס סאָרט פון סקל אָנפֿרעג די קוועריאַבלע שפּייַזער, אין דיין פאַל, ענטיטי פראַמעוואָרק, וועט דזשענערייט.

לאָמיר קוקן אויף צוויי הויפּט פונקטן ניצן אַ בייַשפּיל.
צו טאָן דאָס, שאַפֿן אַ טעסט דאַטאַבייס אין SQL סערווירער און שאַפֿן צוויי טישן אין עס מיט די פאלגענדע אָנפֿרעג:

שאפן טאַבלעס

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

איצט לאָזן אונדז באַפעלקערן די רעף טיש דורך לויפן די פאלגענדע שריפט:

פילונג די רעף טיש

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 מיליאָן ראָוז פון דאַטן.

איצט אין וויסואַל סטודיאָ איר דאַרפֿן צו שאַפֿן אַ פּראָבע וויסואַל C # קאַנסאָול אַפּ (.נעט פראַמעוואָרק) פּרויעקט:

דערנאָך איר דאַרפֿן צו לייגן אַ ביבליאָטעק פֿאַר די ענטיטי פראַמעוואָרק צו ינטעראַקט מיט די דאַטאַבייס.
צו לייגן עס, רעכט גיט אויף די פּרויעקט און סעלעקטירן 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; }
    }
}

ווייַטער, מיר וועלן מאַכן אונדזער צוויי ענטיטיז אין באַזונדער טעקעס:

ימפּלאַמענטיישאַן פון די רעף קלאַס

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 צו SQL דורך EF פֿאַר MS SQL Server:

איצט אַרייַן די פאלגענדע קאָד אין די 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();
            }
        }
    }
}

ווייַטער, לאָזן אונדז קאַטער אונדזער פּרויעקט.

אין די סוף פון די אַרבעט, די פאלגענדע וועט זיין געוויזן אויף די קאַנסאָול:

דזשענערייטאַד סקל אָנפֿרעג

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 גאַנץ גוט.

איצט לאָזן אונדז טוישן די 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:

, דעמאָלט איר קענען מאַכן זיכער אַז די סעלעקציע אַקערז דורך די קאַרטעסיאַן פּראָדוקט פון צוויי שטעלט (טישן):

דזשענערייטאַד סקל אָנפֿרעג

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 סעקונדעס:
פֿאַר ינער 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

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

Таким образом мы рассмотрели некоторые аспекты в оптимизации LINQ-запросов к MS SQL Server.

К сожалению даже опытные и ведущие .NET-разработчики забывают о том, что необходимо понимать что делают за кадром те инструкции, которые они используют. Иначе они становятся конфигураторами и могут заложить бомбу замедленного действия в будущем как при масштабировании программного решения, так и при незначительных изменениях внешних условий среды.

Также небольшой обзор проводился и דאָ.

Исходники для теста-сам проект, создание таблиц в базе данных TEST, а также наполнение данными этих таблиц находится דאָ.
אויך אין דעם ריפּאַזאַטאָרי, אין די פּלאַנס טעקע, עס זענען פּלאַנז פֿאַר עקסאַקיוטינג קוויריז מיט OR טנאָים.

מקור: www.habr.com

עטלעכע אַספּעקץ פון אָפּטימיזינג LINQ קוויריז אין C #.NET פֿאַר MS SQL Server

לייגן אַ באַמערקונג באָטל מאַכן ענטפער