طرق تحسين استعلامات LINQ في C#.NET

مقدمة

В هذا المقال تم النظر في بعض طرق التحسين استعلامات لينك.
نقدم هنا أيضًا بعض الأساليب الإضافية لتحسين التعليمات البرمجية المتعلقة بـ استعلامات لينك.

ومن المعروف أن LINQ(الاستعلام المدمج باللغة) هي لغة بسيطة ومريحة للاستعلام عن مصدر بيانات.

А LINQ إلى SQL هي تقنية للوصول إلى البيانات في نظام إدارة قواعد البيانات (DBMS). هذه أداة قوية للتعامل مع البيانات، حيث يتم إنشاء الاستعلامات من خلال لغة تعريفية، والتي سيتم بعد ذلك تحويلها إلى استعلامات SQL منصة وإرسالها إلى خادم قاعدة البيانات للتنفيذ. في حالتنا، نعني بـ DBMS مرض التصلب العصبي المتعدد مزود خدمة.

ومع ذلك، استعلامات لينك لا يتم تحويلها إلى تلك المكتوبة على النحو الأمثل استعلامات SQL، والتي يمكن أن يكتبها DBA ذو الخبرة بكل الفروق الدقيقة في التحسين استعلامات SQL:

1. التوصيلات المثالية (الانضمام) وتصفية النتائج (WHERE)
2. العديد من الفروق الدقيقة في استخدام الاتصالات وشروط المجموعة
3. العديد من الاختلافات في استبدال الظروف IN في المشرقيةи ليس في، <> على المشرقية
4. التخزين المؤقت المتوسط ​​للنتائج عبر الجداول المؤقتة، CTE، ومتغيرات الجدول
5. استخدام الجملة (OPTION) مع التعليمات وتلميحات الجدول مع (...)
6. استخدام طرق العرض المفهرسة كإحدى الوسائل للتخلص من قراءات البيانات الزائدة أثناء التحديدات

اختناقات الأداء الرئيسية الناتجة استعلامات SQL عند التجميع استعلامات لينك هي:

1. توحيد آلية اختيار البيانات بأكملها في طلب واحد
2. تكرار كتل متطابقة من التعليمات البرمجية، مما يؤدي في النهاية إلى قراءة بيانات متعددة غير ضرورية
3. مجموعات من الشروط متعددة المكونات (المنطقية "و" و"أو") - لأي لبس и OR، يؤدي الدمج في ظروف معقدة إلى حقيقة أن المُحسِّن، الذي لديه فهارس غير مجمعة مناسبة للحقول الضرورية، يبدأ في النهاية في المسح مقابل الفهرس المجمع (مسح الفهرس) حسب مجموعات الشروط
4. التداخل العميق للاستعلامات الفرعية يجعل التحليل مشكلة كبيرة بيانات SQL وتحليل خطة الاستعلام من جانب المطورين و DBA

طرق التحسين

الآن دعنا ننتقل مباشرة إلى أساليب التحسين.

1) فهرسة إضافية

من الأفضل مراعاة عوامل التصفية في جداول التحديد الرئيسية، نظرًا لأنه في كثير من الأحيان يتم إنشاء الاستعلام بأكمله حول جدول واحد أو جدولين رئيسيين (التطبيقات - الأشخاص - العمليات) ومع مجموعة قياسية من الشروط (مغلق، ملغى، ممكّن، الحالة). ومن المهم إنشاء مؤشرات مناسبة للعينات المحددة.

يكون هذا الحل منطقيًا عند تحديد هذه الحقول مما يحد بشكل كبير من المجموعة التي تم إرجاعها إلى الاستعلام.

على سبيل المثال، لدينا 500000 طلب. ومع ذلك، لا يوجد سوى 2000 طلب نشط. ثم سيوفر لنا الفهرس المحدد بشكل صحيح مسح الفهرس على جدول كبير وسيسمح لك بتحديد البيانات بسرعة من خلال فهرس غير مجمع.

كما يمكن تحديد نقص الفهارس من خلال المطالبات بتحليل خطط الاستعلام أو جمع إحصائيات عرض النظام مرض التصلب العصبي المتعدد مزود خدمة:

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

تحتوي كافة بيانات العرض على معلومات حول الفهارس المفقودة، باستثناء الفهارس المكانية.

ومع ذلك، فإن الفهارس والتخزين المؤقت غالبًا ما تكون طرقًا لمكافحة عواقب الكتابة السيئة استعلامات لينك и استعلامات SQL.

كما تظهر ممارسات الحياة القاسية، غالبًا ما يكون من المهم للشركة تنفيذ ميزات العمل في مواعيد نهائية معينة. وبالتالي، غالبا ما يتم نقل الطلبات الثقيلة إلى الخلفية مع التخزين المؤقت.

