LINQ đã đưa .NET vào làm ngôn ngữ thao tác dữ liệu mới mạnh mẽ. LINQ to SQL là một phần của nó cho phép bạn giao tiếp khá thuận tiện với DBMS bằng cách sử dụng Entity Framework, chẳng hạn như. Tuy nhiên, việc sử dụng nó khá thường xuyên, các nhà phát triển quên xem loại truy vấn SQL nào mà nhà cung cấp có thể truy vấn, trong trường hợp của bạn là Entity Framework, sẽ tạo ra.
Hãy xem xét hai điểm chính bằng một ví dụ.
Để thực hiện việc này, hãy tạo cơ sở dữ liệu Kiểm tra trong SQL Server và tạo hai bảng trong đó bằng truy vấn sau:
Tạo bảng
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
Bây giờ hãy điền vào bảng Ref bằng cách chạy tập lệnh sau:
Điền vào bảng 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
Tương tự, hãy điền vào bảng Khách hàng bằng cách sử dụng tập lệnh sau:
Điền vào bảng Khách hàng
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
Do đó, chúng tôi nhận được hai bảng, một bảng có hơn 1 triệu hàng dữ liệu và bảng còn lại có hơn 10 triệu hàng dữ liệu.
Bây giờ trong Visual Studio, bạn cần tạo dự án Visual C# Console App (.NET Framework) thử nghiệm:
Tiếp theo, bạn cần thêm thư viện để Entity Framework tương tác với cơ sở dữ liệu.
Để thêm nó, nhấp chuột phải vào dự án và chọn Quản lý gói NuGet từ menu ngữ cảnh:
Sau đó, trong cửa sổ quản lý gói NuGet hiện ra, bạn nhập từ “Entity Framework” vào cửa sổ tìm kiếm rồi chọn gói Entity Framework và cài đặt:
Tiếp theo, trong tệp App.config, sau khi đóng phần tử configSections, bạn cần thêm khối sau:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Trong ConnectionString bạn cần nhập chuỗi kết nối.
Bây giờ hãy tạo 3 giao diện trong các tệp riêng biệt:
- Triển khai giao diện IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Triển khai giao diện IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Triển khai giao diện IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Và trong một tệp riêng biệt, chúng ta sẽ tạo một lớp cơ sở BaseEntity cho hai thực thể của mình, bao gồm các trường chung:
Triển khai lớp cơ sở BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Tiếp theo, chúng ta sẽ tạo hai thực thể trong các tệp riêng biệt:
- Triển khai lớp Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Triển khai lớp Khách hàng
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; } } }
Bây giờ hãy tạo bối cảnh UserContext trong một tệp riêng biệt:
Triển khai lớp 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; }
}
}
Chúng tôi đã nhận được giải pháp có sẵn để tiến hành kiểm tra tối ưu hóa với LINQ to SQL via EF cho MS SQL Server:
Bây giờ hãy nhập đoạn mã sau vào tệp Program.cs:
Tệp chương trình.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();
}
}
}
}
Tiếp theo, hãy khởi động dự án của chúng tôi.
Khi kết thúc công việc, thông tin sau sẽ được hiển thị trên bảng điều khiển:
Truy vấn SQL đã tạo
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])
Nghĩa là, nói chung, truy vấn LINQ đã tạo ra một truy vấn SQL tới MS SQL Server DBMS khá tốt.
Bây giờ hãy thay đổi điều kiện AND thành OR trong truy vấn LINQ:
truy vấn 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 };
Và hãy khởi chạy lại ứng dụng của chúng tôi.
Quá trình thực thi sẽ gặp sự cố và xảy ra lỗi do thời gian thực hiện lệnh vượt quá 30 giây:
Nếu bạn nhìn vào truy vấn được tạo bởi LINQ:
, thì bạn có thể đảm bảo rằng phép chọn xảy ra thông qua tích Descartes của hai tập hợp (bảng):
Truy vấn SQL đã tạo
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]
Hãy viết lại truy vấn LINQ như sau:
Truy vấn LINQ được tối ưu hóa
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 });
Sau đó, chúng tôi nhận được truy vấn SQL sau:
truy vấn 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]
Than ôi, trong các truy vấn LINQ chỉ có thể có một điều kiện nối, vì vậy ở đây có thể tạo một truy vấn tương đương bằng cách sử dụng hai truy vấn cho mỗi điều kiện và sau đó kết hợp chúng thông qua Union để loại bỏ trùng lặp giữa các hàng.
Có, các truy vấn thường sẽ không tương đương, có tính đến việc các hàng trùng lặp hoàn chỉnh có thể được trả về. Tuy nhiên, trong cuộc sống thực, những dòng trùng lặp hoàn toàn là không cần thiết và mọi người cố gắng loại bỏ chúng.
Bây giờ hãy so sánh kế hoạch thực hiện của hai truy vấn này:
- đối với CROSS JOIN thời gian thực hiện trung bình là 195 giây:
- đối với INNER JOIN-UNION thời gian thực hiện trung bình dưới 24 giây:
Như bạn có thể thấy từ kết quả, đối với hai bảng có hàng triệu bản ghi, truy vấn LINQ được tối ưu hóa nhanh hơn nhiều lần so với bảng không được tối ưu hóa.
Đối với tùy chọn có AND trong các điều kiện, truy vấn LINQ có dạng:
truy vấn 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 };
Truy vấn SQL chính xác hầu như sẽ luôn được tạo và chạy trung bình trong khoảng 1 giây:
Ngoài ra, đối với các thao tác LINQ to Object thay vì truy vấn như:
Truy vấn LINQ (tùy chọn thứ 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 };
bạn có thể sử dụng một truy vấn như:
Truy vấn LINQ (tùy chọn thứ 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 };
Trong đó:
Xác định hai mảng
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" } };
và kiểu Para được định nghĩa như sau:
Định nghĩa loại Para
class Para
{
public int Key1, Key2;
public string Data;
}
Vì vậy, chúng tôi đã xem xét một số khía cạnh trong việc tối ưu hóa các truy vấn LINQ cho MS SQL Server.
Thật không may, ngay cả các nhà phát triển .NET hàng đầu và có kinh nghiệm cũng quên rằng họ cần phải hiểu những hướng dẫn mà họ sử dụng thực hiện ở hậu trường. Nếu không, họ sẽ trở thành người cấu hình và có thể đặt một quả bom hẹn giờ trong tương lai cả khi mở rộng giải pháp phần mềm cũng như khi có những thay đổi nhỏ trong điều kiện môi trường bên ngoài.
Một đánh giá ngắn cũng được thực hiện .
Các nguồn cho thử nghiệm - chính dự án, việc tạo các bảng trong cơ sở dữ liệu TEST, cũng như điền dữ liệu vào các bảng này đều được đặt .
Cũng trong kho này, trong thư mục Plans, còn có các kế hoạch thực hiện truy vấn với điều kiện OR.
Nguồn: www.habr.com