LINQ, güçlü ve yeni bir veri işleme dili olarak .NET'e girdi. LINQ to SQL, bunun bir parçası olarak, örneğin Entity Framework kullanarak bir DBMS ile oldukça rahat bir şekilde iletişim kurmanıza olanak tanır. Ancak bunu oldukça sık kullanan geliştiriciler, sizin durumunuzda Entity Framework olan sorgulanabilir sağlayıcının ne tür bir SQL sorgusu oluşturacağına bakmayı unuturlar.
Bir örnek kullanarak iki ana noktaya bakalım.
Bunu yapmak için SQL Server'da bir Test veritabanı oluşturun ve aşağıdaki sorguyu kullanarak bu veritabanında iki tablo oluşturun:
Tablo oluşturma
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
Şimdi aşağıdaki betiği çalıştırarak Ref tablosunu dolduralım:
Referans tablosunun doldurulması
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
Benzer şekilde Müşteri tablosunu aşağıdaki komut dosyasını kullanarak dolduralım:
Müşteri tablosunu doldurma
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
Böylece elimizde biri 1 milyon satırdan fazla veri içeren, diğeri ise 10 milyon satırdan fazla veri içeren iki tablo elde ettik.
Artık Visual Studio'da bir test Visual C# Konsol Uygulaması (.NET Framework) projesi oluşturmanız gerekir:
Daha sonra Entity Framework'ün veritabanıyla etkileşime girmesi için bir kitaplık eklemeniz gerekir.
Eklemek için projeye sağ tıklayın ve içerik menüsünden NuGet Paketlerini Yönet'i seçin:
Ardından açılan NuGet paket yönetimi penceresinde, arama penceresine “Entity Framework” kelimesini girin ve Entity Framework paketini seçin ve yükleyin:
Daha sonra, App.config dosyasında configSections öğesini kapattıktan sonra aşağıdaki bloğu eklemeniz gerekir:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
ConnectionString'de bağlantı dizesini girmeniz gerekir.
Şimdi ayrı dosyalarda 3 arayüz oluşturalım:
- IBaseEntityID arayüzünü uygulama
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - IBaseEntityName arayüzünün uygulanması
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - IBaseNameInsertUTCDate arayüzünün uygulanması
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Ayrı bir dosyada, iki varlığımız için ortak alanları içerecek bir temel sınıf BaseEntity oluşturacağız:
BaseEntity temel sınıfının uygulanması
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Daha sonra iki varlığımızı ayrı dosyalarda oluşturacağız:
- Ref sınıfının uygulanması
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Müşteri sınıfının uygulanması
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; } } }
Şimdi ayrı bir dosyada bir UserContext bağlamı oluşturalım:
UserContex sınıfının uygulanması
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; }
}
}
MS SQL Server için EF aracılığıyla LINQ to SQL ile optimizasyon testleri yürütmek için hazır bir çözüm aldık:
Şimdi Program.cs dosyasına aşağıdaki kodu girin:
Program.cs dosyası
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();
}
}
}
}
Daha sonra projemizi başlatalım.
Çalışmanın sonunda konsolda aşağıdakiler görüntülenecektir:
Oluşturulan SQL Sorgusu
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])
Yani, genel olarak LINQ sorgusu, MS SQL Server DBMS'ye oldukça iyi bir SQL sorgusu oluşturdu.
Şimdi LINQ sorgusunda AND koşulunu OR olarak değiştirelim:
LINQ sorgusu
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 };
Ve uygulamamızı tekrar başlatalım.
Komut yürütme süresinin 30 saniyeyi aşması nedeniyle yürütme bir hatayla çökecek:
LINQ tarafından oluşturulan sorguya bakarsanız:
, o zaman seçimin iki kümenin (tabloların) Kartezyen çarpımı aracılığıyla gerçekleştiğinden emin olabilirsiniz:
Oluşturulan SQL Sorgusu
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 sorgusunu şu şekilde yeniden yazalım:
Optimize edilmiş LINQ sorgusu
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 });
Daha sonra aşağıdaki SQL sorgusunu alıyoruz:
SQL sorgusu
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]
Ne yazık ki, LINQ sorgularında yalnızca bir birleştirme koşulu olabilir, dolayısıyla burada her koşul için iki sorgu kullanarak eşdeğer bir sorgu yapmak ve ardından satırlar arasındaki kopyaları kaldırmak için bunları Union aracılığıyla birleştirmek mümkündür.
Evet, yinelenen satırların tamamının döndürülebileceği dikkate alındığında sorgular genellikle eşdeğer olmayacaktır. Ancak gerçek hayatta birbirinin aynısı satırlara ihtiyaç duyulmaz ve insanlar bunlardan kurtulmaya çalışır.
Şimdi bu iki sorgunun yürütme planlarını karşılaştıralım:
- CROSS JOIN için ortalama yürütme süresi 195 saniyedir:
- INNER JOIN-UNION için ortalama yürütme süresi 24 saniyeden azdır:
Sonuçlardan da görebileceğiniz gibi, milyonlarca kayıt içeren iki tablo için optimize edilmiş LINQ sorgusu, optimize edilmemiş sorgudan kat kat daha hızlıdır.
Koşullarda AND bulunan seçenek için formun bir LINQ sorgusu:
LINQ sorgusu
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 };
Neredeyse her zaman doğru SQL sorgusu oluşturulacak ve bu sorgu ortalama olarak yaklaşık 1 saniye içinde çalışacaktır:
Ayrıca aşağıdaki gibi bir sorgu yerine LINQ to Objects manipülasyonları için:
LINQ sorgusu (1. seçenek)
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 };
şöyle bir sorgu kullanabilirsiniz:
LINQ sorgusu (2. seçenek)
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 };
burada:
İki dizi tanımlama
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" } };
ve Para türü şu şekilde tanımlanır:
Para Türü Tanımı
class Para
{
public int Key1, Key2;
public string Data;
}
Bu nedenle, LINQ sorgularını MS SQL Server'a optimize etmenin bazı yönlerini inceledik.
Ne yazık ki, deneyimli ve önde gelen .NET geliştiricileri bile, kullandıkları talimatların perde arkasında ne yaptığını anlamaları gerektiğini unutuyorlar. Aksi takdirde, hem yazılım çözümünü ölçeklendirirken hem de dış çevre koşullarındaki küçük değişikliklerle yapılandırıcı haline gelirler ve gelecekte saatli bomba yerleştirebilirler.
Kısa bir inceleme de yapıldı .
Testin kaynakları - projenin kendisi, TEST veritabanında tabloların oluşturulması ve bu tabloların verilerle doldurulması yer almaktadır. .
Ayrıca bu depodaki Planlar klasöründe, sorguların OR koşullarıyla yürütülmesine yönelik planlar vardır.
Kaynak: habr.com