روش هایی برای بهینه سازی پرس و جوهای LINQ در C#.NET

معرفی

В این مقاله برخی از روش های بهینه سازی در نظر گرفته شد جستارهای LINQ.
در اینجا ما همچنین برخی از رویکردهای بیشتر برای بهینه سازی کد مربوط به را ارائه می دهیم پرس و جوهای LINQ.

مشخص شده است که LINQ(Language-Integrated Query) یک زبان ساده و راحت برای پرس و جو از منبع داده است.

А LINQ به SQL یک فناوری برای دسترسی به داده ها در یک DBMS است. این یک ابزار قدرتمند برای کار با داده است، جایی که پرس و جوها از طریق یک زبان اعلامی ساخته می شوند، که سپس به پرس و جوهای SQL پلت فرم و برای اجرا به سرور پایگاه داده ارسال می شود. در مورد ما، منظور ما از DBMS است سرور MS SQL.

با این حال، جستارهای LINQ به نوشته های بهینه تبدیل نمی شوند پرس و جوهای SQL، که یک DBA با تجربه می تواند با تمام نکات ظریف بهینه سازی بنویسد پرس و جوهای SQL:

1. اتصالات بهینه (بپیوندید) و فیلتر کردن نتایج (مکانی که در آن)
2. تفاوت های ظریف در استفاده از اتصالات و شرایط گروه
3. تغییرات زیادی در شرایط جایگزینی IN بر وجود داردи داخل نیست، <> در وجود دارد
4. ذخیره سازی میانی نتایج از طریق جداول موقت، CTE، متغیرهای جدول
5. استفاده از جمله (گزینه) با دستورالعمل ها و نکات جدول با (...)
6. استفاده از نماهای نمایه شده به عنوان یکی از ابزارهایی برای خلاص شدن از شر خوانش داده های اضافی در طول انتخاب

گلوگاه عملکرد اصلی در نتیجه پرس و جوهای SQL هنگام تدوین جستارهای LINQ عبارتند از:

1. ادغام کل مکانیسم انتخاب داده در یک درخواست
2. کپی کردن بلوک های یکسان کد، که در نهایت منجر به خواندن چندین داده غیر ضروری می شود.
3. گروه های شرایط چند جزئی (منطقی "و" و "یا") - و и ORترکیب شدن در شرایط پیچیده، منجر به این واقعیت می شود که بهینه ساز با داشتن شاخص های غیر خوشه ای مناسب برای فیلدهای لازم، در نهایت شروع به اسکن در برابر شاخص خوشه ای می کند.اسکن INDEX) بر اساس گروهی از شرایط
4. تودرتو عمیق پرس و جوها تجزیه را بسیار مشکل ساز می کند عبارات SQL و تجزیه و تحلیل طرح پرس و جو از طرف توسعه دهندگان و DBA

روش های بهینه سازی

اکنون مستقیماً به سراغ روش های بهینه سازی می رویم.

1) نمایه سازی اضافی

بهتر است فیلترها را روی جداول انتخاب اصلی در نظر بگیرید، زیرا اغلب کل پرس‌وجو حول یک یا دو جدول اصلی (برنامه‌ها-افراد-عملیات) و با مجموعه‌ای استاندارد از شرایط (IsClosed، Canceled، Enabled، Status) ساخته می‌شود. ایجاد شاخص های مناسب برای نمونه های شناسایی شده مهم است.

این راه حل زمانی منطقی است که انتخاب این فیلدها به طور قابل توجهی مجموعه بازگشتی را به پرس و جو محدود می کند.

مثلا ما 500000 اپلیکیشن داریم. با این حال، تنها 2000 برنامه فعال وجود دارد. سپس یک شاخص به درستی انتخاب شده ما را از آن نجات خواهد داد اسکن INDEX بر روی یک جدول بزرگ و به شما این امکان را می دهد که به سرعت داده ها را از طریق یک شاخص غیر خوشه ای انتخاب کنید.

همچنین، کمبود ایندکس ها را می توان از طریق اعلان های تجزیه طرح های پرس و جو یا جمع آوری آمار نمایش سیستم شناسایی کرد. سرور MS SQL:

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

همه داده‌های نمایش حاوی اطلاعاتی در مورد نمایه‌های گمشده هستند، به استثنای نمایه‌های مکانی.

با این حال، فهرست‌ها و ذخیره‌سازی حافظه پنهان اغلب روش‌هایی برای مبارزه با پیامدهای نوشتن ضعیف هستند جستارهای LINQ и پرس و جوهای SQL.

همانطور که تمرین سخت زندگی نشان می دهد، اغلب برای یک کسب و کار مهم است که ویژگی های تجاری را در مهلت های زمانی مشخص اجرا کند. و بنابراین، درخواست های سنگین اغلب با ذخیره سازی به پس زمینه منتقل می شوند.

این تا حدی قابل توجیه است، زیرا کاربر همیشه به آخرین داده ها نیاز ندارد و سطح قابل قبولی از پاسخگویی رابط کاربری وجود دارد.

