🥇Mõned aspektid LINQ-päringute optimeerimisest C#.NET-is MS SQL Serveri jaoks

LINQ tuli .NET-i uue võimsa andme manipuleerimise keeles. LINQ to SQL, selle osana, võimaldab mugavalt suhelda andmebaasidega näiteks Entity Frameworki kaudu. Kuid tihti, seda kasutades, unustavad arendajad vaadata, milline SQL-päring genereeritakse queryable provider'i poolt, teie puhul — Entity Framework.

Vaatame kaht põhiaspekti näite kaudu.
Selleks loome SQL Serveris andmebaasi Test ja loome seal kaks tabelit järgmise päringu abil:

Tabelite loomine

KASUTA [TEST]
GO

SEADKE ANSI_NULLS ON
GO

SEADKE QUOTED_IDENTIFIER ON
GO

LOO KREATE 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

KASUTA [TEST]
GO

SEADKE ANSI_NULLS ON
GO

SEADKE QUOTED_IDENTIFIER ON
GO

LOO KREATE 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

Nüüd täitke tabel Ref järgmise skripti käivitamisega:

Tabeli Ref täitmine

KASUTA [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

Samamoodi täidame tabeli Customer järgmise skripti abil:

Tabeli Customer täitmine

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

Nii sai meil kaks tabelit, millest ühes on üle 1 miljon rida andmeid ja teises üle 10 miljoni rida andmeid.

Nüüd tuleb Visual Studios luua testprojekt Visual C# Console App (.NET Framework):

Seejärel peab andmebaasiga suhtlemiseks lisama Entity Framework'i teegi.
Selle lisamiseks paremklõpsame projektil ja valime kontekstimenüüst Manage NuGet Packages:

Seejärel sisestame avanevas NuGet-pakettide haldamise aknas otsinguväljas sõna „Entity Framework” ja valime paketi Entity Framework ning installime selle:

Seejärel tuleb failis App.config configSections elemendi sulgemise järel lisada järgmine plokk:

Tuleb connectionStringi kirjutada ühenduse string.

Nüüd loome eraldi failides 3 liidest:

IBaseEntityID liidese rakendamine

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

IBaseEntityName liidese rakendamine

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

IBaseNameInsertUTCDate liidese rakendamine

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

Ja eraldi failis loome baasklass BaseEntity, kuhu kuuluvad ühised väljad:

BaseEntity baasklassi rakendamine

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

Seejärel loome eraldi failides meie kaks entsüsteemi:

Ref klassi rakendamine

using System.ComponentModel.DataAnnotations.Schema;

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

Customer klassi rakendamine

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

Nüüd loome eraldi failis konteksti UserContext:

Klassi UserContext rakendamine

using System.Data.Entity;

namespace TestLINQ
{
    public class UserContext : DbContext
    {
        public UserContext()
            : base("DbConnection")
        {
            Database.SetInitializer(null);
        }

        public DbSet Customer { get; set; }
        public DbSet Ref { get; set; }
    }
}

Oleme saanud valmis lahenduse testimiseks LINQ to SQL kaudu EF MS SQL Serverile optimeerimiseks:

Nüüd sisestame faili Program.cs järgmise koodi:

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

Seejärel käivitame meie projekti.

Töö lõpus kuvatakse konsoolil:

Genereeritud SQL-päring

VALI TOP (1000) 
    [Extent1].[Ref_ID] NAG [Ref_ID], 
    [Extent1].[Name] NAG [Name], 
    [Extent2].[Name] NAG [Name1]
    FROM  [dbo].[Customer] NAG [Extent1]
    SISELIKE [dbo].[Ref] NAG [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) JA ([Extent1].[Ref_ID2] = [Extent2].[ID2])

Nagu öeldud, on LINQ päring tõeliselt hästi genereerinud SQL päringu MS SQL Serveri andmebaasi.

Nüüd muudame tingimuse 'JA' peale 'VÕI' LINQ päringus:

LINQ-päring

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

Ja käivitame uuesti meie rakenduse.

Täidamine katkeb veateatega, mis on seotud käsu täitmise aja ületamisega 30 sekundi jooksul:

Kui vaadata, milline päring on selle käigus genereeritud LINQ:

, siis saab veenduda, et valik tehakse kahe hulkade (tabelite) dekartaalsete korrutiste kaudu:

Genereeritud SQL-päring

VALI TOP (1000) 
    [Extent1].[Ref_ID] NAG [Ref_ID], 
    [Extent1].[Name] NAG [Name], 
    [Extent2].[Name] NAG [Name1]
    FROM  [dbo].[Customer] NAG [Extent1]
    RISTI ÜHENDUS [dbo].[Ref] NAG [Extent2]
    KUS [Extent1].[Ref_ID] = [Extent2].[ID] VÕI [Extent1].[Ref_ID2] = [Extent2].[ID2]

Küsimus on, kuidas saaksime LINQ päringu järgmiselt ümber kirjutada:

Optimeeritud LINQ päring

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

Siis saame järgmise SQL-päringu:

SQL-päring

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]

Kahjuks saavad LINQ-päringutes ühenduse tingimuseks olla ainult ühed, mistõttu on siin võimalik teha ekvivalentne päring kahes eraldi päringus, millest kummagi tingimuse kohaselt järgnevalt nende ühendamine Union-iga, et eemaldada ridu duplikaadid.
Jah, päringud on üldiselt mittesarnased, arvestades, et võivad tagasi anda täielikke ridade koopiaid. Siiski, reaalses elus ei ole täielikud kopeeritud read vajalikud ja neist püüab lahti saada.

Võrdleme nüüd nende kahe päringu täitmise plaane:

CROSS JOINi puhul on keskmine täitmise aeg 195 sek:
INNER JOIN-UNION puhul on keskmine täitmise aeg alla 24 sek:

Nagu nähtub tulemustest, töötab optimeeritud LINQ-päring kahel miljonite ridade tabelil mitu korda kiiremini kui optimeerimata.

LINQ-päringu tingimuste puhul, kus on ja:

LINQ-päring

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

peaaegu alati genereeritakse õige SQL-päring, mis täitub keskmiselt umbes 1 sek:

Lisaks LINQ to Objects manipulatsioonide jaoks, erinevalt päringu vormist:

LINQ-päring (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 };

võib kasutada päringut järgmisel kujul:

LINQ-päring (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 };

kus:

Kahte massiivi määratlemine

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

, ja tüüp Para on määratletud järgmiselt:

Tüübi Para määratlemine

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

Nii oleme käsitlenud mõningaid aspekte LINQ-päringute optimeerimisel MS SQL Serveris.

Kahjuks unustavad isegi kogenud ja juhtivad .NET-arendajad aru saada, mida teavad nende kasutatavad käsklused. Vastasel juhul muutuvad nad seadistajateks ja võivad tulevikus luua ajaliselt piiritletud probleemi nii programmilahenduse laiendamisel kui ka keskkonna tingimuste väiksematel muutustel.

Samuti viidi läbi väike ülevaade siit.

Testi lähtekood – ise projekt, TEST andmebaasi tabelite loomine ja nende tabelite andmetega täitmine on siit.
Selles hoidlas, kaustas Plans, on samuti päringute täitmise plaanid, mille tingimuseks on OR.

Allikas: habr.com

Mõned aspektid LINQ-päringute optimeerimisest C#.NET-ile MS SQL Serveri jaoks