LINQ memasukkan .NET sebagai bahasa manipulasi data baru yang kuat. LINQ ke SQL sebagai bagiannya memungkinkan Anda berkomunikasi dengan DBMS dengan nyaman menggunakan, misalnya, Entity Framework. Namun, cukup sering menggunakannya, pengembang lupa melihat jenis kueri SQL apa yang akan dihasilkan oleh penyedia kueri, dalam kasus Anda Entity Framework.
Mari kita lihat dua poin utama dengan menggunakan sebuah contoh.
Untuk melakukannya, buat database Uji di SQL Server, dan buat dua tabel di dalamnya menggunakan kueri berikut:
Membuat tabel
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 kita isi tabel Ref dengan menjalankan skrip berikut:
Mengisi tabel 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
Mari kita isi tabel Pelanggan dengan cara yang sama menggunakan skrip berikut:
Mengisi tabel 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
Jadi, kami menerima dua tabel, salah satunya memiliki lebih dari 1 juta baris data, dan yang lainnya memiliki lebih dari 10 juta baris data.
Sekarang di Visual Studio Anda perlu membuat proyek pengujian Aplikasi Konsol Visual C# (.NET Framework):
Selanjutnya, Anda perlu menambahkan perpustakaan untuk Entity Framework untuk berinteraksi dengan database.
Untuk menambahkannya, klik kanan pada proyek dan pilih Kelola Paket NuGet dari menu konteks:
Kemudian, pada jendela manajemen paket NuGet yang muncul, masukkan kata βEntity Frameworkβ di jendela pencarian dan pilih paket Entity Framework dan instal:
Selanjutnya, di file App.config, setelah menutup elemen configSections, Anda perlu menambahkan blok berikut:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ΠΠΠ―_ΠΠΠΠΠΠΠΠ―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Di connectionString Anda harus memasukkan string koneksi.
Sekarang mari kita buat 3 antarmuka dalam file terpisah:
- Menerapkan antarmuka IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementasi antarmuka IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementasi antarmuka IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Dan dalam file terpisah kita akan membuat kelas dasar BaseEntity untuk dua entitas kita, yang akan mencakup bidang umum:
Implementasi kelas dasar BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Selanjutnya, kita akan membuat dua entitas dalam file terpisah:
- Implementasi kelas Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementasi 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 buat konteks UserContext di file terpisah:
Implementasi 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 solusi siap pakai untuk melakukan tes optimasi dengan LINQ ke SQL melalui EF untuk MS SQL Server:
Sekarang masukkan kode berikut ke dalam file Program.cs:
File 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();
}
}
}
}
Selanjutnya, mari kita luncurkan proyek kita.
Di akhir pekerjaan, yang berikut ini akan ditampilkan di konsol:
Kueri SQL 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])
Artinya, secara umum, kueri LINQ menghasilkan kueri SQL ke DBMS MS SQL Server dengan cukup baik.
Sekarang mari kita ubah kondisi AND menjadi OR pada query LINQ:
permintaan 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 luncurkan aplikasi kita lagi.
Eksekusi akan terhenti karena kesalahan karena waktu eksekusi perintah melebihi 30 detik:
Jika Anda melihat kueri yang dihasilkan oleh LINQ:
, maka Anda dapat memastikan bahwa pemilihan terjadi melalui perkalian Cartesian dari dua himpunan (tabel):
Kueri SQL 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 ulang query LINQ sebagai berikut:
Kueri LINQ yang dioptimalkan
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 kita mendapatkan query SQL berikut:
kueri 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]
Sayangnya, dalam kueri LINQ hanya ada satu kondisi gabungan, jadi di sini dimungkinkan untuk membuat kueri yang setara menggunakan dua kueri untuk setiap kondisi dan kemudian menggabungkannya melalui Union untuk menghapus duplikat di antara baris.
Ya, kueri umumnya tidak setara, dengan mempertimbangkan bahwa baris duplikat lengkap dapat dikembalikan. Namun, dalam kehidupan nyata, garis duplikat yang lengkap tidak diperlukan dan orang-orang mencoba untuk menghilangkannya.
Sekarang mari kita bandingkan rencana eksekusi kedua kueri ini:
- untuk CROSS JOIN waktu eksekusi rata-rata adalah 195 detik:
- untuk INNER JOIN-UNION waktu eksekusi rata-rata kurang dari 24 detik:
Seperti yang dapat Anda lihat dari hasilnya, untuk dua tabel dengan jutaan catatan, kueri LINQ yang dioptimalkan berkali-kali lebih cepat daripada kueri yang tidak dioptimalkan.
Untuk opsi dengan AND dalam kondisi, kueri LINQ berbentuk:
permintaan 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 };
Kueri SQL yang benar hampir selalu dihasilkan, yang akan berjalan rata-rata dalam waktu sekitar 1 detik:
Juga untuk manipulasi LINQ ke Objek alih-alih kueri seperti:
Kueri LINQ (opsi 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 dapat menggunakan kueri seperti:
Kueri LINQ (opsi 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:
Mendefinisikan dua array
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 tipe Para didefinisikan sebagai berikut:
Definisi Tipe Para
class Para
{
public int Key1, Key2;
public string Data;
}
Jadi, kami memeriksa beberapa aspek dalam mengoptimalkan kueri LINQ ke MS SQL Server.
Sayangnya, bahkan pengembang .NET yang berpengalaman dan terkemuka pun lupa bahwa mereka perlu memahami instruksi yang mereka gunakan di balik layar. Jika tidak, mereka akan menjadi konfigurator dan dapat memasang bom waktu di masa depan baik saat menskalakan solusi perangkat lunak maupun dengan perubahan kecil pada kondisi lingkungan eksternal.
Tinjauan singkat juga dilakukan .
Sumber pengujian - proyek itu sendiri, pembuatan tabel di database TEST, serta pengisian tabel ini dengan data berada .
Juga di repositori ini, di folder Plans, terdapat rencana untuk mengeksekusi query dengan kondisi OR.
Sumber: www.habr.com