وهذا له ما يبرره جزئيا، لأن المستخدم لا يحتاج دائما إلى أحدث البيانات وهناك مستوى مقبول من استجابة واجهة المستخدم.

يسمح هذا الأسلوب بحل احتياجات العمل، ولكنه في النهاية يقلل من أداء نظام المعلومات عن طريق تأخير حلول المشكلات.

ومن الجدير بالذكر أيضًا أنه أثناء عملية البحث عن الفهارس اللازمة لإضافتها، يتم تقديم الاقتراحات مرض التصلب العصبي المتعدد مزود قد يكون التحسين غير صحيح، بما في ذلك في ظل الشروط التالية:

1. إذا كانت هناك بالفعل فهارس تحتوي على مجموعة مماثلة من الحقول
2. إذا تعذر فهرسة الحقول الموجودة في الجدول بسبب قيود الفهرسة (الموصوفة بمزيد من التفصيل هنا).

2) دمج السمات في سمة واحدة جديدة

في بعض الأحيان، يمكن استبدال بعض الحقول من جدول واحد، والتي تكون بمثابة أساس لمجموعة من الشروط، بإدخال حقل واحد جديد.

وينطبق هذا بشكل خاص على حقول الحالة، والتي عادةً ما تكون من النوع بت أو عدد صحيح.

على سبيل المثال:

مغلق = 0 وملغى = 0 وممكن = 0 استبداله الحالة = 1.

هذا هو المكان الذي يتم فيه تقديم سمة الحالة الصحيحة للتأكد من ملء هذه الحالات في الجدول. وبعد ذلك، تتم فهرسة هذه السمة الجديدة.

يعد هذا حلاً أساسيًا لمشكلة الأداء، لأننا نصل إلى البيانات دون حسابات غير ضرورية.

3) تجسيد وجهة النظر

للأسف في استعلامات لينك لا يمكن استخدام الجداول المؤقتة وCTEs ومتغيرات الجدول مباشرة.

ومع ذلك، هناك طريقة أخرى لتحسين هذه الحالة - طرق العرض المفهرسة.

مجموعة الشروط (من المثال أعلاه) مغلق = 0 وملغى = 0 وممكن = 0 (أو مجموعة من الشروط المشابهة الأخرى) تصبح خيارًا جيدًا لاستخدامها في طريقة عرض مفهرسة، مع تخزين شريحة صغيرة من البيانات مؤقتًا من مجموعة كبيرة.

ولكن هناك عددًا من القيود عند تجسيد وجهة النظر:

1. استخدام الاستعلامات الفرعية والبنود المشرقية ينبغي استبداله باستخدام الانضمام
2. لا يمكنك استخدام الجمل UNION, اتحاد الجميع, استثناء, تتقاطع
3. لا يمكنك استخدام تلميحات الجدول والبنود OPTION
4. لا توجد إمكانية للعمل مع دورات
5. من المستحيل عرض البيانات في طريقة عرض واحدة من جداول مختلفة

من المهم أن تتذكر أن الفائدة الحقيقية من استخدام طريقة العرض المفهرسة لا يمكن تحقيقها إلا من خلال فهرستها فعليًا.

ولكن عند استدعاء طريقة عرض، لا يجوز استخدام هذه الفهارس، ويجب تحديدها لاستخدامها بشكل صريح مع (عدم التوسيع).

منذ ذلك الحين في استعلامات لينك من المستحيل تحديد تلميحات الجدول، لذلك يتعين عليك إنشاء تمثيل آخر - "غلاف" للنموذج التالي:

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

4) استخدام وظائف الجدول

في كثير من الأحيان استعلامات لينك تشكل الكتل الكبيرة من الاستعلامات الفرعية أو الكتل التي تستخدم طرق العرض ذات البنية المعقدة استعلامًا نهائيًا ببنية تنفيذ معقدة للغاية ودون المستوى الأمثل.

الفوائد الرئيسية لاستخدام وظائف الجدول في استعلامات لينك:

1. القدرة، كما في حالة طرق العرض، على استخدامها وتحديدها ككائن، ولكن يمكنك تمرير مجموعة من معلمات الإدخال:
   من الوظيفة (@param1، @param2 ...)
   ونتيجة لذلك، يمكن تحقيق أخذ عينات بيانات مرنة
