LINQ به دات نت به عنوان یک زبان جدید قدرتمند برای دستکاری داده ها وارد شد. LINQ به SQL به عنوان بخشی از آن به شما این امکان را می دهد که به راحتی با یک DBMS با استفاده از Entity Framework ارتباط برقرار کنید. با این حال، اغلب با استفاده از آن، توسعه‌دهندگان فراموش می‌کنند که ببینند ارائه‌دهنده پرسش‌پذیر، در مورد شما Entity Framework، چه نوع پرس‌وجوی SQL را ایجاد می‌کند.
بیایید با استفاده از یک مثال به دو نکته اصلی نگاه کنیم.
برای انجام این کار، یک پایگاه داده تست در SQL Server ایجاد کنید و با استفاده از کوئری زیر دو جدول در آن ایجاد کنید:
ایجاد جداول
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ref](
[ID] [int] NOT NULL,
[ID2] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
CONSTRAINT [PK_Ref] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ref] ADD CONSTRAINT [DF_Ref_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Ref_ID] [int] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
[Ref_ID2] [int] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_Ref_ID] DEFAULT ((0)) FOR [Ref_ID]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
حالا بیایید جدول Ref را با اجرای اسکریپت زیر پر کنیم:
پر کردن جدول Ref
USE [TEST]
GO
DECLARE @ind INT=1;
WHILE(@ind<1200000)
BEGIN
INSERT INTO [dbo].[Ref]
([ID]
,[ID2]
,[Name])
SELECT
@ind
,@ind
,CAST(@ind AS NVARCHAR(255));
SET @ind=@ind+1;
END
GO
بیایید به طور مشابه جدول مشتری را با استفاده از اسکریپت زیر پر کنیم:
پر کردن جدول مشتری
USE [TEST]
GO
DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;
WHILE(@ind<=12000000)
BEGIN
IF(@ind%3=0) SET @ind_ref=1;
ELSE IF (@ind%5=0) SET @ind_ref=2;
ELSE IF (@ind%7=0) SET @ind_ref=3;
ELSE IF (@ind%11=0) SET @ind_ref=4;
ELSE IF (@ind%13=0) SET @ind_ref=5;
ELSE IF (@ind%17=0) SET @ind_ref=6;
ELSE IF (@ind%19=0) SET @ind_ref=7;
ELSE IF (@ind%23=0) SET @ind_ref=8;
ELSE IF (@ind%29=0) SET @ind_ref=9;
ELSE IF (@ind%31=0) SET @ind_ref=10;
ELSE IF (@ind%37=0) SET @ind_ref=11;
ELSE SET @ind_ref=@ind%1190000;
INSERT INTO [dbo].[Customer]
([ID]
,[Name]
,[Ref_ID]
,[Ref_ID2])
SELECT
@ind,
CAST(@ind AS NVARCHAR(255)),
@ind_ref,
@ind_ref;
SET @ind=@ind+1;
END
GO
بنابراین دو جدول دریافت کردیم که یکی از آنها بیش از 1 میلیون ردیف داده و دیگری بیش از 10 میلیون ردیف داده دارد.
اکنون در ویژوال استودیو باید یک پروژه آزمایشی Visual C# Console App (.NET Framework) ایجاد کنید:
در مرحله بعد، باید یک کتابخانه برای Entity Framework اضافه کنید تا با پایگاه داده تعامل داشته باشد.
برای اضافه کردن آن، روی پروژه کلیک راست کرده و از منوی زمینه، Manage NuGet Packages را انتخاب کنید:
سپس در پنجره مدیریت بسته NuGet که ظاهر می شود، کلمه Entity Framework را در پنجره جستجو وارد کرده و بسته Entity Framework را انتخاب کرده و نصب کنید:
بعد، در فایل App.config، پس از بستن عنصر configSections، باید بلوک زیر را اضافه کنید:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
در ConnectionString باید رشته اتصال را وارد کنید.
حالا بیایید 3 رابط در فایل های جداگانه ایجاد کنیم:
- پیاده سازی رابط IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - پیاده سازی رابط IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - پیاده سازی رابط IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
و در یک فایل جداگانه یک کلاس پایه BaseEntity برای دو موجودیت خود ایجاد می کنیم که شامل فیلدهای مشترک است:
پیاده سازی کلاس پایه BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
بعد، ما دو موجودیت خود را در فایل های جداگانه ایجاد می کنیم:
- پیاده سازی کلاس Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - پیاده سازی کلاس مشتری
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Customer")] public class Customer: BaseEntity { public int Ref_ID { get; set; } public int Ref_ID2 { get; set; } } }
حال بیایید یک زمینه UserContext در یک فایل جداگانه ایجاد کنیم:
پیاده سازی کلاس UserContex
using System.Data.Entity;
namespace TestLINQ
{
public class UserContext : DbContext
{
public UserContext()
: base("DbConnection")
{
Database.SetInitializer<UserContext>(null);
}
public DbSet<Customer> Customer { get; set; }
public DbSet<Ref> Ref { get; set; }
}
}
ما یک راه حل آماده برای انجام تست های بهینه سازی با LINQ به SQL از طریق EF برای MS SQL Server دریافت کردیم:
حالا کد زیر را در فایل Program.cs وارد کنید:
فایل Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
namespace TestLINQ
{
class Program
{
static void Main(string[] args)
{
using (UserContext db = new UserContext())
{
var dblog = new List<string>();
db.Database.Log = dblog.Add;
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
var result = query.Take(1000).ToList();
Console.WriteLine(dblog[1]);
Console.ReadKey();
}
}
}
}
بعد، بیایید پروژه خود را راه اندازی کنیم.
در پایان کار موارد زیر بر روی کنسول نمایش داده می شود:
پرس و جوی SQL ایجاد شد
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) AND ([Extent1].[Ref_ID2] = [Extent2].[ID2])
یعنی به طور کلی، پرس و جوی LINQ یک پرس و جوی SQL را برای DBMS سرور MS SQL به خوبی ایجاد می کند.
حال بیایید شرط AND را در کوئری LINQ به OR تغییر دهیم:
پرس و جو LINQ
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
|| (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
و بیایید دوباره برنامه خود را راه اندازی کنیم.
به دلیل زمان اجرای فرمان بیش از 30 ثانیه، اجرا با یک خطا از کار می افتد:
اگر به پرسشی که توسط LINQ ایجاد شده است نگاه کنید:
، سپس می توانید مطمئن شوید که انتخاب از طریق حاصل ضرب دکارتی دو مجموعه (جدول) انجام می شود:
پرس و جوی SQL ایجاد شد
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
CROSS JOIN [dbo].[Ref] AS [Extent2]
WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]
بیایید کوئری LINQ را به صورت زیر بازنویسی کنیم:
پرس و جوی LINQ بهینه شده
var query = (from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID equals e2.ID
select new { Data1 = e1.Name, Data2 = e2.Name }).Union(
from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID2 equals e2.ID2
select new { Data1 = e1.Name, Data2 = e2.Name });
سپس پرس و جوی SQL زیر را دریافت می کنیم:
پرس و جوی SQL
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[Limit1].[C3] AS [C3]
FROM ( SELECT DISTINCT TOP (1000)
[UnionAll1].[C1] AS [C1],
[UnionAll1].[Name] AS [C2],
[UnionAll1].[Name1] AS [C3]
FROM (SELECT
1 AS [C1],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
UNION ALL
SELECT
1 AS [C1],
[Extent3].[Name] AS [Name],
[Extent4].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent3]
INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
) AS [Limit1]
افسوس، در جستارهای LINQ فقط یک شرط اتصال می تواند وجود داشته باشد، بنابراین در اینجا می توان با استفاده از دو پرس و جو برای هر شرط یک پرس و جو معادل ایجاد کرد و سپس آنها را از طریق Union ترکیب کرد تا موارد تکراری در بین ردیف ها حذف شود.
بله، با در نظر گرفتن اینکه ممکن است سطرهای تکراری کامل برگردانده شوند، پرس و جوها معمولاً معادل نیستند. با این حال، در زندگی واقعی، خطوط تکراری کامل مورد نیاز نیست و مردم سعی می کنند از شر آنها خلاص شوند.
حال بیایید برنامه های اجرایی این دو پرس و جو را با هم مقایسه کنیم:
- برای CROSS JOIN میانگین زمان اجرا 195 ثانیه است:
- برای INNER JOIN-UNION میانگین زمان اجرا کمتر از 24 ثانیه است:
همانطور که از نتایج مشاهده می کنید، برای دو جدول با میلیون ها رکورد، کوئری LINQ بهینه شده چندین برابر سریعتر از جدول بهینه نشده است.
برای گزینه با AND در شرایط، یک پرس و جو LINQ از فرم:
پرس و جو LINQ
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
پرس و جوی صحیح SQL تقریباً همیشه تولید می شود که به طور متوسط در حدود 1 ثانیه اجرا می شود:
همچنین برای دستکاری های LINQ to Objects به جای پرس و جو مانند:
پرس و جو LINQ (گزینه اول)
var query = from e1 in seq1
from e2 in seq2
where (e1.Key1==e2.Key1)
&& (e1.Key2==e2.Key2)
select new { Data1 = e1.Data, Data2 = e2.Data };
می توانید از یک پرس و جو مانند:
پرس و جو LINQ (گزینه اول)
var query = from e1 in seq1
join e2 in seq2
on new { e1.Key1, e1.Key2 } equals new { e2.Key1, e2.Key2 }
select new { Data1 = e1.Data, Data2 = e2.Data };
که در آن:
تعریف دو آرایه
Para[] seq1 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 4, Data = "999" } };
Para[] seq2 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 5, Data = "999" } };
و نوع Para به صورت زیر تعریف می شود:
تعریف نوع پارا
class Para
{
public int Key1, Key2;
public string Data;
}
بنابراین، ما برخی از جنبه‌ها را در بهینه‌سازی کوئری‌های LINQ به MS SQL Server بررسی کردیم.
متأسفانه، حتی توسعه دهندگان باتجربه و پیشرو دات نت فراموش می کنند که باید بفهمند دستورالعمل هایی که استفاده می کنند در پشت صحنه چه می کنند. در غیر این صورت، آنها به پیکربندی‌کننده تبدیل می‌شوند و می‌توانند در آینده بمب ساعتی را هم هنگام مقیاس‌بندی راه‌حل نرم‌افزاری و هم با تغییرات جزئی در شرایط محیطی خارجی نصب کنند.
بررسی کوتاهی نیز انجام شد .
منابع آزمایش - خود پروژه، ایجاد جداول در پایگاه داده TEST و همچنین پر کردن این جداول با داده ها قرار دارند. .
همچنین در این مخزن در پوشه Plans پلان هایی برای اجرای کوئری ها با شرایط OR وجود دارد.
منبع: www.habr.com