🥇Neki aspekti optimizacije LINQ upita u C#.NET-u za MS SQL Server

LINQ je ušao u .NET kao moćan novi jezik za manipulaciju podacima. LINQ to SQL kao njegov dio omogućava vam da komunicirate sa DBMS-om prilično zgodno koristeći, na primjer, Entity Framework. Međutim, koristeći ga prilično često, programeri zaboravljaju da pogledaju kakvu će vrstu SQL upita upitni provajder generisati, u vašem slučaju, Entity Framework.

Pogledajmo dvije glavne točke na primjeru.
Da bismo to uradili, u SQL Serveru ćemo kreirati test baze podataka iu njemu ćemo kreirati dve tabele koristeći sledeći upit:

Kreiranje tabela

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

Sada popunimo Ref tabelu pokretanjem sljedeće skripte:

Popunjavanje 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

Popunimo tabelu Customer na isti način koristeći sljedeću skriptu:

Popunjavanje tabele kupaca

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

Tako smo dobili dvije tabele, od kojih jedna ima više od milion redova podataka, a druga više od 1 miliona redova podataka.

Sada u Visual Studiju, morate kreirati probni projekat Visual C# Console App (.NET Framework):

Zatim morate dodati biblioteku za Entity Framework za interakciju s bazom podataka.
Da biste ga dodali, kliknite desnim tasterom miša na projekat i izaberite Upravljanje NuGet paketima iz kontekstnog menija:

Zatim, u prozoru za upravljanje NuGet paketom koji se pojavi, u polju za pretragu, unesite riječ "Entity Framework" i odaberite Entity Framework paket i instalirajte ga:

Zatim, u datoteci App.config, nakon zatvaranja elementa configSections, dodajte sljedeći blok:

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

U connectionString trebate unijeti niz veze.

Sada kreirajmo 3 interfejsa u odvojenim fajlovima:

Implementacija IBaseEntityID sučelja

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

Implementacija sučelja IBaseEntityName

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

Implementacija IBaseNameInsertUTCDate sučelja

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

I u zasebnoj datoteci, kreirat ćemo osnovnu klasu BaseEntity za naša dva entiteta, koja će uključivati zajednička polja:

Implementacija osnovne klase BaseEntity

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

Zatim ćemo u odvojenim datotekama kreirati naša dva entiteta:

Implementacija Ref klase

using System.ComponentModel.DataAnnotations.Schema;

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

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

Sada kreirajmo kontekst UserContext u zasebnoj datoteci:

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

Dobili smo gotovo rješenje za provođenje optimizacijskih testova sa LINQ to SQL preko EF-a za MS SQL Server:

Sada u datoteku Program.cs unesite sljedeći kod:

Program.cs fajl

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

Zatim, pokrenimo naš projekat.

Na kraju rada, na konzoli će biti prikazano sljedeće:

Generirani SQL upit

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

To jest, općenito, LINQ upit je prilično dobro generirao SQL upit prema MS SQL Server DBMS-u.

Sada promijenimo uvjet AND u OR u LINQ upitu:

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

Pokrenimo ponovo našu aplikaciju.

Izvršenje će se srušiti s greškom koja se odnosi na vrijeme izvršenja naredbe duže od 30 sekundi:

Ako pogledate koji je upit generirao LINQ:

, tada možete osigurati da se odabir odvija kroz kartezijanski proizvod dva skupa (tablice):

Generirani SQL upit

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]

Prepišimo LINQ upit ovako:

Optimizirani LINQ upit

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

Tada dobijamo sljedeći SQL upit:

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]

Nažalost, u LINQ upitima može postojati samo jedan uslov spajanja, stoga je moguće napraviti ekvivalentan upit kroz dva upita za svaki uslov, nakon čega slijedi njihovo ujedinjenje kroz Uniju kako bi se uklonili duplikati među redovima.
Da, upiti će općenito biti neekvivalentni, s obzirom na to da se kompletni dupli redovi mogu vratiti. Međutim, u stvarnom životu, puni duplikati linija nisu potrebni i pokušavaju ih se riješiti.

Sada uporedimo planove izvršenja ova dva upita:

za CROSS JOIN, prosječno vrijeme izvršenja je 195 sekundi:
za INNER JOIN-UNION prosječno vrijeme izvršenja je manje od 24 sekunde:

Kao što se vidi iz rezultata, za dve tabele sa milionima zapisa optimizovani LINQ upit je mnogo puta brži od neoptimizovanog.

Za varijantu sa I u uslovima LINQ upita oblika:

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

gotovo uvijek će se generirati ispravan SQL upit, koji će se izvoditi u prosjeku oko 1 sekundu:

Također za LINQ to Objects manipulacije umjesto upita za pogled:

LINQ upit (1. opcija)

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

Možete koristiti upit poput:

LINQ upit (2. opcija)

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

gde:

Definiranje dva niza

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

, a tip Para je definiran na sljedeći način:

Definicija tipa para

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

Stoga smo razmotrili neke aspekte u optimizaciji LINQ upita za MS SQL Server.

Nažalost, čak i iskusni i vodeći .NET programeri zaboravljaju da je potrebno razumjeti šta upute koje koriste rade iza scene. U suprotnom, oni postaju konfiguratori i mogu postaviti tempiranu bombu u budućnosti, kako pri skaliranju softverskog rješenja, tako i uz manje promjene u vanjskim uvjetima okoline.

Bila je i mala recenzija ovdje.

Izvori za test - nalazi se sam projekat, kreiranje tabela u bazi podataka TEST, kao i popunjavanje ovih tabela podacima ovdje.
Takođe u ovom spremištu u folderu Planovi su planovi za izvršavanje upita sa uslovima ILI.

izvor: www.habr.com

Neki aspekti optimizacije LINQ upita u C#.NET-u za MS SQL Server

Dodajte komentar ÐžÑ,Ð¼ÐμÐ½Ð¸Ñ,ÑŒ Ð¾Ñ,Ð²ÐμÑ,

Dodajte komentar