🥇כמה היבטים של אופטימיזציה של שאילתות 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 שניות:

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

גם עבור מניפולציות LINQ to Objects במקום שאילתה כמו:

שאילתת LINQ (אפשרות ראשונה)

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 (אפשרות ראשונה)

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 מוגדר כדלקמן:

הגדרת סוג פארה

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

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