🥇MS SQL Server uchun C#.NET da LINQ so'rovlarini optimallashtirishning ba'zi jihatlari

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 shu yerda.

Sinov uchun manbalar - loyihaning o'zi, TEST ma'lumotlar bazasida jadvallarni yaratish, shuningdek, ushbu jadvallarni ma'lumotlar bilan to'ldirish. shu yerda.
Shuningdek, ushbu omborda, Rejalar papkasida OR shartlari bilan so'rovlarni bajarish rejalari mavjud.

Manba: www.habr.com

MS SQL Server uchun C#.NET da LINQ so'rovlarini optimallashtirishning ba'zi jihatlari

a Izoh qo'shish Bu savolga javob bekor