بهینه سازی کوئری LINQ

یکی از جذاب‌ترین لحظات کار با LINQ و EF زمانی است که به خاطر افزایش حجم دیتا، کوئری خود را بازنگری کرده و آن را بهینه می‌کنید.

برای یک مسئله می‌توان کوئری‌های متنوعی نوشت که همگی به یک جواب میرسند؛ ولی زمان اجرا و میزان حافظه‌ی مصرفی متفاوتی دارند. یک سناریوی رایج در نوشتن کوئری‌های LINQ، ترکیب اطلاعات جداول مختلف و محاسبه‌ی یک عدد معنی دار از ترکیب آن هاست.

برای نمونه دو Entity زیر را در مدل EF خود داریم:

public class User
{
            public int ID { get; set; }
            public string Name { get; set; }
            public int Age { get; set; }
}
public class Login
{
            public int ID { get; set; }
            public DateTime Date { get; set; }
            public int UserID { get; set; }
            public User User { get; set; }
}

موجودیت User، اطلاعات کاربر و موجودیت Login، اطلاعات مربوط به لوگین‌های هر کاربر را نگه می‌دارد. برای تست، یک دیتاست را به صورت تصادفی تولید کردیم که حاوی 1200 کاربر و 21000 لوگین هست.

برای تولید اطلاعات تصادفی می‌توان از کد زیر در LINQPad استفاده کرد:

int usersCount = 1200;
Random rnd = new Random();
for(int i=0; i<usersCount; i++)
{
   Users.Add(new User()
                        {
       Name = $"User {i + 1}",
       Age = rnd.Next(10, i + 10) / 10
                        });
}
SaveChanges();
$"Users: {Users.Count()}".Dump();
var usersID = Users.Select(x => x.ID).ToArray();
int loginsCount  = 20000;
for(int i=0; i<loginsCount; i++)
{
    Logins.Add(new Login()
                        {
        UserID = usersID[rnd.Next(0, usersID.Length - 1)],
        Date = DateTime.Now.AddDays(rnd.Next(0, i))
                        });
                        if(i % 1000 == 0)
                        {
                        SaveChanges();
      $"Save {i + 1}".Dump();
                        }
}
SaveChanges();
$"Logins: {Logins.Count()}".Dump();

$"Users: {Users.Count()}".Dump();
$"Logins: {Logins.Count()}".Dump();
Users: 1200
Logins: 21000

مسئله: نمایش اطلاعات پروفایل هر کاربر، به همراه تاریخ آخرین لوگین و تعداد کل لوگین‌های فرد

در سناریوهای این سبکی، باید خیلی با دقت عمل کرد و از تمام اطلاعات موجود استفاده کرد. اطلاعاتی که در اینجا برای ما مفید است، تعداد نسبی رکوردهای جداول دیتابیس است. مثلا در حال حاضر تعداد رکوردهای Logins تقریبا 17 برابر Users است و در آینده هم رشد Logins چند برابر Users خواهد بود. از طرفی در صورت مسئله، اطلاعات هر کاربر را می‌خواهیم، که به سادگی یک SELECT است. ولی بخش سنگین‌تر کوئری، محاسبه‌ی تعداد لوگین‌ها و تاریخ آخرین لوگین‌های هر فرد است که باز هم به جدول Logins بر می‌گردد.

روش اول:

راه حل اولی که به ذهن می‌رسد، JOIN کردن این دو جدول و محاسبه موارد لازم از ترکیب این دو جدول است:

var data =
(
            from u in Users
            join x in Logins on u.ID equals x.UserID into g
            from x in g.DefaultIfEmpty()
            select new
            {
        UserID = u.ID,
        Name = u.Name,
        Age = u.Age,
        Date = x.Date
            }
);
var result =
(
            from d in data
            group d by d.UserID into g
            select new
            {
       UserID = g.Key,
       Name = g.FirstOrDefault().Name,
       LoginsCount = g.Count(x => x.Date != null),
       LastLogin = g.Max(x => (DateTime?) x.Date) ?? null
            }
);

کد SQL تولید شده‌ی در این روش، ترکیبی از 11 دستور SELECT تو در تو و 4 دستور LEFT OUTER JOIN است که ممکن است در حجم اطلاعات بیشتر، کوئری را با کندی همراه کند. نکته‌ی جالب توجه اینست که دستور group by ما در خروجی ظاهر نشده است و تبدیل به دستور SELECT تو در تو شده است که مورد انتظار ما نبوده است