این رویکرد اجازه می دهد تا نیازهای کسب و کار را حل کند، اما در نهایت با به تاخیر انداختن راه حل های مشکلات، عملکرد سیستم اطلاعاتی را کاهش می دهد.

همچنین لازم به یادآوری است که در فرآیند جستجوی شاخص های لازم برای اضافه کردن، پیشنهادات MS SQL بهینه سازی ممکن است نادرست باشد، از جمله در شرایط زیر:

1. اگر از قبل نمایه هایی با مجموعه ای از فیلدهای مشابه وجود داشته باشد
2. اگر فیلدهای جدول به دلیل محدودیت های نمایه سازی نمی توانند ایندکس شوند (با جزئیات بیشتر توضیح داده شده است اینجا).

2) ادغام ویژگی ها در یک ویژگی جدید

گاهی اوقات برخی از فیلدهای یک جدول، که به عنوان مبنایی برای گروهی از شرایط عمل می کنند، می توانند با معرفی یک فیلد جدید جایگزین شوند.

این به ویژه برای فیلدهای وضعیت، که معمولاً از نوع بیت یا عدد صحیح هستند، صادق است.

به عنوان مثال:

بسته شده = 0 و لغو شده = 0 و فعال = 0 جایگزین می شود با وضعیت = 1.

اینجاست که ویژگی وضعیت عدد صحیح معرفی می شود تا اطمینان حاصل شود که این وضعیت ها در جدول پر شده اند. در مرحله بعد، این ویژگی جدید ایندکس می شود.

این یک راه حل اساسی برای مشکل عملکرد است، زیرا ما بدون محاسبات غیر ضروری به داده ها دسترسی داریم.

3) مادی شدن دیدگاه

متاسفانه در جستارهای LINQ جداول موقت، CTEها و متغیرهای جدول را نمی توان مستقیما استفاده کرد.

با این حال، راه دیگری برای بهینه سازی برای این مورد وجود دارد - نماهای نمایه شده.

گروه شرط (از مثال بالا) بسته شده = 0 و لغو شده = 0 و فعال = 0 (یا مجموعه ای از شرایط مشابه دیگر) گزینه خوبی برای استفاده از آنها در نمای نمایه شده، ذخیره تکه کوچکی از داده از یک مجموعه بزرگ است.

اما تعدادی محدودیت برای تحقق یک دیدگاه وجود دارد:

1. استفاده از سوالات فرعی، بندها وجود دارد باید با استفاده جایگزین شود بپیوندید
2. شما نمی توانید از جملات استفاده کنید UNION, اتحاد همه, استثنا, تقاطع
3. شما نمی توانید از نکات و بندهای جدول استفاده کنید گزینه
4. امکان کار با چرخه ها وجود ندارد
5. نمایش داده ها در یک نمای از جداول مختلف غیرممکن است

مهم است که به یاد داشته باشید که سود واقعی استفاده از نمای نمایه شده تنها با نمایه سازی واقعی آن حاصل می شود.

اما در هنگام فراخوانی View ممکن است از این شاخص ها استفاده نشود و برای استفاده صریح از آنها باید مشخص کنید با (NOEXPAND).

از آنجا که در جستارهای LINQ تعریف نکات جدول غیرممکن است، بنابراین باید یک نمایش دیگر ایجاد کنید - یک "پوشش" به شکل زیر:

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

4) استفاده از توابع جدول

اغلب در جستارهای LINQ بلوک‌های بزرگی از زیرپرس و جوها یا بلوک‌هایی که از نماهایی با ساختار پیچیده استفاده می‌کنند، یک پرس‌وجو نهایی را با ساختار اجرای بسیار پیچیده و غیربهینه تشکیل می‌دهند.

مزایای کلیدی استفاده از توابع جدول در جستارهای LINQ:

1. توانایی، مانند نماها، استفاده و مشخص شدن به عنوان یک شی، اما می توانید مجموعه ای از پارامترهای ورودی را ارسال کنید:
   FROM FUNCTION (@param1، @param2 ...)
   در نتیجه می توان به نمونه گیری داده های انعطاف پذیر دست یافت
2. در مورد استفاده از تابع جدول، هیچ محدودیت قوی مانند نماهای نمایه شده که در بالا توضیح داده شد وجود ندارد:
   1. نکات جدول:
      از طریق LINQ شما نمی توانید مشخص کنید که کدام شاخص ها باید استفاده شوند و سطح جداسازی داده ها را هنگام پرس و جو تعیین کنید.
      اما تابع این قابلیت ها را دارد.
      با استفاده از این تابع، می توانید به یک طرح پرس و جو اجرای نسبتاً ثابت دست یابید که در آن قوانین کار با شاخص ها و سطوح جداسازی داده ها تعریف شده است.
   2. استفاده از تابع اجازه می دهد تا در مقایسه با نماهای نمایه شده، به دست آورید:
      • منطق نمونه گیری داده های پیچیده (حتی با استفاده از حلقه ها)
      • واکشی داده ها از جداول مختلف
      • использование UNION и وجود دارد

