LINQ เข้าสู่ .NET ในฐานะภาษาการจัดการข้อมูลใหม่ที่ทรงพลัง LINQ เป็น SQL ซึ่งเป็นส่วนหนึ่งของมันทำให้คุณสามารถสื่อสารกับ DBMS ได้ค่อนข้างสะดวกโดยใช้ Entity Framework เช่น อย่างไรก็ตาม เมื่อใช้บ่อยครั้ง นักพัฒนาลืมดูว่าจะสร้างการสืบค้น SQL ประเภทใดที่ผู้ให้บริการที่สามารถสืบค้นได้ ในกรณีของคุณ Entity Framework จะสร้าง
ลองดูประเด็นหลักสองประเด็นโดยใช้ตัวอย่าง
เมื่อต้องการทำเช่นนี้ ให้สร้างฐานข้อมูลทดสอบใน 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 โดยการรันสคริปต์ต่อไปนี้:
กรอกตารางอ้างอิง
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 Studio คุณต้องสร้างโครงการทดสอบ Visual C# Console App (.NET Framework):
ถัดไป คุณต้องเพิ่มไลบรารีสำหรับ Entity Framework เพื่อโต้ตอบกับฐานข้อมูล
หากต้องการเพิ่ม ให้คลิกขวาที่โปรเจ็กต์แล้วเลือกจัดการแพ็คเกจ NuGet จากเมนูบริบท:
จากนั้นในหน้าต่างการจัดการแพ็คเกจ 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; }
}
}
ต่อไป เราจะสร้างเอนทิตีทั้งสองของเราในไฟล์แยกกัน:
- การดำเนินการของคลาสอ้างอิง
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 ไปยัง MS SQL Server DBMS ได้ค่อนข้างดี
ตอนนี้เรามาเปลี่ยนเงื่อนไข AND เป็น OR ในการสืบค้น 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 };
และมาเปิดแอปพลิเคชันของเราอีกครั้ง
การดำเนินการจะล้มเหลวโดยมีข้อผิดพลาดเนื่องจากเวลาดำเนินการคำสั่งเกิน 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 (ตัวเลือกที่ 1)
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 (ตัวเลือกที่ 2)
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
น่าเสียดายที่แม้แต่นักพัฒนา .NET ชั้นนำที่มีประสบการณ์และเป็นผู้นำก็ลืมไปว่าพวกเขาจำเป็นต้องเข้าใจว่าคำแนะนำที่พวกเขาใช้ทำอะไรอยู่เบื้องหลัง มิฉะนั้น พวกเขาจะกลายเป็นผู้กำหนดค่าและสามารถสร้างระเบิดเวลาได้ในอนาคต ทั้งเมื่อปรับขนาดโซลูชันซอฟต์แวร์และเมื่อมีการเปลี่ยนแปลงเล็กน้อยในสภาวะแวดล้อมภายนอก
มีการทบทวนสั้นๆ ด้วย .
แหล่งที่มาของการทดสอบ - ตัวโครงการ, การสร้างตารางในฐานข้อมูล TEST รวมถึงการกรอกตารางเหล่านี้ด้วยข้อมูล .
นอกจากนี้ ในพื้นที่เก็บข้อมูลนี้ ในโฟลเดอร์ Plans มีแผนสำหรับการดำเนินการค้นหาด้วยเงื่อนไข OR
ที่มา: will.com