שיטות לאופטימיזציה של שאילתות LINQ ב-C#.NET

מבוא

В מאמר זה נבקלו כמה שיטות אופטימיזציה שאילתות LINQ.
כאן אנו מציגים גם כמה גישות נוספות למיטוב קוד הקשורות שאילתות LINQ.

זה ידוע כי LINQ(שאילתה משולבת בשפה) היא שפה פשוטה ונוחה לשאילתה של מקור נתונים.

А LINQ ל- SQL היא טכנולוגיה לגישה לנתונים ב-DBMS. זהו כלי רב עוצמה לעבודה עם נתונים, שבו שאילתות נבנות באמצעות שפה הצהרתית, אשר לאחר מכן תומר ל- שאילתות SQL פלטפורמה ונשלח לשרת מסד הנתונים לביצוע. במקרה שלנו, ב-DBMS אנחנו מתכוונים MS SQL Server.

עם זאת, שאילתות LINQ אינם מומרים לכתובים בצורה מיטבית שאילתות SQL, ש-DBA מנוסה יוכל לכתוב עם כל הניואנסים של אופטימיזציה שאילתות SQL:

1. חיבורים אופטימליים (להצטרף) וסינון התוצאות (איפה)
2. ניואנסים רבים בשימוש בקשרים ובתנאים קבוצתיים
3. וריאציות רבות בהחלפת תנאים IN על EXISTSи לא ב, <> פועל EXISTS
4. מטמון ביניים של תוצאות באמצעות טבלאות זמניות, CTE, משתני טבלה
5. שימוש במשפט (אוֹפְּצִיָה) עם הוראות ורמזים לטבלה עִם (...)
6. שימוש בתצוגות אינדקס כאחד האמצעים להיפטר מקריאות נתונים מיותרות במהלך בחירות

צווארי הבקבוק העיקריים של הביצועים המתקבלים שאילתות SQL בעת הידור שאילתות LINQ הם:

1. איחוד של כל מנגנון בחירת הנתונים בבקשה אחת
2. שכפול בלוקים זהים של קוד, מה שמוביל בסופו של דבר לקריאות נתונים מיותרות מרובות
3. קבוצות של תנאים מרובי רכיבים ("ו" ו"או" לוגיים) - ו и OR, שילוב לתנאים מורכבים, מוביל לעובדה שהאופטימיזר, בעל אינדקסים לא מקובצים מתאימים לשדות הדרושים, מתחיל בסופו של דבר לסרוק כנגד האינדקס המקובץ (סריקת אינדקס) לפי קבוצות של תנאים
4. קינון עמוק של שאילתות משנה הופך את הניתוח לבעייתי מאוד הצהרות SQL וניתוח תוכנית השאילתות מצד מפתחים ו DBA

שיטות מיטוב

כעת נעבור ישירות לשיטות אופטימיזציה.

1) הוספה לאינדקס

עדיף לשקול מסננים בטבלאות הבחירה הראשיות, מכיוון שלעתים קרובות כל השאילתה בנויה סביב טבלה ראשית אחת או שתיים (אפליקציות-אנשים-פעולות) ועם מערכת סטנדרטית של תנאים (IsClosed, Canceled, Enabled, Status). חשוב ליצור מדדים מתאימים לדגימות שזוהו.

פתרון זה הגיוני כאשר בחירה בשדות אלו מגבילה באופן משמעותי את הסט המוחזר לשאילתה.

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

כמו כן, ניתן לזהות את היעדר האינדקסים באמצעות הנחיות לניתוח תוכניות שאילתות או איסוף סטטיסטיקות של תצוגות מערכת MS SQL Server:

1. sys.dm_db_missing_index_groups
2. sys.dm_db_missing_index_group_stats
3. sys.dm_db_missing_index_details

כל נתוני התצוגה מכילים מידע על אינדקסים חסרים, למעט אינדקסים מרחביים.

עם זאת, אינדקסים ושמירת מטמון הם לעתים קרובות שיטות להילחם בהשלכות של כתיבה גרועה שאילתות LINQ и שאילתות SQL.

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

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

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

