🥇כמה היבטים של אופטימיזציה של שאילתות LINQ ב-C#.NET עבור MS SQL Server

LINQ נכנסה ל-.NET כשפת מניפולציה חדשה וחזקה. LINQ ל-SQL כחלק ממנו מאפשר לך לתקשר בצורה נוחה למדי עם DBMS באמצעות, למשל, Entity Framework. עם זאת, משתמשים בו לעתים קרובות למדי, מפתחים שוכחים לבדוק איזה סוג של שאילתת SQL יפיק הספק הניתן לשאילתה, במקרה שלך Entity Framework.

בואו נסתכל על שתי נקודות עיקריות באמצעות דוגמה.
לשם כך, צור מסד נתונים Test ב-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 מיליון שורות נתונים.

כעת ב-Visual Studio אתה צריך ליצור פרויקט בדיקה של Visual C# Console App (.NET Framework):

לאחר מכן, עליך להוסיף ספריה כדי שמסגרת הישות תיצור אינטראקציה עם מסד הנתונים.
כדי להוסיף אותו, לחץ לחיצה ימנית על הפרויקט ובחר ניהול חבילות NuGet מתפריט ההקשר:

לאחר מכן, בחלון ניהול החבילות של 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 ל-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();
            }
        }
    }
}

לאחר מכן, בואו נשיק את הפרויקט שלנו.

בסיום העבודה יוצגו במסוף הדברים הבאים:

שאילתת 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 ל-DBMS של MS SQL Server בצורה טובה למדי.

כעת נשנה את תנאי ה-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 יכול להיות רק תנאי הצטרפות אחד, אז כאן אפשר לעשות שאילתה שוות ערך באמצעות שתי שאילתות לכל תנאי ולאחר מכן לשלב אותן דרך Union כדי להסיר כפילויות בין השורות.
כן, השאילתות בדרך כלל לא יהיו שוות ערך, תוך התחשבות בכך שייתכן שיוחזרו שורות כפולות שלמות. עם זאת, בחיים האמיתיים, אין צורך בשורות כפולות שלמות ואנשים מנסים להיפטר מהם.

כעת נשווה את תוכניות הביצוע של שתי השאילתות הללו:

עבור CROSS JOIN זמן הביצוע הממוצע הוא 195 שניות:
עבור INNER JOIN-UNION זמן הביצוע הממוצע הוא פחות מ-24 שניות:

.
גם במאגר זה, בתיקיית התוכניות, ישנן תוכניות לביצוע שאילתות עם תנאי OR.

מקור: www.habr.com

כמה היבטים של אופטימיזציה של שאילתות LINQ ב-C#.NET עבור MS SQL Server

הוספת תגובה ביטול תגובה