LINQ memasukkan .NET sebagai bahasa manipulasi data baharu yang berkuasa. LINQ kepada SQL sebagai sebahagian daripadanya membolehkan anda berkomunikasi dengan agak mudah dengan DBMS menggunakan, sebagai contoh, Rangka Kerja Entiti. Walau bagaimanapun, menggunakannya agak kerap, pembangun terlupa untuk melihat jenis pertanyaan SQL yang akan dijana oleh pembekal boleh pertanyaan, dalam kes anda Rangka Kerja Entiti.
Mari kita lihat dua perkara utama menggunakan contoh.
Untuk melakukan ini, buat pangkalan data Ujian dalam SQL Server dan buat dua jadual di dalamnya menggunakan pertanyaan berikut:
Mencipta jadual
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
Sekarang mari isi jadual Ref dengan menjalankan skrip berikut:
Mengisi meja Rujukan
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
Mari kita sama-sama mengisi jadual Pelanggan menggunakan skrip berikut:
Mengisi jadual Pelanggan
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
Oleh itu, kami menerima dua jadual, satu daripadanya mempunyai lebih daripada 1 juta baris data, dan satu lagi mempunyai lebih daripada 10 juta baris data.
Sekarang dalam Visual Studio anda perlu mencipta projek Visual C# Console App (.NET Framework) ujian:
Seterusnya, anda perlu menambah perpustakaan untuk Rangka Kerja Entiti untuk berinteraksi dengan pangkalan data.
Untuk menambahkannya, klik kanan pada projek dan pilih Urus Pakej NuGet daripada menu konteks:
Kemudian, dalam tetingkap pengurusan pakej NuGet yang muncul, masukkan perkataan "Rangka Kerja Entiti" dalam tetingkap carian dan pilih pakej Rangka Kerja Entiti dan pasangnya:
Seterusnya, dalam fail App.config, selepas menutup elemen configSections, anda perlu menambah blok berikut:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ΠΠΠ―_ΠΠΠΠΠΠΠΠ―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Dalam connectionString anda perlu memasukkan rentetan sambungan.
Sekarang mari kita buat 3 antara muka dalam fail berasingan:
- Melaksanakan antara muka IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Pelaksanaan antara muka IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Pelaksanaan antara muka IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Dan dalam fail berasingan kami akan mencipta BaseEntity kelas asas untuk dua entiti kami, yang akan merangkumi medan biasa:
Pelaksanaan kelas asas BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Seterusnya, kami akan mencipta dua entiti kami dalam fail berasingan:
- Pelaksanaan kelas Ruj
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Pelaksanaan kelas Pelanggan
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; } } }
Sekarang mari kita buat konteks UserContext dalam fail berasingan:
Pelaksanaan kelas 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; }
}
}
Kami menerima penyelesaian sedia untuk menjalankan ujian pengoptimuman dengan LINQ ke SQL melalui EF untuk MS SQL Server:
Sekarang masukkan kod berikut ke dalam fail Program.cs:
Fail 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();
}
}
}
}
Seterusnya, mari kita lancarkan projek kami.
Pada akhir kerja, perkara berikut akan dipaparkan pada konsol:
SQL Query yang dihasilkan
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])
Iaitu, secara umum, pertanyaan LINQ menghasilkan pertanyaan SQL kepada MS SQL Server DBMS dengan baik.
Sekarang mari kita tukar syarat AND kepada OR dalam pertanyaan LINQ:
pertanyaan 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 };
Dan mari melancarkan aplikasi kami sekali lagi.
Pelaksanaan akan ranap dengan ralat kerana masa pelaksanaan arahan melebihi 30 saat:
Jika anda melihat pertanyaan yang dihasilkan oleh LINQ:
, maka anda boleh memastikan bahawa pemilihan berlaku melalui produk Cartesian dua set (jadual):
SQL Query yang dihasilkan
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]
Mari kita tulis semula pertanyaan LINQ seperti berikut:
Pertanyaan LINQ yang dioptimumkan
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 });
Kemudian kami mendapat pertanyaan SQL berikut:
Pertanyaan 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]
Malangnya, dalam pertanyaan LINQ hanya terdapat satu syarat gabungan, jadi di sini adalah mungkin untuk membuat pertanyaan setara menggunakan dua pertanyaan untuk setiap syarat dan kemudian menggabungkannya melalui Union untuk mengalih keluar pendua antara baris.
Ya, pertanyaan biasanya tidak setara, dengan mengambil kira baris pendua yang lengkap mungkin dikembalikan. Walau bagaimanapun, dalam kehidupan sebenar, baris pendua lengkap tidak diperlukan dan orang ramai cuba menyingkirkannya.
Sekarang mari kita bandingkan rancangan pelaksanaan dua pertanyaan ini:
- untuk CROSS JOIN purata masa pelaksanaan ialah 195 saat:
- untuk INNER JOIN-UNION purata masa pelaksanaan adalah kurang daripada 24 saat:
Seperti yang anda lihat daripada keputusan, untuk dua jadual dengan berjuta-juta rekod, pertanyaan LINQ yang dioptimumkan adalah berkali-kali lebih pantas daripada yang tidak dioptimumkan.
Untuk pilihan dengan DAN dalam syarat, pertanyaan LINQ borang:
pertanyaan 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 };
Pertanyaan SQL yang betul akan hampir selalu dihasilkan, yang akan berjalan secara purata dalam kira-kira 1 saat:
Juga untuk manipulasi LINQ kepada Objek dan bukannya pertanyaan seperti:
Pertanyaan LINQ (pilihan pertama)
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 };
anda boleh menggunakan pertanyaan seperti:
Pertanyaan LINQ (pilihan pertama)
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 };
di mana:
Mentakrifkan dua tatasusunan
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" } };
, dan jenis Para ditakrifkan seperti berikut:
Definisi Jenis Para
class Para
{
public int Key1, Key2;
public string Data;
}
Oleh itu, kami mengkaji beberapa aspek dalam mengoptimumkan pertanyaan LINQ kepada MS SQL Server.
Malangnya, walaupun pembangun .NET yang berpengalaman dan terkemuka lupa bahawa mereka perlu memahami arahan yang mereka gunakan di sebalik tabir. Jika tidak, mereka menjadi konfigurator dan boleh menanam bom jangka pada masa hadapan apabila menskalakan penyelesaian perisian dan dengan perubahan kecil dalam keadaan persekitaran luaran.
Tinjauan ringkas juga telah dijalankan .
Sumber untuk ujian - projek itu sendiri, penciptaan jadual dalam pangkalan data TEST, serta mengisi jadual ini dengan data terletak .
Juga dalam repositori ini, dalam folder Plans, terdapat rancangan untuk melaksanakan pertanyaan dengan syarat ATAU.
Sumber: www.habr.com