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:
.
Shuningdek, ushbu omborda, Rejalar papkasida OR shartlari bilan so'rovlarni bajarish rejalari mavjud.
Manba: www.habr.com