LINQ ба .NET ҳамчун забони пурқуввати нави коркарди додаҳо ворид шуд. LINQ ба SQL ҳамчун як қисми он ба шумо имкон медиҳад, ки бо истифода аз барномаи Entity Framework хеле қулай бо DBMS муошират кунед. Бо вуҷуди ин, аз он аксар вақт истифода бурда, таҳиягарон фаромӯш мекунанд, ки чӣ гуна дархости SQL-ро провайдери дархостшаванда, дар ҳолати шумо Entity Framework тавлид мекунад.
Биёед ду нуктаи асосиро бо истифода аз мисол дида бароем.
Барои ин, дар 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-ро тавассути иҷро кардани скрипти зерин пур кунем:
Пур кардани ҷадвали 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; }
}
}
Мо ҳалли тайёрро барои гузаронидани санҷишҳои оптимизатсия бо LINQ ба SQL тавассути EF барои MS SQL Server гирифтем:
Акнун рамзи зеринро ба файли 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 дархости SQL-ро ба MS SQL Server DBMS хеле хуб тавлид кардааст.
Акнун биёед шарти AND-ро ба OR дар дархости 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
class Para
{
public int Key1, Key2;
public string Data;
}
Таким образом мы рассмотрели некоторые аспекты в оптимизации LINQ-запросов к MS SQL Server.
К сожалению даже опытные и ведущие .NET-разработчики забывают о том, что необходимо понимать что делают за кадром те инструкции, которые они используют. Иначе они становятся конфигураторами и могут заложить бомбу замедленного действия в будущем как при масштабировании программного решения, так и при незначительных изменениях внешних условий среды.
Также небольшой обзор проводился и
Исходники для теста-сам проект, создание таблиц в базе данных TEST, а также наполнение данными этих таблиц находится
Инчунин дар ин анбор, дар папкаи Нақшаҳо нақшаҳои иҷрои дархостҳо бо шартҳои OR мавҷуданд.
Манбаъ: will.com