כדאי גם לזכור שבתהליך החיפוש אחר האינדקסים הדרושים להוסיף, הצעות SQL MS אופטימיזציה עשויה להיות שגויה, כולל בתנאים הבאים:

1. אם כבר יש אינדקסים עם קבוצה דומה של שדות
2. אם השדות בטבלה אינם ניתנים לאינדקס עקב מגבלות הוספה לאינדקס (מתואר בפירוט רב יותר כאן).

2) מיזוג תכונות לתכונה חדשה אחת

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

זה נכון במיוחד עבור שדות סטטוס, שהם בדרך כלל ביט או מספר שלם.

לדוגמה:

IsClosed = 0 AND Cancelled = 0 ו-Enabled = 0 הוחלף על ידי סטטוס = 1.

כאן מוצגת תכונת המצב השלם כדי להבטיח שהסטטוסים האלה מאוכלסים בטבלה. לאחר מכן, התכונה החדשה הזו מתווספת לאינדקס.

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

3) התממשות הנוף

למרבה הצער, ב שאילתות LINQ לא ניתן להשתמש בטבלאות זמניות, CTEs ומשתני טבלה ישירות.

עם זאת, יש דרך נוספת לבצע אופטימיזציה עבור מקרה זה - תצוגות אינדקס.

קבוצת מצב (מהדוגמה למעלה) IsClosed = 0 AND Cancelled = 0 ו-Enabled = 0 (או קבוצה של תנאים דומים אחרים) הופכת לאפשרות טובה להשתמש בהם בתצוגה אינדקסית, תוך שמירה במטמון של פרוסה קטנה של נתונים מקבוצה גדולה.

אבל ישנן מספר הגבלות בעת מימוש תצוגה:

1. באמצעות שאילתות משנה, סעיפים EXISTS יש להחליף על ידי שימוש להצטרף
2. אתה לא יכול להשתמש במשפטים אגוד, UNION ALL, יוצא מן הכלל, לְהִצְטָלֵב
3. אינך יכול להשתמש ברמזים ובסעיפים לטבלה אוֹפְּצִיָה
4. אין אפשרות לעבוד עם מחזורים
5. אי אפשר להציג נתונים בתצוגה אחת מטבלאות שונות

חשוב לזכור שאת התועלת האמיתית של שימוש בתצוגת אינדקס ניתן להשיג רק על ידי הוספתה לאינדקס בפועל.

אבל בעת קריאה לתצוגה, אין להשתמש באינדקסים האלה, וכדי להשתמש בהם במפורש, עליך לציין WITH(NOEXPAND).

מאז ב שאילתות LINQ אי אפשר להגדיר רמזים לטבלה, אז אתה צריך ליצור ייצוג אחר - "עטיפה" מהצורה הבאה:

CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) שימוש בפונקציות טבלה

לעתים קרובות ב שאילתות LINQ בלוקים גדולים של שאילתות משנה או בלוקים המשתמשים בתצוגות בעלות מבנה מורכב יוצרים שאילתה סופית עם מבנה ביצוע מורכב מאוד ולא אופטימלי.

היתרונות העיקריים של שימוש בפונקציות טבלה ב שאילתות LINQ:

1. היכולת, כמו במקרה של תצוגות, לשמש ולציין כאובייקט, אבל אתה יכול להעביר קבוצה של פרמטרי קלט:
   FROM FUNCTION(@param1, @param2 ...)
   כתוצאה מכך, ניתן להשיג דגימת נתונים גמישה
2. במקרה של שימוש בפונקציית טבלה, אין הגבלות כה חזקות כמו במקרה של תצוגות אינדקס המתוארות לעיל:
   1. רמזים לטבלה:
      דרך LINQ אינך יכול לציין באילו אינדקסים יש להשתמש ולקבוע את רמת בידוד הנתונים בעת ביצוע שאילתה.
      אבל לפונקציה יש את היכולות האלה.
      עם הפונקציה, אתה יכול להשיג תוכנית שאילתות ביצוע קבועה למדי, שבה מוגדרים כללים לעבודה עם אינדקסים ורמות בידוד נתונים
   2. השימוש בפונקציה מאפשר, בהשוואה לתצוגות שנוספו לאינדקס, להשיג:
      • לוגיקה מורכבת של דגימת נתונים (אפילו באמצעות לולאות)
      • שליפת נתונים מטבלאות רבות ושונות
      • использование אגוד и EXISTS

