LINQ .NET-ə yeni güclü verilənlərin manipulyasiya dili kimi daxil oldu. Onun bir hissəsi kimi LINQ to SQL, məsələn, Entity Framework-dən istifadə edərək DBMS ilə kifayət qədər rahat əlaqə saxlamağa imkan verir. Bununla belə, ondan tez-tez istifadə edən tərtibatçılar sorğulana bilən provayderin hansı SQL sorğusunu, sizin vəziyyətinizdə Entity Framework yaradacağına baxmağı unudurlar.
Bir nümunə ilə iki əsas məqama nəzər salaq.
Bunun üçün SQL Serverdə verilənlər bazası Testi yaradacağıq və orada aşağıdakı sorğudan istifadə edərək iki cədvəl yaradacağıq:
Cədvəllərin yaradılması
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
İndi aşağıdakı skripti işlətməklə Ref cədvəlini dolduraq:
Cədvəlin doldurulması 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
Aşağıdakı skriptdən istifadə edərək Müştəri cədvəlini eyni şəkildə dolduraq:
Müştəri cədvəlinin doldurulması
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
Beləliklə, iki cədvəl əldə etdik, onlardan birində 1 milyondan çox, digərində isə 10 milyondan çox məlumat var.
İndi Visual Studio-da siz test Visual C# Konsol Tətbiqi (.NET Framework) layihəsi yaratmalısınız:
Sonra, verilənlər bazası ilə qarşılıqlı əlaqə yaratmaq üçün Entity Framework üçün kitabxana əlavə etməlisiniz.
Onu əlavə etmək üçün layihənin üzərinə sağ klikləyin və kontekst menyusundan Manage NuGet Paketlərini seçin:
Sonra, görünən NuGet paket idarəetmə pəncərəsində axtarış qutusuna "Entity Framework" sözünü daxil edin və Entity Framework paketini seçin və quraşdırın:
Sonra, App.config faylında configSections elementini bağladıqdan sonra aşağıdakı bloku əlavə edin:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
ConnectionString-də siz əlaqə sətirini daxil etməlisiniz.
İndi ayrı-ayrı fayllarda 3 interfeys yaradaq:
- IBaseEntityID interfeysinin həyata keçirilməsi
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - IBaseEntityName interfeysinin həyata keçirilməsi
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - IBaseNameInsertUTCDate interfeysinin həyata keçirilməsi
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Ayrı bir faylda biz iki obyektimiz üçün ümumi sahələri ehtiva edən BaseEntity baza sinfi yaradacağıq:
BaseEntity baza sinfinin həyata keçirilməsi
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Sonra, ayrı-ayrı fayllarda iki obyektimizi yaradacağıq:
- Ref sinfinin həyata keçirilməsi
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Müştəri sinfinin həyata keçirilməsi
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; } } }
İndi ayrı faylda UserContext kontekstini yaradaq:
UserContex sinfinin həyata keçirilməsi
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 üçün EF vasitəsilə LINQ to SQL ilə optimallaşdırma testlərinin aparılması üçün hazır həll variantımız var:
İndi Program.cs faylında aşağıdakı kodu daxil edin:
Program.cs faylı
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();
}
}
}
}
Sonra, layihəmizi icra edək.
İşin sonunda konsolda aşağıdakılar görünəcək:
Yaradılmış SQL sorğusu
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])
Yəni, ümumiyyətlə, LINQ sorğusu MS SQL Server DBMS-ə kifayət qədər yaxşı SQL sorğusu yaradıb.
İndi LINQ sorğusunda AND şərtini OR-a dəyişək:
LINQ sorğusu
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 };
Gəlin tətbiqimizi yenidən işə salaq.
İcra 30 saniyədən çox əmrin icra müddəti ilə bağlı xəta ilə çökəcək:
LINQ tərəfindən hansı sorğunun yaradıldığına baxsanız:
, onda seçimin iki dəstin (cədvəlin) Kartezyen hasilatı vasitəsilə baş verdiyinə əmin ola bilərsiniz:
Yaradılmış SQL sorğusu
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 sorğusunu bu şəkildə yenidən yazaq:
Optimallaşdırılmış LINQ sorğusu
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 });
Sonra aşağıdakı SQL sorğusunu alırıq:
SQL sorğusu
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]
Təəssüf ki, LINQ sorğularında yalnız bir qoşulma şərti ola bilər, ona görə də hər bir şərt üçün iki sorğu vasitəsilə ekvivalent sorğu etmək, ardınca sətirlər arasında dublikatları silmək üçün birlik vasitəsilə onların birləşməsi mümkündür.
Bəli, tam dublikat sətirlərin qaytarıla biləcəyini nəzərə alsaq, sorğular ümumiyyətlə qeyri-ekvivalent olacaq. Halbuki real həyatda tam dublikat sətirlərə ehtiyac yoxdur və onlardan qurtulmağa çalışırlar.
İndi bu iki sorğunun icra planlarını müqayisə edək:
- CROSS JOIN üçün orta icra müddəti 195 saniyədir:
- INNER JOIN-UNION üçün orta icra müddəti 24 saniyədən azdır:
Nəticələrdən göründüyü kimi, milyonlarla qeydi olan iki cədvəl üçün optimallaşdırılmış LINQ sorğusu optimallaşdırılmamışdan dəfələrlə sürətlidir.
Formanın LINQ sorğusu şəraitində AND olan variant üçün:
LINQ sorğusu
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 };
demək olar ki, həmişə düzgün SQL sorğusu yaradılacaq ki, bu da orta hesabla təxminən 1 saniyə davam edəcək:
Görünüşü sorğulamaq əvəzinə LINQ to Objects manipulyasiyaları üçün:
LINQ sorğusu (1-ci seçim)
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 };
Belə bir sorğudan istifadə edə bilərsiniz:
LINQ sorğusu (2-ci seçim)
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 };
Ü:
İki massivin müəyyən edilməsi
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ə Para növü aşağıdakı kimi müəyyən edilir:
Para növü tərifi
class Para
{
public int Key1, Key2;
public string Data;
}
Beləliklə, biz LINQ sorğularının MS SQL Serverə optimallaşdırılmasında bəzi aspektləri nəzərdən keçirdik.
Təəssüf ki, hətta təcrübəli və aparıcı .NET tərtibatçıları da unudurlar ki, istifadə etdikləri təlimatların pərdə arxasında nə etdiyini başa düşmək lazımdır. Əks təqdirdə, onlar konfiquratora çevrilirlər və gələcəkdə həm proqram həllini miqyaslandırarkən, həm də xarici mühit şəraitində kiçik dəyişikliklərlə saat bombası qoya bilərlər.
Kiçik bir baxış da var idi .
Test üçün mənbələr - layihənin özü, TEST verilənlər bazasında cədvəllər yaratmaq, həmçinin bu cədvəlləri məlumatlarla doldurmaq .
Həmçinin Planlar qovluğundakı bu depoda OR şərtləri ilə sorğuların icrası üçün planlar var.
Mənbə: www.habr.com