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

LINQ je u .NET ušao kao novi moćan jezik za manipulaciju podacima. LINQ to SQL kao njegov deo omogućava relativno jednostavnu komunikaciju sa SGBD-om putem, na primer, Entity Framework-a. Međutim, često koristeći ga, developeri zaboravljaju da obrate pažnju na to koji tačno SQL upit će generisati queryable provider, u vašem slučaju — Entity Framework.

Razmotrit ćemo dva osnovna aspekta na primeru.
Za to ćemo u SQL Server-u kreirati bazu podataka Test, a u njoj ćemo kreirati dvije tabele pomoću sljedećeg upita:

Kreiranje tablica

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 ćemo popuniti tabelu Ref pokretanjem sljedećeg skripta:

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

Na sličan način popunićemo tabelu Customer pomoću sljedećeg skripta:

Popunjavanje tabele Customer

KORISTITE [TEST]
IDITE

DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;

DOK(@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=0) SET @ind_ref=4;
	ELSE IF (@ind=0) SET @ind_ref=5;
	ELSE IF (@ind=0) SET @ind_ref=6;
	ELSE IF (@ind=0) SET @ind_ref=7;
	ELSE IF (@ind=0) SET @ind_ref=8;
	ELSE IF (@ind=0) SET @ind_ref=9;
	ELSE IF (@ind=0) SET @ind_ref=10;
	ELSE IF (@ind=0) SET @ind_ref=11;
	ELSE SET @ind_ref=@ind90000;
	
	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
IDITE

Na taj način smo dobili dvije tabele, od kojih jedna sadrži više od 1 milion redova podataka, a druga više od 10 miliona redova podataka.

Sada u Visual Studiju potrebno je kreirati testni projekat Visual C# Console App (.NET Framework):

Nakon toga, potrebno je dodati biblioteku za Entity Framework za interakciju sa bazom podataka.
Da bismo je dodali, desnom tipkom miša kliknemo na projekt i u kontekstnom izborniku odaberemo Upravljanje NuGet paketima:

Zatim u prozoru za upravljanje NuGet paketima u okvir za pretraživanje unesemo riječ „Entity Framework“ i odaberemo paket Entity Framework te ga instaliramo:

Nakon toga u datoteci App.config nakon zatvaranja elementa configSections potrebno je dodati sljedeći blok:

U connectionString potrebno je unijeti vezu.

Sada ćemo stvoriti tri sučelja u zasebnim datotekama:

Implementacija sučelja IBaseEntityID

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

Implementacija sučelja IBaseEntityName

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

Implementacija sučelja IBaseNameInsertUTCDate

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

I u zasebnoj datoteci stvorit ćemo osnovnu klasu BaseEntity za naše dvije entitete, koja će sadržavati 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 u zasebnim datotekama stvorimo naše dvije entitete:

Implementacija klase Ref

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 ćemo stvoriti kontekst UserContext u zasebnoj datoteci:

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

Dobili smo spremno rješenje za provođenje optimizacijskih testova s LINQ to SQL putem EF za MS SQL Server:

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

Datoteka Program.cs

koristeći System;
koristeći System.Collections.Generic;
koristeći 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();
            }
        }
    }
}

Zatim ćemo pokrenuti naš projekt.

Na kraju rada na konzoli će biti prikazano:

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, ukupno je prilično dobro LINQ upit generirao SQL upit prema MS SQL Serveru.

Sada ćemo promijeniti uvjet I na Ili 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 };

I opet pokrenimo našu aplikaciju.

Izvršenje će se završiti greškom zbog premašivanja vremena izvršavanja naredbe od 30 sekundi:

Ako pogledamo koji je upit tada bio generiran LINQ:

, možemo se uvjeriti da se izbor vrši putem kartezijevog produkta dvaju skupova (tablica):

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]

Hajde da prepišemo LINQ upit na sljedeći način:

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 ćemo dobiti 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 uvjet za spajanje, stoga je moguće napraviti ekvivalentan upit kroz dva upita za svako stanje, a potom ih spojiti putem Union kako bi se uklonile duplicirane linije.
Da, upiti će u općem smislu biti neekvivalentni s obzirom na to da se mogu vratiti puni duplicirani redovi. Međutim, u stvarnom životu puni duplicirani redovi nisu potrebni i nastoji se riješiti tih slučajeva.

Sada usporedimo planove izvršavanja ta dva upita:

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

Kao što se može vidjeti iz rezultata, optimizirani LINQ upit radi nekoliko puta brže nego neoptimizirani za dva tablice s milijunima zapisa.

Za varijantu s AND u uvjetima LINQ upit izgleda ovako:

LINQ upit

var upit = 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 biti generiran ispravan SQL upit koji će se izvršavati u prosjeku otprilike 1 sekundu:

Također, za manipulacije LINQ to Objects umjesto upita izgleda:

LINQ upit (1. varijanta)

var upit = 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 koji izgleda ovako:

LINQ upit (2. varijanta)

var upit = 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 };

gdje:

Definicija 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 definira se na sljedeći način:

Definicija tipa Para

class Para
{
        public int Ključ1, Ključ2;
        public string Podaci;
}

Tako smo razmotrili neke aspekte optimizacije LINQ upita za MS SQL Server.

Nažalost, čak i iskusni i vodeći .NET programeri zaboravljaju da je važno razumeti što rade iza kulisa s onim instrukcijama koje koriste. Inače postaju konfiguratori i mogu postaviti bombu sa odgodom za budućnost, bilo pri skaliranju softverskog rešenja, bilo pri minimalnim promenama u spoljnim uslovima okruženja.

Takođe je obavljen kratak pregled i ovdje.

Izvori za test - sam projekt, kreiranje tabela u bazi podataka TEST, kao i popunjavanje ovih tabela se nalazi ovdje.
Takođe, u ovom repozitoriju u folderu Plans nalaze se planovi izvršavanja upita sa uslovima ILI.

Izvor: habr.com