3. הצעה אוֹפְּצִיָה שימושי מאוד כאשר אנו צריכים לספק בקרת מקבילות OPTION(MAXDOP N), סדר תוכנית ביצוע השאילתה. לדוגמה:
   • אתה יכול לציין יצירה מחדש כפויה של תוכנית השאילתות אפשרות (הידור מחדש)
   • אתה יכול לציין אם לאלץ את תוכנית השאילתה להשתמש בסדר ההצטרפות שצוין בשאילתה אפשרות (צו בכפייה)

   פרטים נוספים על אוֹפְּצִיָה מְתוּאָר כאן.

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

   באופן קונבנציונלי, לכל הבקשות יש מצב קבוע a = 0 ו- b = 0.

   עם זאת, הבקשה לתחום c משתנה יותר.

   תן את התנאי a = 0 ו- b = 0 זה באמת עוזר לנו להגביל את מערך התוצאות הנדרש לאלפי רשומות, אבל התנאי על с מצמצם את הבחירה למאה רשומות.

   כאן פונקציית הטבלה עשויה להיות אפשרות טובה יותר.

   כמו כן, פונקציית טבלה צפויה ועקבית יותר בזמן הביצוע.

דוגמאות

הבה נסתכל על יישום לדוגמה באמצעות מסד הנתונים של שאלות כדוגמה.

יש בקשה לבחור, המשלבת מספר טבלאות ומשתמשת בתצוגה אחת (OperativeQuestions), שבה נבדקת ההשתייכות בדוא"ל (באמצעות EXISTS) ל"שאלות אופרטיביות":

בקשה מס' 1

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL) 
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));

לתצוגה יש מבנה מורכב למדי: הוא מכיל צירוף של שאילתות משנה ומשתמש במיון DISTINCT, שבאופן כללי מדובר במבצע די עתיר משאבים.

מדגם מ-OperativeQuestions הוא כעשרת אלפים רשומות.

הבעיה העיקרית בשאילתה זו היא שעבור הרשומות מהשאילתה החיצונית, תת-שאילתה פנימית מבוצעת בתצוגה [OperativeQuestions], שאמורה עבור [Email] = @p__linq__0 לאפשר לנו להגביל את בחירת הפלט (באמצעות EXISTS) עד מאות רשומות.

ואולי נראה שהשאילתה המשנה צריכה לחשב את הרשומות פעם אחת לפי [Email] = @p__linq__0, ואז כמה מאות הרשומות האלה צריכות להיות מחוברות על ידי Id עם שאלות, והשאילתה תהיה מהירה.

למעשה, יש חיבור רציף של כל הטבלאות: בדיקת התכתבות של Id Questions עם Id מ-OperativeQuestions, וסינון באמצעות אימייל.

למעשה, הבקשה עובדת עם כל עשרות אלפי רשומות ה-OperativeQuestions, אך נדרשים רק הנתונים המעניינים באמצעות דואר אלקטרוני.

טקסט תצוגת שאלות אופרטיביות:

