ProHoster > Blog > Administracija > Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server

Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server

LINQ je v .NET vstopil kot nov močan jezik za manipulacijo s podatki. LINQ to SQL kot del tega omogoča dokaj enostavno komunikacijo s SGBD, na primer z uporabo Entity Framework. Vendar pa razvijalci, ki ga pogosto uporabljajo, pogosto pozabijo preveriti, kateri SQL poizvedba bo generirana s strani queryable providerja, v vašem primeru — Entity Framework.

Razmislimo o dveh glavnih točkah na primeru.
Za to v SQL Serverju ustvarimo bazo podatkov Test, v kateri bomo ustvarili dve tabeli s pomočjo naslednje poizvedbe:

Создание таблиц

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

Sedaj bomo napolnili tabelo Ref s pomočjo izvajanja naslednjega skripta:

Polnjenje tabele 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

Podobno bomo napolnili tabelo Customer s pomočjo naslednjega skripta:

Polnjenje tabele Customer

UPORABI [TEST]
GO

DEKLARIRAJ @ind INT=1;
DEKLARIRAJ @ind_ref INT=1;

MEDTEM(@ind<=12000000)
ZAČNI
	ČE(@ind%3=0) POSTAVI @ind_ref=1;
	ELSE ČE (@ind%5=0) POSTAVI @ind_ref=2;
	ELSE ČE (@ind%7=0) POSTAVI @ind_ref=3;
	ELSE ČE (@ind=0) POSTAVI @ind_ref=4;
	ELSE ČE (@ind=0) POSTAVI @ind_ref=5;
	ELSE ČE (@ind=0) POSTAVI @ind_ref=6;
	ELSE ČE (@ind=0) POSTAVI @ind_ref=7;
	ELSE ČE (@ind=0) POSTAVI @ind_ref=8;
	ELSE ČE (@ind=0) POSTAVI @ind_ref=9;
	ELSE ČE (@ind=0) POSTAVI @ind_ref=10;
	ELSE ČE (@ind=0) POSTAVI @ind_ref=11;
	ELSE POSTAVI @ind_ref=@ind90000;
	
	VSTAVI V [dbo].[Stranka]
	 ([ID]
	 ,[Ime]
	 ,[Ref_ID]
	 ,[Ref_ID2])
	 IZBERI
	 @ind,
	 CAST(@ind AS NVARCHAR(255)),
	 @ind_ref,
	 @ind_ref;


	SET @ind=@ind+1;
KONEC
GO

Tako smo pridobili dve tabeli, v katerih je v eni več kot 1 milijon podatkovnih vrstic, v drugi pa več kot 10 milijonov podatkovnih vrstic.

Sedaj je potrebno v Visual Studiu ustvariti testni projekt Visual C# Console App (.NET Framework):

Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server

Nato je potrebno za interakcijo z bazo podatkov dodati knjižnico za Entity Framework.
Za dodajanje, z desno tipko kliknemo na projekt in v kontekstnem meniju izberemo Manage NuGet Packages:

Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server

Nato v pojavnem oknu za upravljanje NuGet paketov v iskalno polje vpišemo besedo „Entity Framework“ in izberemo paket Entity Framework ter ga namestimo:

Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server

Nato v datoteki App.config po zapiranju elementa configSections dodamo naslednji blok:

V connectionString je treba vpisati niz povezave.

Zdaj v ločenih datotekah ustvarimo 3 vmesnike:

  1. Implementacija vmesnika IBaseEntityID
    namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

  2. Implementacija vmesnika IBaseEntityName
    namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

  3. Implementacija vmesnika IBaseNameInsertUTCDate
    namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

In v ločeni datoteki ustvarimo osnovni razred BaseEntity za naše dve entiteti, ki bo vseboval skupna polja:

Implementacija osnovnega razreda BaseEntity

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

Nato v ločenih datotekah ustvarimo naši dve entiteti:

  1. Implementacija razreda Ref
    using System.ComponentModel.DataAnnotations.Schema;

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

  2. Implementacija razreda Customer
    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; }
    }
}