3. پیشنهاد گزینه زمانی که نیاز به ارائه کنترل همزمان داریم بسیار مفید است OPTION (MAXDOP N)، ترتیب طرح اجرای پرس و جو. مثلا:
   • می توانید ایجاد مجدد اجباری طرح پرس و جو را مشخص کنید OPTION (RECOMPILE)
   • می توانید تعیین کنید که آیا طرح پرس و جو را مجبور به استفاده از ترتیب پیوستن مشخص شده در پرس و جو کنید OPTION (سفارش اجباری)

   جزئیات بیشتر در مورد گزینه شرح داده شده اینجا.

4. استفاده از باریک ترین و مورد نیازترین برش داده:
   نیازی به ذخیره مجموعه داده های بزرگ در حافظه پنهان (مانند نماهای نمایه شده) نیست، که همچنان باید داده ها را بر اساس پارامتر فیلتر کنید.
   مثلا جدولی هست که فیلترش مکانی که در آن سه فیلد استفاده می شود (الف، ب، ج).

   به طور متعارف، همه درخواست ها دارای یک شرط ثابت هستند a = 0 و b = 0.

   با این حال، درخواست برای میدان c متغیر تر

   بگذارید شرط شود a = 0 و b = 0 این واقعاً به ما کمک می کند تا مجموعه مورد نیاز را به هزاران رکورد محدود کنیم، اما این شرط وجود دارد с انتخاب را به صد رکورد کاهش می دهد.

   در اینجا تابع جدول ممکن است گزینه بهتری باشد.

   همچنین، یک تابع جدول در زمان اجرا قابل پیش بینی تر و سازگارتر است.

نمونه

بیایید به اجرای نمونه با استفاده از پایگاه داده Questions به عنوان مثال نگاه کنیم.

یک درخواست وجود دارد انتخاب کنید، که چندین جدول را ترکیب می کند و از یک نمای (OperativeQuestions) استفاده می کند که در آن وابستگی با ایمیل (از طریق) بررسی می شود وجود دارد) به «سوالات عملیاتی»:

درخواست شماره 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 به ما اجازه می دهد تا انتخاب خروجی را محدود کنیم (از طریق وجود دارد) تا صدها رکورد.

و ممکن است به نظر برسد که پرس و جو باید یک بار رکوردها را توسط [Email] = @p__linq__0 محاسبه کند و سپس این چند صد رکورد باید توسط Id با Questions به هم متصل شوند و کوئری سریع خواهد بود.

در واقع، اتصال متوالی همه جداول وجود دارد: بررسی مطابقت سؤالات شناسه با شناسه از OperativeQuestions و فیلتر کردن از طریق ایمیل.

در واقع، این درخواست با تمام ده ها هزار رکورد 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();

در این مورد خاص، ما در حال بررسی راه حلی برای این مشکل بدون تغییرات زیرساختی، بدون معرفی جدول جداگانه با نتایج آماده ("Active Queries") هستیم که نیازمند مکانیزمی برای پر کردن آن با داده ها و به روز نگه داشتن آن است. .

اگرچه این راه حل خوبی است، اما گزینه دیگری برای بهینه سازی این مشکل وجود دارد.

هدف اصلی این است که ورودی های ذخیره شده توسط [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 بهینه باشند و طرح های پرس و جوی بهینه داشته باشند، یک ساختار دقیق مورد نیاز است و نه جدول به عنوان بازگشت...

در این حالت، Query 1 مورد نیاز به Query 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. کاهش میانگین زمان مسدود شدن به مقادیر قابل قبول

نتیجه

بهینه سازی و تنظیم دقیق تماس های پایگاه داده MS SQL از طریق LINQ مشکلی است که قابل حل است.

توجه و ثبات در این کار بسیار مهم است.

در ابتدای فرآیند:

1. لازم است داده هایی را که درخواست با آنها کار می کند بررسی کنید (مقادیر، انواع داده های انتخاب شده)
2. نمایه سازی مناسب این داده ها را انجام دهید
3. صحت شرایط اتصال بین جداول را بررسی کنید

تکرار بهینه سازی بعدی نشان می دهد:

1. بر اساس درخواست و تعریف فیلتر درخواست اصلی
2. تکرار بلوک های پرس و جو مشابه و تجزیه و تحلیل تقاطع شرایط
3. در SSMS یا رابط کاربری گرافیکی دیگر برای SQL سرور خود را بهینه می کند پرس و جوی SQL (تخصیص یک ذخیره سازی داده میانی، ساخت پرس و جو حاصل با استفاده از این ذخیره سازی (ممکن است چندین وجود داشته باشد))
4. در آخرین مرحله، به عنوان مبنای نتیجه پرس و جوی SQL، سازه در حال بازسازی است پرس و جو LINQ

نتیجه پرس و جو LINQ باید از نظر ساختار با بهینه شناسایی شده یکسان شود پرس و جوی SQL از نقطه 3

تقدیر و تشکر

با تشکر فراوان از همکاران jobgemws и alex_ozr از شرکت Fortis برای کمک در تهیه این مواد.

منبع: www.habr.com