LINQ .NET жүйесін қуатты жаңа деректерді өңдеу тілі ретінде енгізді. LINQ to SQL оның бөлігі ретінде, мысалы, Entity Framework көмегімен ДҚБЖ-мен өте ыңғайлы байланысуға мүмкіндік береді. Дегенмен, оны жиі пайдалана отырып, әзірлеушілер сұранысқа ие провайдер, сіздің жағдайда Entity Framework, қандай SQL сұрауын жасайтынын қарауды ұмытып кетеді.
Мысал арқылы екі негізгі ойды қарастырайық.
Ол үшін SQL Server серверінде сынақ деректер базасын жасаңыз және келесі сұрауды пайдаланып онда екі кесте жасаңыз:
Кестелерді жасау
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
Енді келесі сценарийді іске қосу арқылы 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
Келесі сценарий арқылы Тұтынушы кестесін дәл осылай толтырайық:
Тұтынушы кестесін толтыру
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
Осылайша, біз екі кесте алдық, олардың бірінде 1 миллионнан астам деректер жолы бар, ал екіншісінде 10 миллионнан астам деректер бар.
Енді Visual Studio бағдарламасында сынақ Visual C# Console App (.NET Framework) жобасын жасау керек:
Содан кейін дерекқормен әрекеттесу үшін Entity Framework үшін кітапхана қосу керек.
Оны қосу үшін жобаны тінтуірдің оң жақ түймешігімен басып, контекстік мәзірден NuGet пакеттерін басқару пәрменін таңдаңыз:
Содан кейін, пайда болатын NuGet пакетін басқару терезесінде іздеу терезесіне «Entity Framework» сөзін енгізіңіз және Entity Framework бумасын таңдап, оны орнатыңыз:
Әрі қарай, App.config файлында configSections элементін жапқаннан кейін келесі блокты қосу керек:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
ConnectionString ішінде қосылым жолын енгізу керек.
Енді бөлек файлдарда 3 интерфейсті жасайық:
- IBaseEntityID интерфейсін енгізу
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } }
- IBaseEntityName интерфейсін енгізу
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } }
- IBaseNameInsertUTCDate интерфейсін енгізу
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Ал бөлек файлда біз екі нысанымыз үшін жалпы өрістерді қамтитын BaseEntity базалық класын жасаймыз:
BaseEntity базалық класын іске асыру
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Содан кейін біз екі нысанды бөлек файлдарда жасаймыз:
- Ref сыныбын жүзеге асыру
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } }
- Тұтынушы класын енгізу
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; } } }
Енді бөлек файлда UserContext контекстін жасайық:
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; }
}
}
Біз MS SQL серверіне арналған EF арқылы LINQ to SQL арқылы оңтайландыру сынақтарын жүргізуге арналған дайын шешім алдық:
Енді Program.cs файлына келесі кодты енгізіңіз:
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();
}
}
}
}
Келесі кезекте жобамызды бастаймыз.
Жұмыстың соңында консольде келесілер көрсетіледі:
Жасалған SQL сұрауы
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])
Яғни, жалпы алғанда, LINQ сұрауы MS SQL Server ДҚБЖ үшін SQL сұрауын өте жақсы жасады.
Енді LINQ сұрауындағы ЖӘНЕ шартын НЕМЕСЕ етіп өзгертейік:
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 };
Ал енді қосымшамызды қайтадан іске қосайық.
Пәрменді орындау уақыты 30 секундтан асатындықтан, орындалу қатемен бұзылады:
LINQ арқылы жасалған сұрауды қарасаңыз:
, содан кейін таңдау екі жиынның (кестенің) декарттық көбейтіндісі арқылы жүзеге асатынына көз жеткізуге болады:
Жасалған SQL сұрауы
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 сұрауын келесідей қайта жазайық:
Оңтайландырылған LINQ сұрауы
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 });
Содан кейін келесі SQL сұрауын аламыз:
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]
Өкінішке орай, LINQ сұрауларында тек бір қосылу шарты болуы мүмкін, сондықтан мұнда әрбір шарт үшін екі сұрауды пайдаланып, жолдар арасындағы көшірмелерді жою үшін оларды одақ арқылы біріктіру арқылы баламалы сұрау жасауға болады.
Иә, толық қайталанатын жолдар қайтарылуы мүмкін екенін ескере отырып, сұраулар әдетте баламалы емес болады. Дегенмен, нақты өмірде толық қайталанатын жолдар қажет емес және адамдар олардан құтылуға тырысады.
Енді осы екі сұраудың орындалу жоспарларын салыстырайық:
- CROSS JOIN үшін орташа орындау уақыты 195 секунд:
- INNER JOIN-UNION үшін орташа орындау уақыты 24 секундтан аз:
Нәтижелерден көріп отырғаныңыздай, миллиондаған жазбалары бар екі кесте үшін оңтайландырылған LINQ сұрауы оңтайландырылмағанға қарағанда бірнеше есе жылдамырақ.
Шарттардағы ЖӘНЕ параметрі үшін пішіннің LINQ сұрауы:
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 };
Дұрыс SQL сұрауы әрқашан дерлік жасалады, ол орташа есеппен шамамен 1 секундта орындалады:
Сондай-ақ келесідей сұраудың орнына LINQ to Objects манипуляциялары үшін:
LINQ сұрауы (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 };
сияқты сұрауды пайдалана аласыз:
LINQ сұрауы (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 };
мұнда:
Екі массивті анықтау
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" } };
, және Para түрі келесідей анықталады:
Para Type анықтамасы
class Para
{
public int Key1, Key2;
public string Data;
}
Осылайша, MS SQL серверіне LINQ сұрауларын оңтайландырудың кейбір аспектілерін қарастырдық.
Өкінішке орай, тіпті тәжірибелі және жетекші .NET әзірлеушілері қолданатын нұсқаулардың сахна артында не істейтінін түсіну керек екенін ұмытып кетеді. Әйтпесе, олар конфигураторға айналады және болашақта бағдарламалық шешімді масштабтау кезінде де, сыртқы орта жағдайларындағы шамалы өзгерістермен де уақыт бомбасын қоя алады.
Сондай-ақ қысқаша шолу жасалды
Сынақ көздері – жобаның өзі, TEST мәліметтер базасында кестелерді құру, сонымен қатар осы кестелерді деректермен толтыру орналасқан.
Сондай-ақ осы репозиторийде Жоспарлар қалтасында НЕМЕСЕ шарттары бар сұрауларды орындау жоспарлары бар.
Ақпарат көзі: www.habr.com