2. في حالة استخدام دالة الجدول، لا توجد قيود قوية كما في حالة العروض المفهرسة الموضحة أعلاه:
   1. تلميحات الجدول:
      من خلال LINQ لا يمكنك تحديد الفهارس التي يجب استخدامها وتحديد مستوى عزل البيانات عند الاستعلام.
      ولكن الوظيفة لديها هذه القدرات.
      باستخدام الوظيفة، يمكنك تحقيق خطة استعلام تنفيذ ثابتة إلى حد ما، حيث يتم تحديد قواعد العمل مع الفهارس ومستويات عزل البيانات
   2. يسمح استخدام الدالة، بالمقارنة مع طرق العرض المفهرسة، بالحصول على:
      • منطق أخذ عينات البيانات المعقد (حتى باستخدام الحلقات)
      • جلب البيانات من العديد من الجداول المختلفة
      • استخدم UNION и المشرقية

3. اقتراح OPTION مفيد جدًا عندما نحتاج إلى توفير التحكم في التزامن الخيار (ماكسدوب ن)، ترتيب خطة تنفيذ الاستعلام. على سبيل المثال:
   • يمكنك تحديد إعادة الإنشاء القسري لخطة الاستعلام الخيار (إعادة الترجمة)
   • يمكنك تحديد ما إذا كان سيتم فرض خطة الاستعلام لاستخدام ترتيب الانضمام المحدد في الاستعلام الخيار (فرض الأمر)

   مزيد من التفاصيل حول OPTION وصفها هنا.

4. استخدام شريحة البيانات الأضيق والأكثر طلبًا:
   ليست هناك حاجة لتخزين مجموعات كبيرة من البيانات في ذاكرات التخزين المؤقت (كما هو الحال مع طرق العرض المفهرسة)، حيث لا تزال بحاجة إلى تصفية البيانات حسب المعلمة.
   على سبيل المثال، هناك جدول مرشحه WHERE يتم استخدام ثلاثة مجالات (أ، ب، ج).

   تقليديا، جميع الطلبات لها شرط ثابت أ = 0 و ب = 0.

   ومع ذلك، فإن الطلب في هذا المجال c أكثر متغيرة.

   دع الشرط أ = 0 و ب = 0 إنه يساعدنا حقًا على قصر المجموعة الناتجة المطلوبة على آلاف السجلات، ولكن الشرط قيد التشغيل с يضيق التحديد وصولاً إلى مائة سجل.

   هنا قد تكون وظيفة الجدول خيارًا أفضل.

   كما أن دالة الجدول أكثر قابلية للتنبؤ بها وأكثر اتساقًا في وقت التنفيذ.

أمثلة

دعونا نلقي نظرة على مثال للتنفيذ باستخدام قاعدة بيانات الأسئلة كمثال.

هناك طلب اختر، والذي يجمع بين عدة جداول ويستخدم عرضًا واحدًا (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 بالحد من اختيار الإخراج (عبر المشرقية) ما يصل إلى مئات السجلات.

وقد يبدو أن الاستعلام الفرعي يجب أن يحسب السجلات مرة واحدة عن طريق [البريد الإلكتروني] = @p__linq__0، ثم يجب ربط هذه السجلات التي يبلغ عددها بضع مئات بواسطة معرف مع الأسئلة، وسيكون الاستعلام سريعًا.

في الواقع، هناك اتصال تسلسلي لجميع الجداول: التحقق من تطابق أسئلة المعرف مع المعرف من 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. تقليل متوسط ​​وقت الحظر إلى قيم مقبولة

إنتاج

تحسين وضبط مكالمات قاعدة البيانات مرض التصلب العصبي المتعدد مزود من خلال LINQ هي مشكلة يمكن حلها.

الاهتمام والاتساق مهمان جدًا في هذا العمل.

في بداية العملية:

1. من الضروري التحقق من البيانات التي يعمل بها الطلب (القيم، أنواع البيانات المحددة)
2. إجراء الفهرسة الصحيحة لهذه البيانات
3. التحقق من صحة شروط الربط بين الجداول

يكشف تكرار التحسين التالي:

1. أساس الطلب ويحدد مرشح الطلب الرئيسي
2. تكرار كتل الاستعلام المماثلة وتحليل تقاطع الشروط
3. في SSMS أو واجهة المستخدم الرسومية الأخرى ملقم SQL يحسن نفسه استعلام SQL (تخصيص مخزن بيانات وسيط، وإنشاء الاستعلام الناتج باستخدام هذا التخزين (قد يكون هناك العديد))
4. في المرحلة الأخيرة، مع الأخذ كأساس للنتيجة استعلام SQL، يتم إعادة بناء الهيكل استعلام لينك

النتيجة استعلام LINQ يجب أن تصبح متطابقة في الهيكل مع الأمثل المحدد استعلام SQL من النقطة 3.

شكر وتقدير

شكرا جزيلا للزملاء com.jobgemws и alex_ozr من الشركة فورتيس للمساعدة في إعداد هذه المادة.

المصدر: www.habr.com