LINQ .NET ga yangi kuchli ma'lumotlarni manipulyatsiya qilish tili sifatida kirdi. Uning bir qismi sifatida LINQ to SQL sizga, masalan, Entity Framework-dan foydalangan holda ma'lumotlar bazasi bilan juda qulay muloqot qilish imkonini beradi. Biroq, uni tez-tez ishlatib, ishlab chiquvchilar so'raladigan provayder, sizning holatingizda Entity Framework qanday SQL so'rovini yaratishini ko'rishni unutishadi.
Keling, misol yordamida ikkita asosiy fikrni ko'rib chiqaylik.
Buning uchun SQL Serverda Test ma'lumotlar bazasini yarating va unda quyidagi so'rov yordamida ikkita jadval yarating:
Jadvallar yaratish
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
Endi quyidagi skriptni ishga tushirish orqali Ref jadvalini to'ldiramiz:
Ref jadvalini to'ldirish
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
Quyidagi skript yordamida mijoz jadvalini xuddi shunday to'ldiramiz:
Mijozlar jadvalini to'ldirish
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
Shunday qilib, biz ikkita jadval oldik, ulardan birida 1 milliondan ortiq ma'lumotlar, ikkinchisida esa 10 milliondan ortiq ma'lumotlar mavjud.
Endi Visual Studio'da siz Visual C# Console App (.NET Framework) test loyihasini yaratishingiz kerak:
Keyinchalik, ma'lumotlar bazasi bilan ishlash uchun Entity Framework uchun kutubxona qo'shishingiz kerak.
Uni qo'shish uchun loyihani o'ng tugmasini bosing va kontekst menyusidan NuGet paketlarini boshqarish-ni tanlang:
Keyin paydo bo'lgan NuGet paketlarini boshqarish oynasida qidiruv oynasiga "Entity Framework" so'zini kiriting va Entity Framework paketini tanlang va uni o'rnating:
Keyinchalik, App.config faylida configSections elementini yopgandan so'ng, quyidagi blokni qo'shishingiz kerak:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ΠΠΠ―_ΠΠΠΠΠΠΠΠ―Π Π_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
ConnectionString-da siz ulanish qatorini kiritishingiz kerak.
Endi alohida fayllarda 3 ta interfeys yaratamiz:
- IBaseEntityID interfeysini amalga oshirish
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } }
- IBaseEntityName interfeysini amalga oshirish
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } }
- IBaseNameInsertUTCDate interfeysini amalga oshirish
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Va alohida faylda biz ikkita ob'ektimiz uchun BaseEntity asosiy sinfini yaratamiz, ular umumiy maydonlarni o'z ichiga oladi:
BaseEntity tayanch sinfini amalga oshirish
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Keyinchalik, ikkita ob'ektimizni alohida fayllarda yaratamiz:
- Ref sinfini amalga oshirish
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } }
- Buyurtmachi sinfini amalga oshirish
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; } } }
Endi alohida faylda UserContext kontekstini yaratamiz:
UserContex sinfini amalga oshirish
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; }
}
}
Biz MS SQL Server uchun EF orqali LINQ to SQL bilan optimallashtirish testlarini o'tkazish uchun tayyor yechim oldik:
Endi Program.cs fayliga quyidagi kodni kiriting:
Program.cs fayli
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();
}
}
}
}
Keyin loyihamizni ishga tushiramiz.
Ish oxirida konsolda quyidagilar ko'rsatiladi:
Yaratilgan SQL so'rovi
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])
Ya'ni, umuman olganda, LINQ so'rovi MS SQL Server DBMS uchun SQL so'rovini juda yaxshi yaratdi.
Endi LINQ soβrovida AND shartini OR ga oβzgartiramiz:
LINQ so'rovi
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 };
Va yana dasturimizni ishga tushiramiz.
Buyruqning bajarilish vaqti 30 sekunddan oshganligi sababli bajarilish xato bilan buziladi:
Agar siz LINQ tomonidan yaratilgan so'rovga qarasangiz:
, keyin tanlov ikkita to'plamning (jadvalning) Dekart mahsuloti orqali sodir bo'lishiga ishonch hosil qilishingiz mumkin:
Yaratilgan SQL so'rovi
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 so'rovini quyidagicha qayta yozamiz:
Optimallashtirilgan LINQ so'rovi
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 });
Keyin biz quyidagi SQL so'rovini olamiz:
SQL so'rovi
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]
Afsuski, LINQ so'rovlarida faqat bitta qo'shilish sharti bo'lishi mumkin, shuning uchun bu erda har bir shart uchun ikkita so'rovdan foydalangan holda ekvivalent so'rovni amalga oshirish va keyin ularni Union orqali birlashtirib, qatorlar orasidagi dublikatlarni olib tashlash mumkin.
Ha, to'liq takroriy satrlar qaytarilishi mumkinligini hisobga olgan holda so'rovlar odatda ekvivalent bo'lmaydi. Biroq, haqiqiy hayotda to'liq takroriy chiziqlar kerak emas va odamlar ulardan xalos bo'lishga harakat qilishadi.
Keling, ushbu ikki so'rovni bajarish rejalarini taqqoslaylik:
- CROSS JOIN uchun o'rtacha bajarilish vaqti 195 soniya:
- INNER JOIN-UNION uchun o'rtacha bajarish vaqti 24 soniyadan kam:
Natijalardan ko'rinib turibdiki, millionlab yozuvlari bo'lgan ikkita jadval uchun optimallashtirilgan LINQ so'rovi optimallashtirilmaganidan bir necha baravar tezroq.
Shartlarda AND bilan variant uchun, shaklning LINQ so'rovi:
LINQ so'rovi
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 };
To'g'ri SQL so'rovi deyarli har doim yaratiladi, u o'rtacha 1 soniyada ishlaydi:
Shuningdek, quyidagi so'rov o'rniga LINQ to Objects manipulyatsiyasi uchun:
LINQ so'rovi (1-variant)
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 };
kabi so'rovdan foydalanishingiz mumkin:
LINQ so'rovi (2-variant)
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 };
qaerda:
Ikki massivni aniqlash
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" } };
, va Para turi quyidagicha aniqlanadi:
Para turi ta'rifi
class Para
{
public int Key1, Key2;
public string Data;
}
Shunday qilib, biz MS SQL Serverga LINQ so'rovlarini optimallashtirishning ba'zi jihatlarini ko'rib chiqdik.
Afsuski, hatto tajribali va yetakchi .NET dasturchilari ham oβzlari foydalanadigan koβrsatmalar sahna ortida nima qilishini tushunishlari kerakligini unutib qoβyishadi. Aks holda, ular konfiguratorga aylanadi va kelajakda dasturiy ta'minot echimini kengaytirganda ham, tashqi muhit sharoitidagi kichik o'zgarishlar bilan ham vaqtli bomba qo'yishi mumkin.
Shuningdek, qisqacha ko'rib chiqish o'tkazildi
Sinov uchun manbalar - loyihaning o'zi, TEST ma'lumotlar bazasida jadvallarni yaratish, shuningdek, ushbu jadvallarni ma'lumotlar bilan to'ldirish.
Shuningdek, ushbu omborda, Rejalar papkasida OR shartlari bilan so'rovlarni bajarish rejalari mavjud.
Manba: www.habr.com