Zdaj v ločeni datoteki ustvarimo kontekst UserContext:

Implementacija razreda UserContext

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

Dobilo se je končno rešitev za optimizacijo testov z LINQ to SQL preko EF za MS SQL Server:

Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server

Zdaj v datoteko Program.cs vpišemo naslednjo kodo:

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

Nato bomo zagnali naš projekt.

Na koncu dela bo na konzoli prikazano:

Generiran SQL poizvedba

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

Torej, v splošnem je LINQ poizvedba zelo dobro generirala SQL poizvedbo za MS SQL Server.

Zdaj bomo spremenili pogoj IN na OR v LINQ poizvedbi:

LINQ upit

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

In ponovno bomo zagnali našo aplikacijo.

Izvajanje bo prekinjeno z napako, povezano s prekoračitvijo časa izvajanja ukaza v 30 sek:

Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server

Če pogledamo, katera poizvedba je bila generirana z LINQ:

Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server
, lahko zagotovimo, da se izbor izvaja prek kartezičnega produkta dveh množic (tabel):

Generiran SQL poizvedba

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]

Prepisali bomo LINQ poizvedbo na naslednji način:

Optimizirana LINQ poizvedba

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

Takrat bomo dobili naslednjo SQL poizvedbo:

SQL upit

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]

Žal, v LINQ poizvedbah lahko obstaja samo ena pogojna povezava, zato je možno narediti ekvivalentno poizvedbo z dvema poizvedbama za vsak pogoj in ju združiti z Union za odstranitev podvojenih vrstic.
Da, poizvedbe v splošnem primeru ne bodo ekvivalentne, saj se lahko vrnejo popolni podvojeni zapisi. Vendar pa v resničnem svetu popolni podvojeni zapisi niso potrebni in se jih trudimo znebiti.

Zdaj primerjajmo načrte izvajanja teh dveh poizvedb:

  1. za CROSS JOIN je povprečni čas izvajanja 195 sekund:
    Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server
  2. za INNER JOIN-UNION je povprečni čas izvajanja manj kot 24 sekund:
    Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server

Kot je vidno iz rezultatov, optimizirana LINQ poizvedba deluje večkrat hitreje kot neoptimizirana za dve tabeli z milijoni zapisov.

Za različico z AND v pogojih LINQ poizvedbe:

LINQ upit

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

skoraj vedno bo ustvarjena pravilna SQL poizvedba, ki se v povprečju izvaja približno 1 sekundo:

Nekateri vidiki optimizacije LINQ poizvedb v C#.NET za MS SQL Server
Prav tako za manipulacije LINQ to Objects namesto poizvedbe v obliki:

LINQ poizvedba (1. različica)

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

lahko uporabimo poizvedbo v obliki:

LINQ poizvedba (2. različica)

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

kde:

Določitev dveh tabel

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

, tip Para pa je določen kot:

Določitev tipa Para

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

Tako smo obravnavali nekatere vidike optimizacije LINQ poizvedb za MS SQL Server.

Žal tudi izkušeni in vodilni .NET razvijalci pozabljajo, da je treba razumeti, kaj se dogaja za kulisami tistih ukazov, ki jih uporabljajo. V nasprotnem primeru postanejo konfiguratorji in lahko postavijo bombo s časovnikom v prihodnosti, tako pri razširjanju programske rešitve, kot tudi pri manjših spremembah zunanjih pogojev okolja.

Prav tako je bila opravljena kratka predstavitev ovde.

Izvorna koda za test - sam projekt, ustvarjanje tabel v podatkovni bazi TEST ter polnjenje teh tabel z podatki se nahaja ovde.
Prav tako v tej repozitoriju v mapi Plans najdemo načrte za izvajanje poizvedb s pogoji OR.

Izvor: habr.com

Kupite zanesljivo gostovanje za spletne strani z zaščito pred DDoS, VPS VDS strežniki 🔥 Kupite zanesljivo gostovanje za spletne strani z zaščito pred DDoS, VPS VDS strežniki | ProHoster