בקשה מס' 2

 
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM            [dbo].Questions AS Q INNER JOIN
                         [dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id 
OUTER APPLY
                     (SELECT   1 AS HasNoObjects
                      WHERE   NOT EXISTS
                                    (SELECT   1
                                     FROM     [dbo].ObjectUserAccesses AS BOU
                                     WHERE   BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
                         [dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE        CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL 
AND (BO.HasNoObjects = 1 OR
              EXISTS (SELECT   1
                           FROM   [dbo].ObjectUserAccesses AS BOU INNER JOIN
                                      [dbo].ObjectQuestions AS QBO 
                                                  ON QBO.[Object_Id] =BOU.ObjectId
                               WHERE  BOU.ProcessUserAccessId = BPU.Id 
                               AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));

מיפוי תצוגה ראשוני ב-DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}

שאילתת LINQ ראשונית

var businessObjectsData = await context
    .OperativeQuestions
    .Where(x => x.Email == Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

במקרה הספציפי הזה, אנו שוקלים פתרון לבעיה זו ללא שינויים תשתיתיים, מבלי להכניס טבלה נפרדת עם תוצאות מוכנות ("שאילתות אקטיביות"), אשר ידרוש מנגנון למילויה בנתונים ולעדכנין. .

למרות שזהו פתרון טוב, ישנה אפשרות נוספת לייעל את הבעיה הזו.

המטרה העיקרית היא לשמור ערכים במטמון על ידי [Email] = @p__linq__0 מהתצוגה OperativeQuestions.

הכנס את פונקציית הטבלה [dbo].[OperativeQuestionsUserMail] למסד הנתונים.

על ידי שליחת אימייל כפרמטר קלט, אנו מקבלים בחזרה טבלת ערכים:

בקשה מס' 3

CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
    @Email  nvarchar(4000)
)
RETURNS
@tbl TABLE
(
    [Id]           uniqueidentifier,
    [Email]      nvarchar(4000)
)
AS
BEGIN
        INSERT INTO @tbl ([Id], [Email])
        SELECT Id, @Email
        FROM [OperativeQuestions]  AS [x] WHERE [x].[Email] = @Email;
     
    RETURN;
END

זה מחזיר טבלת ערכים עם מבנה נתונים מוגדר מראש.

על מנת ששאילתות ל-OperativeQuestionsUserMail יהיו אופטימליות ובעלות תוכניות שאילתות אופטימליות, נדרש מבנה קפדני, ולא טבלת החזרות כהחזרה...

במקרה זה, שאילתה 1 הנדרשת מומרת לשאילתה 4:

בקשה מס' 4

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
    SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);

מיפוי תצוגות ופונקציות ב-DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

שאילתת LINQ סופית

var businessObjectsData = await context
    .OperativeQuestions
    .GetByUserEmail(Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

סדר זמן הביצוע ירד מ-200-800 אלפיות השנייה ל-2-20 שניות וכו', כלומר מהר יותר עשרות מונים.

אם ניקח את זה בצורה ממוצעת יותר, אז במקום 350 אלפיות השנייה קיבלנו 8 אלפיות השנייה.

מהיתרונות הברורים אנו מקבלים גם:

1. ירידה כללית בעומס הקריאה,
2. הפחתה משמעותית בסבירות לחסימה
3. צמצום זמן החסימה הממוצע לערכים מקובלים

פלט

אופטימיזציה וכיוונון עדין של שיחות מסד נתונים SQL MS דרך LINQ היא בעיה שניתן לפתור.

קשב ועקביות חשובים מאוד בעבודה זו.

בתחילת התהליך:

1. יש צורך לבדוק את הנתונים שאיתם הבקשה עובדת (ערכים, סוגי נתונים נבחרים)
2. לבצע אינדקס נכון של נתונים אלה
3. בדוק את נכונות תנאי ההצטרפות בין הטבלאות

איטרציית האופטימיזציה הבאה מגלה:

1. בסיס הבקשה ומגדיר את מסנן הבקשות הראשי
2. חזרה על בלוקים של שאילתות דומות וניתוח צומת התנאים
3. ב-SSMS או GUI אחר עבור שרת SQL מייעל את עצמו שאילתת SQL (הקצאת אחסון נתונים ביניים, בניית השאילתה המתקבלת באמצעות אחסון זה (ייתכנו כמה))
4. בשלב האחרון, תוך התבססות על המתקבל שאילתת SQL, המבנה נבנה מחדש שאילתת LINQ

המתקבל שאילתת LINQ צריך להיות זהה במבנה לאופטימלי שזוהה שאילתת SQL מנקודה 3.

תודות

תודה רבה לעמיתים jobgemws и alex_ozr מהחברה פורטיס לסיוע בהכנת חומר זה.

מקור: www.habr.com