עטלעכע אַספּעקץ פון אָפּטימיזינג 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 ינטערפייסיז אין באַזונדער טעקעס:

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. ימפּלאַמענטיישאַן פון די רעף קלאַס

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

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

איצט לאָזן ס פאַרגלייַכן די דורכפירונג פּלאַנז פון די צוויי פֿראגן:

1. פֿאַר CROSS JOIN די דורכשניטלעך דורכפירונג צייט איז 195 סעקונדעס:
   
2. פֿאַר ינער 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 צו אָבדזשעקץ מאַניפּיאַליישאַנז אַנשטאָט פון אַ אָנפֿרעג ווי:

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 Server.

צום באַדויערן, אפילו יקספּיריאַנסט און לידינג .NET דעוועלאָפּערס פאַרגעסן אַז זיי דאַרפֿן צו פֿאַרשטיין וואָס די ינסטראַקשאַנז זיי נוצן טאָן הינטער די סינז. אַנדערש, זיי ווערן קאָנפיגוראַטאָרס און קענען פאַבריק אַ צייט באָמבע אין דער צוקונפֿט ביידע ווען סקיילינג די ווייכווארג לייזונג און מיט מינערווערטיק ענדערונגען אין פונדרויסנדיק ינווייראַנמענאַל טנאָים.

א קורצע רעצענזיע איז אויך דורכגעפירט געווארן דאָ.

די קוואלן פֿאַר די פּראָבע - די פּרויעקט זיך, די שאַפונג פון טישן אין די TEST דאַטאַבייס, און פילונג די טישן מיט דאַטן זענען ליגן דאָ.
אויך אין דעם ריפּאַזאַטאָרי, אין די פּלאַנס טעקע, עס זענען פּלאַנז פֿאַר עקסאַקיוטינג קוויריז מיט OR טנאָים.

מקור: www.habr.com