SELECT
    [Project7].[ID] AS [ID],
    [Project7].[C2] AS [C1],
    [Project7].[C3] AS [C2],
    [Project7].[C1] AS [C3]
    FROM ( SELECT
        [Project6].[ID] AS [ID],
        CASE WHEN ([Project6].[C3] IS NULL) THEN CAST(NULL AS datetime2) ELSE [Project6].[C4] END AS [C1],
        [Project6].[C1] AS [C2],
        [Project6].[C2] AS [C3]
        FROM ( SELECT
            [Project5].[ID] AS [ID],
            [Project5].[C1] AS [C1],
            [Project5].[C2] AS [C2],
            [Project5].[C3] AS [C3],
            (SELECT
                MAX( CAST( [Extent9].[Date] AS datetime2)) AS [A1]
                FROM  [dbo].[Users] AS [Extent8]
                LEFT OUTER JOIN [dbo].[Logins] AS [Extent9] ON [Extent8].[ID] = [Extent9].[UserID]
                WHERE [Project5].[ID] = [Extent8].[ID]) AS [C4]
            FROM ( SELECT
                [Project4].[ID] AS [ID],
                [Project4].[C1] AS [C1],
                [Project4].[C2] AS [C2],
                (SELECT
                    MAX( CAST( [Extent7].[Date] AS datetime2)) AS [A1]
                    FROM  [dbo].[Users] AS [Extent6]
                    LEFT OUTER JOIN [dbo].[Logins] AS [Extent7] ON [Extent6].[ID] = [Extent7].[UserID]
                    WHERE [Project4].[ID] = [Extent6].[ID]) AS [C3]
                FROM ( SELECT
                    [Project3].[ID] AS [ID],
                    [Project3].[C1] AS [C1],
                    (SELECT
                        COUNT(1) AS [A1]
                        FROM [dbo].[Logins] AS [Extent5]
                        WHERE [Project3].[ID] = [Extent5].[UserID]) AS [C2]
                    FROM ( SELECT
                        [Distinct1].[ID] AS [ID],
                        (SELECT TOP (1)
                            [Extent3].[Name] AS [Name]
                            FROM  [dbo].[Users] AS [Extent3]
                            LEFT OUTER JOIN [dbo].[Logins] AS [Extent4] ON [Extent3].[ID] = [Extent4].[UserID]
                            WHERE [Distinct1].[ID] = [Extent3].[ID]) AS [C1]
                        FROM ( SELECT DISTINCT
                            [Extent1].[ID] AS [ID]
                            FROM  [dbo].[Users] AS [Extent1]
                            LEFT OUTER JOIN [dbo].[Logins] AS [Extent2] ON [Extent1].[ID] = [Extent2].[UserID]
                        )  AS [Distinct1]
                    )  AS [Project3]
                )  AS [Project4]
            )  AS [Project5]
        )  AS [Project6]
    )  AS [Project7]
    ORDER BY [Project7].[C3] ASC, [Project7].[ID] ASC

روش دوم:

روش دوم اینست که داده‌های سنگین‌تر (اطلاعات Logins) را ابتدا محاسبه کرده و سپس JOIN را انجام دهیم:

var data =
(
            from x in Logins
            group x by x.UserID into g
            orderby g.Key descending
            select new
            {
    UserID = g.Key,
    LoginsCount = g.Count(),
    LastLogin = g.Max(d => d.Date)
            }
);
var result =
(
            from u in Users
            join d in data on u.ID equals d.UserID into g
            from d in g.DefaultIfEmpty()
            select new
            {
    UserID = u.ID,
    LoginsCount = d != null ? d.LoginsCount : 0,
    LastLogin = d != null ? (DateTime?)d.LastLogin : null
            }
);

در روش دوم، ابتدا فقط به Logins کوئری می‌زنیم و برای محاسبه‌ی تعداد لوگین و آخرین لوگین، از Group By استفاده می‌کنیم. استفاده از این دستور باعث می‌شود که محاسبه‌ی سنگین ما در سریعترین حالت ممکن توسط SQL انجام شود. در مرحله‌ی بعد، این اطلاعات را با جدول Users از طریق LEFT OUTER JOIN ترکیب می‌کنیم. علت استفاده از DefaultIfEmpty بدین سبب است که برخی از کاربران ممکن است تاکنون لوگینی را انجام نداده باشند؛ در نتیجه باید تعداد صفر و تاریخ null برای آنها نمایش داده شود.

اکنون اگر کد SQL روش دوم را بررسی کنیم خواهیم دید که تنها 2 دستور SELECT ، یک LEFT OUTER JOIN به همراه یک GROUP BY تولید شده است که با توجه به ماهیت مسئله و ساختار دیتای ما، این دستورات منطقی‌ترین و بهینه‌ترین دستورات ممکن به نظر می‌رسد.

SELECT
        [Project1].[ID] AS [ID],
        [Project1].[C1] AS [C1],
        [Project1].[C2] AS [C2]
        FROM ( SELECT
        [Extent1].[ID] AS [ID],
        CASE WHEN ([GroupBy1].[K1] IS NOT NULL) THEN [GroupBy1].[A1] ELSE 0 END AS [C1],
        CASE WHEN ([GroupBy1].[K1] IS NOT NULL) THEN  CAST( [GroupBy1].[A2] AS datetime2) END AS [C2]
        FROM  [dbo].[Users] AS [Extent1]
        LEFT OUTER JOIN  (SELECT
        [Extent2].[UserID] AS [K1],
        COUNT(1) AS [A1],
        MAX([Extent2].[Date]) AS [A2]
        FROM [dbo].[Logins] AS [Extent2]
        GROUP BY [Extent2].[UserID] ) AS [GroupBy1] ON [Extent1].[ID] = [GroupBy1].[K1]
        )  AS [Project1]
        ORDER BY [Project1].[C1] ASC, [Project1].[ID] ASC

پس، همواره کد SQL دستورات LINQ خود را یا از طریق SQL Profiler یا برنامه‌ای مثل LINQPad حتما تست کنید و کوئری خود را در مقابل حجم زیاد اطلاعات هم بررسی کنید. چرا که LINQ به علت سادگی و قدرتی که دارد، گاهی شما را به اشتباه می‌اندازد و باعث می‌شود شما کوئری ای بزنید که جواب شما را می‌دهد، ولی فقط برای حجم کم دیتای کنونی بهینه است و در صورت افزایش رکوردها، یا خیلی کند می‌شود یا کلا شما را با Timeout مواجه می‌کند.

منبع : .netTip

نرم افزار هوش تجاری , داشبورد مدیران , داشبورد مدیریتی , گزارش ساز , نرم افزار داشبورد

نرم افزار کلیک ویو ,آموزش کلیک ویو , هوش تجاری کلیک ویو , فیلم کلیک وی , دانلود کلیک ویو

نرم افزار مدیریت فرایند ها , مدیریت فرایند , سامانه ساز , پنجره واحد ,دولت الکترونیک , معماری سازمان , سیستم ساز