🥇Disa aspekte të optimizimit të pyetjeve LINQ në C#.NET për MS SQL Server

LINQ hyri në .NET si një gjuhë e re e fuqishme e manipulimit të të dhënave. LINQ në SQL si pjesë e tij ju lejon të komunikoni mjaft mirë me një DBMS duke përdorur, për shembull, Entity Framework. Megjithatë, duke e përdorur atë mjaft shpesh, zhvilluesit harrojnë të shikojnë se çfarë lloj pyetjeje SQL do të gjenerojë ofruesi i pyetjes, në rastin tuaj Entity Framework.

Le të shohim dy pika kryesore duke përdorur një shembull.
Për ta bërë këtë, krijoni një bazë të dhënash Test në SQL Server dhe krijoni dy tabela në të duke përdorur pyetjen e mëposhtme:

Krijimi i tabelave

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

Tani le të plotësojmë tabelën Ref duke ekzekutuar skriptin e mëposhtëm:

Plotësimi i tabelës 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

Le të plotësojmë në mënyrë të ngjashme tabelën e Klientit duke përdorur skriptin e mëposhtëm:

Plotësimi i tabelës së klientit

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

Kështu, morëm dy tabela, njëra prej të cilave ka më shumë se 1 milion rreshta të dhënash dhe tjetra ka më shumë se 10 milion rreshta të dhënash.

Tani në Visual Studio ju duhet të krijoni një projekt testues Visual C# Console App (.NET Framework):

Më pas, ju duhet të shtoni një bibliotekë që Korniza e Entitetit të ndërveprojë me bazën e të dhënave.
Për ta shtuar atë, kliko me të djathtën mbi projekt dhe zgjidhni Menaxho NuGet Packages nga menyja e kontekstit:

Më pas, në dritaren e menaxhimit të paketës NuGet që shfaqet, futni fjalën "Entity Framework" në dritaren e kërkimit dhe zgjidhni paketën Entity Framework dhe instaloni:

Më pas, në skedarin App.config, pasi të mbyllni elementin configSections, duhet të shtoni bllokun e mëposhtëm:

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

Në ConnectionString duhet të futni vargun e lidhjes.

Tani le të krijojmë 3 ndërfaqe në skedarë të veçantë:

Implementimi i ndërfaqes IBaseEntityID

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

Zbatimi i ndërfaqes IBaseEntityName

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

Zbatimi i ndërfaqes IBaseNameInsertUTCDate

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

Dhe në një skedar të veçantë do të krijojmë një klasë bazë BaseEntity për dy entitetet tona, e cila do të përfshijë fusha të përbashkëta:

Zbatimi i klasës bazë BaseEntity

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

Më pas, ne do të krijojmë dy entitetet tona në skedarë të veçantë:

Zbatimi i klasës Ref

using System.ComponentModel.DataAnnotations.Schema;

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

Zbatimi i klasës së klientit

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

Tani le të krijojmë një kontekst UserContext në një skedar të veçantë:

Zbatimi i klasës 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; }
    }
}

Ne morëm një zgjidhje të gatshme për kryerjen e testeve të optimizimit me LINQ në SQL përmes EF për MS SQL Server:

Tani futni kodin e mëposhtëm në skedarin Program.cs:

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

Më pas, le të fillojmë projektin tonë.

Në fund të punës, sa vijon do të shfaqet në tastierë:

Kërkesa e gjeneruar 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])

Kjo do të thotë, në përgjithësi, pyetja LINQ gjeneroi mjaft mirë një pyetje SQL në DBMS të MS SQL Server.

Tani le të ndryshojmë kushtin AND në OSE në pyetjen LINQ:

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

Dhe le të hapim përsëri aplikacionin tonë.

Ekzekutimi do të rrëzohet me një gabim për shkak të kohës së ekzekutimit të komandës që tejkalon 30 sekonda:

Nëse shikoni pyetjen që u krijua nga LINQ:

, atëherë mund të siguroheni që përzgjedhja të ndodhë përmes produktit kartezian të dy grupeve (tabelave):

Kërkesa e gjeneruar 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]

Le të rishkruajmë pyetjen LINQ si më poshtë:

Kërkesa e optimizuar 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 });

Pastaj marrim pyetjen e mëposhtme SQL:

Kërkesa 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]

Mjerisht, në pyetjet LINQ mund të ketë vetëm një kusht bashkimi, kështu që këtu është e mundur të bëhet një pyetje ekuivalente duke përdorur dy pyetje për secilën kusht dhe më pas duke i kombinuar ato përmes Unionit për të hequr dublikatat midis rreshtave.
Po, pyetjet në përgjithësi do të jenë jo ekuivalente, duke marrë parasysh që mund të kthehen rreshtat e plotë të kopjuar. Sidoqoftë, në jetën reale, linjat e plota të kopjuara nuk janë të nevojshme dhe njerëzit përpiqen t'i heqin qafe ato.

Tani le të krahasojmë planet e ekzekutimit të këtyre dy pyetjeve:

për CROSS JOIN koha mesatare e ekzekutimit është 195 sekonda:
për JOIN-UNION INNER koha mesatare e ekzekutimit është më pak se 24 sekonda:

Siç mund ta shihni nga rezultatet, për dy tabela me miliona regjistrime, pyetja e optimizuar LINQ është shumë herë më e shpejtë se ajo e pa optimizuar.

Për opsionin me AND në kushtet, një pyetje LINQ e formularit:

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

Pyetja e saktë SQL do të gjenerohet pothuajse gjithmonë, e cila do të ekzekutohet mesatarisht në rreth 1 sekondë:

Gjithashtu për manipulimet LINQ to Objects në vend të një pyetjeje si:

Pyetja LINQ (opsioni i parë)

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

mund të përdorni një pyetje si:

Pyetja LINQ (opsioni i parë)

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

ku:

Përcaktimi i dy vargjeve

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

, dhe lloji Para përcaktohet si më poshtë:

Përkufizimi i llojit të parave

class Para
{
        public int Key1, Key2;
        public string Data;
}

Kështu, ne shqyrtuam disa aspekte në optimizimin e pyetjeve LINQ në MS SQL Server.

Fatkeqësisht, edhe zhvilluesit me përvojë dhe udhëheqës të .NET harrojnë se duhet të kuptojnë se çfarë bëjnë udhëzimet që përdorin prapa skenave. Përndryshe, ata bëhen konfigurues dhe mund të vendosin një bombë me sahat në të ardhmen si gjatë shkallëzimit të zgjidhjes softuerike ashtu edhe me ndryshime të vogla në kushtet e jashtme të mjedisit.

Gjithashtu është bërë një rishikim i shkurtër këtu.

Burimet për testin - vetë projekti, krijimi i tabelave në bazën e të dhënave TEST, si dhe plotësimi i këtyre tabelave me të dhëna gjenden këtu.
Gjithashtu në këtë depo, në dosjen Plans, ka plane për ekzekutimin e pyetjeve me kushte OR.

Burimi: www.habr.com

Disa aspekte të optimizimit të pyetjeve LINQ në C#.NET për MS SQL Server

Shto një koment Cancel përgjigje