🥇MS SQL Server üçün C#.NET-də LINQ sorğularının optimallaşdırılmasının bəzi aspektləri

LINQ .NET-ə yeni güclü verilənlərin manipulyasiya dili kimi daxil oldu. Onun bir hissəsi kimi LINQ to SQL, məsələn, Entity Framework-dən istifadə edərək DBMS ilə kifayət qədər rahat əlaqə saxlamağa imkan verir. Bununla belə, ondan tez-tez istifadə edən tərtibatçılar sorğulana bilən provayderin hansı SQL sorğusunu, sizin vəziyyətinizdə Entity Framework yaradacağına baxmağı unudurlar.

Bir nümunə ilə iki əsas məqama nəzər salaq.
Bunun üçün SQL Serverdə verilənlər bazası Testi yaradacağıq və orada aşağıdakı sorğudan istifadə edərək iki cədvəl yaradacağıq:

Cədvəllərin yaradılması

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

İndi aşağıdakı skripti işlətməklə Ref cədvəlini dolduraq:

Cədvəlin doldurulması 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

Aşağıdakı skriptdən istifadə edərək Müştəri cədvəlini eyni şəkildə dolduraq:

Müştəri cədvəlinin doldurulması

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

Beləliklə, iki cədvəl əldə etdik, onlardan birində 1 milyondan çox, digərində isə 10 milyondan çox məlumat var.

İndi Visual Studio-da siz test Visual C# Konsol Tətbiqi (.NET Framework) layihəsi yaratmalısınız:

Sonra, verilənlər bazası ilə qarşılıqlı əlaqə yaratmaq üçün Entity Framework üçün kitabxana əlavə etməlisiniz.
Onu əlavə etmək üçün layihənin üzərinə sağ klikləyin və kontekst menyusundan Manage NuGet Paketlərini seçin:

Sonra, görünən NuGet paket idarəetmə pəncərəsində axtarış qutusuna "Entity Framework" sözünü daxil edin və Entity Framework paketini seçin və quraşdırın:

Sonra, App.config faylında configSections elementini bağladıqdan sonra aşağıdakı bloku əlavə edin:

<connectionStrings>
    <add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

ConnectionString-də siz əlaqə sətirini daxil etməlisiniz.

İndi ayrı-ayrı fayllarda 3 interfeys yaradaq:

IBaseEntityID interfeysinin həyata keçirilməsi

namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

IBaseEntityName interfeysinin həyata keçirilməsi

namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

IBaseNameInsertUTCDate interfeysinin həyata keçirilməsi

namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

Ayrı bir faylda biz iki obyektimiz üçün ümumi sahələri ehtiva edən BaseEntity baza sinfi yaradacağıq:

BaseEntity baza sinfinin həyata keçirilməsi

namespace TestLINQ
{
    public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public DateTime InsertUTCDate { get; set; }
    }
}

Sonra, ayrı-ayrı fayllarda iki obyektimizi yaradacağıq:

Ref sinfinin həyata keçirilməsi

using System.ComponentModel.DataAnnotations.Schema;

namespace TestLINQ
{
    [Table("Ref")]
    public class Ref : BaseEntity
    {
        public int ID2 { get; set; }
    }
}

Müştəri sinfinin həyata keçirilməsi

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; }
    }
}

İndi ayrı faylda UserContext kontekstini yaradaq:

UserContex sinfinin həyata keçirilməsi

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 Server üçün EF vasitəsilə LINQ to SQL ilə optimallaşdırma testlərinin aparılması üçün hazır həll variantımız var:

İndi Program.cs faylında aşağıdakı kodu daxil edin:

Program.cs faylı

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();
            }
        }
    }
}

Sonra, layihəmizi icra edək.

İşin sonunda konsolda aşağıdakılar görünəcək:

Yaradılmış SQL sorğusu

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])

Yəni, ümumiyyətlə, LINQ sorğusu MS SQL Server DBMS-ə kifayət qədər yaxşı SQL sorğusu yaradıb.

İndi LINQ sorğusunda AND şərtini OR-a dəyişək:

LINQ sorğusu

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 };

Gəlin tətbiqimizi yenidən işə salaq.

İcra 30 saniyədən çox əmrin icra müddəti ilə bağlı xəta ilə çökəcək:

LINQ tərəfindən hansı sorğunun yaradıldığına baxsanız:

, onda seçimin iki dəstin (cədvəlin) Kartezyen hasilatı vasitəsilə baş verdiyinə əmin ola bilərsiniz:

Yaradılmış SQL sorğusu

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 sorğusunu bu şəkildə yenidən yazaq:

Optimallaşdırılmış LINQ sorğusu

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 });

Sonra aşağıdakı SQL sorğusunu alırıq:

SQL sorğusu

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]

Təəssüf ki, LINQ sorğularında yalnız bir qoşulma şərti ola bilər, ona görə də hər bir şərt üçün iki sorğu vasitəsilə ekvivalent sorğu etmək, ardınca sətirlər arasında dublikatları silmək üçün birlik vasitəsilə onların birləşməsi mümkündür.
Bəli, tam dublikat sətirlərin qaytarıla biləcəyini nəzərə alsaq, sorğular ümumiyyətlə qeyri-ekvivalent olacaq. Halbuki real həyatda tam dublikat sətirlərə ehtiyac yoxdur və onlardan qurtulmağa çalışırlar.

İndi bu iki sorğunun icra planlarını müqayisə edək:

CROSS JOIN üçün orta icra müddəti 195 saniyədir:
INNER JOIN-UNION üçün orta icra müddəti 24 saniyədən azdır:

.
Həmçinin Planlar qovluğundakı bu depoda OR şərtləri ilə sorğuların icrası üçün planlar var.

Mənbə: www.habr.com

MS SQL Server üçün C#.NET-də LINQ sorğularının optimallaşdırılmasının bəzi aspektləri

Добавить комментарий Cavab ləğv