یکی از جذاب‌ترین لحظات کار با LINQ و EF زمانی است که به خاطر افزایش حجم دیتا، کوئری خود را بازنگری کرده و آن را بهینه میکنید.
برای یک مسئله میتوان کوئری‌های متنوعی نوشت که همگی به یک جواب میرسند؛ ولی زمان اجرا و میزان حافظه‌ی مصرفی متفاوتی دارند. یک سناریوی رایج در نوشتن کوئری‌های LINQ، ترکیب اطلاعات جداول مختلف و محاسبه‌ی یک عدد معنی دار از ترکیب آن هاست.
برای نمونه دو Entity زیر را در مدل EF خود داریم:
موجودیت User، اطلاعات کاربر و موجودیت Login، اطلاعات مربوط به لوگین‌های هر کاربر را نگه می‌دارد. برای تست، یک دیتاست را به صورت تصادفی تولید کردیم که حاوی 1200 کاربر و 21000 لوگین هست.
برای تولید اطلاعات تصادفی میتوان از کد زیر در LINQPad استفاده کرد:
مسئله: نمایش اطلاعات پروفایل هر کاربر، به همراه تاریخ آخرین لوگین و تعداد کل لوگین‌های فرد
در سناریوهای این سبکی، باید خیلی با دقت عمل کرد و از تمام اطلاعات موجود استفاده کرد. اطلاعاتی که در اینجا برای ما مفید است، تعداد نسبی رکوردهای جداول دیتابیس است. مثلا در حال حاضر تعداد رکوردهای Logins تقریبا 17 برابر Users است و در آینده هم رشد Logins چند برابر Users خواهد بود. از طرفی در صورت مسئله، اطلاعات هر کاربر را میخواهیم، که به سادگی یک SELECT است. ولی بخش سنگین‌تر کوئری، محاسبه‌ی تعداد لوگین‌ها و تاریخ آخرین لوگین‌های هر فرد است که باز هم به جدول Logins بر میگردد.
روش اول:
راه حل اولی که به ذهن می‌رسد، JOIN کردن این دو جدول و محاسبه موارد لازم از ترکیب این دو جدول است:
کد SQL تولید شده‌ی در این روش، ترکیبی از 11 دستور SELECT تو در تو و 4 دستور LEFT OUTER JOIN است که ممکن است در حجم اطلاعات بیشتر، کوئری را با کندی همراه کند. نکته‌ی جالب توجه اینست که دستور group by ما در خروجی ظاهر نشده است و تبدیل به دستور SELECT تو در تو شده است که مورد انتظار ما نبوده است.
روش دوم:
روش دوم اینست که داده‌های سنگین‌تر (اطلاعات Logins) را ابتدا محاسبه کرده و سپس JOIN را انجام دهیم:
در روش دوم، ابتدا فقط به Logins کوئری میزنیم و برای محاسبه‌ی تعداد لوگین و آخرین لوگین، از Group By استفاده میکنیم. استفاده از این دستور باعث میشود که محاسبه‌ی سنگین ما در سریعترین حالت ممکن توسط SQL انجام شود. در مرحله‌ی بعد، این اطلاعات را با جدول Users از طریق LEFT OUTER JOIN ترکیب میکنیم. علت استفاده از DefaultIfEmpty بدین سبب است که برخی از کاربران ممکن است تاکنون لوگینی را انجام نداده باشند؛ در نتیجه باید تعداد صفر و تاریخ null برای آنها نمایش داده شود.
اکنون اگر کد SQL روش دوم را بررسی کنیم خواهیم دید که تنها 2 دستور SELECT ، یک LEFT OUTER JOIN به همراه یک GROUP BY تولید شده است که با توجه به ماهیت مسئله و ساختار دیتای ما، این دستورات منطقی‌ترین و بهینه‌ترین دستورات ممکن به نظر می‌رسد.
برای یک مسئله میتوان کوئری‌های متنوعی نوشت که همگی به یک جواب میرسند؛ ولی زمان اجرا و میزان حافظه‌ی مصرفی متفاوتی دارند. یک سناریوی رایج در نوشتن کوئری‌های 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; }
} برای تولید اطلاعات تصادفی میتوان از کد زیر در 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
}
); Generated SQL
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
}
); اکنون اگر کد SQL روش دوم را بررسی کنیم خواهیم دید که تنها 2 دستور SELECT ، یک LEFT OUTER JOIN به همراه یک GROUP BY تولید شده است که با توجه به ماهیت مسئله و ساختار دیتای ما، این دستورات منطقی‌ترین و بهینه‌ترین دستورات ممکن به نظر می‌رسد.
Generated SQL
پس، همواره کد SQL دستورات LINQ خود را یا از طریق SQL Profiler یا برنامه‌ای مثل LINQPad حتما تست کنید و کوئری خود را در مقابل حجم زیاد اطلاعات هم بررسی کنید. چرا که LINQ به علت سادگی و قدرتی که دارد، گاهی شما را به اشتباه می‌اندازد و باعث میشود شما کوئری ای بزنید که جواب شما را میدهد، ولی فقط برای حجم کم دیتای کنونی بهینه است و در صورت افزایش رکوردها، یا خیلی کند میشود یا کلا شما را با Timeout مواجه میکند.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 
