🥇O nisi vaega o le fa'amalieina o fesili LINQ ile C#.NET mo MS SQL Server

Na ulufale le LINQ i le .NET o se gagana fou e fa'aogaina ai fa'amatalaga. O le LINQ i le SQL o se vaega e mafai ai ona e fesoʻotaʻi faigofie ma se DBMS faʻaaoga, mo se faʻataʻitaʻiga, Entity Framework. Ae ui i lea, o le faʻaaogaina i le tele o taimi, e galo ai i le au atinaʻe le vaʻavaʻai poʻo le a le ituaiga o fesili SQL e mafai ona maua e le kamupani e mafai ona fesiligia, i lau mataupu Entity Framework.

Seʻi o tatou tilofaʻia manatu autū e lua e faaaogā ai se faaaʻoaʻoga.
Ina ia faia lenei mea, fai se suʻega faʻamaumauga i le SQL Server, ma faia ni laulau se lua i totonu e faʻaaoga ai le fesili lenei:

Fausia laulau

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

Sei o tatou faʻatumu le laulau Ref e ala i le faʻaogaina o le tusitusiga lenei:

Faatumu le laulau 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

Se'i fa'apena fo'i ona fa'atumu le laulau a le Tagata Fa'atau e fa'aaoga ai le fa'amatalaga lea:

Fa'atumuina le laulau a Tagata Fa'atau

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

O lea la, na matou mauaina ni laulau se lua, o le tasi e sili atu i le 1 miliona laina o faʻamaumauga, ma le isi e sili atu i le 10 miliona laina o faʻamaumauga.

Ole taimi nei ile Visual Studio e te mana'omia e fai se su'ega Visual C# Console App (.NET Framework) poloketi:

O le isi, e mana'omia lou fa'aopoopoina o se faletusi mo le Entity Framework e fegalegaleai ai ma le database.
Ina ia faʻaopoopoina, kiliki-saʻo i luga o le poloketi ma filifili Manage NuGet Packages mai le lisi autu:

Ona, i le NuGet faʻamalama faʻamalama o loʻo aliali mai, faʻapipiʻi le upu "Entity Framework" i le faʻamalama suʻesuʻe ma filifili le Entity Framework package ma faʻapipiʻi:

I le isi, i le faila App.config, pe a uma ona tapunia le elemene configSections, e tatau ona e faʻaopoopo le poloka lea:

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

I le connectionString e tatau ona e ulufale i le manoa fesoʻotaʻiga.

Sei o tatou fatuina ni fesoʻotaʻiga 3 i faila eseese:

Faʻatinoina o le IBaseEntityID interface

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

Fa'atinoina o le IBaseEntityName interface

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

Fa'atinoina o le fa'aoga IBaseNameInsertUTCDate

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

Ma i se isi faila o le a tatou fatuina se vasega faavae BaseEntity mo a tatou vaega e lua, lea o le a aofia ai fanua masani:

Fa'atinoina o le vasega faavae BaseEntity

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

Ma le isi, o le a matou fatuina a matou vaega e lua i faila eseese:

Fa'atinoga o le vasega Ref

using System.ComponentModel.DataAnnotations.Schema;

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

Fa'atinoga o le vasega Fa'atau

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

Sei o tatou fatuina se faʻamatalaga UserContext i se faila ese:

Fa'atinoina o le vasega 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; }
    }
}

Na matou mauaina se fofo ua saunia mo le faʻatinoina o suʻega suʻesuʻe ma le LINQ i le SQL e ala i le EF mo le MS SQL Server:

Fa'aulu loa le fa'ailoga lea i le faila o le Program.cs:

Polokalama.cs faila

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

Sosoo ai, se'i amata la tatou galuega.

I le faaiuga o le galuega, o mea nei o le a faʻaalia i luga o le faʻamafanafanaga:

Fausia SQL Query

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

O lona uiga, i se tulaga lautele, o le LINQ fesili na faʻatupuina se fesili SQL i le MS SQL Server DBMS lelei.

Sei o tatou suia le AND condition ile OR ile LINQ fesili:

LINQ fesili

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

Ma tatou toe fa'alauiloa la tatou talosaga.

O le faʻatinoga o le a paʻu i se mea sese ona o le faʻatonuga o le taimi e sili atu i le 30 sekone:

Afai e te vaʻai i le fesili na faia e LINQ:

, ona mafai lea ona e mautinoa o le filifiliga e tupu mai i le Cartesian oloa o seti e lua (laupapa):

Fausia SQL Query

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]

Sei o tatou toe tusi le fesili LINQ e pei ona taua i lalo:

Fa'atonu LINQ fesili

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

Ona matou maua lea o le fesili SQL nei:

SQL fesili

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]

Talofa, ile LINQ fesili e na'o le tasi le tu'ufa'atasiga, o lea e mafai ai ona fai se fesili tutusa e fa'aaoga ai fesili se lua mo tulaga ta'itasi ona tu'ufa'atasia lea e ala i le Iuni e aveese fa'alua i totonu o laina.
Ioe, o fesili e masani lava e le tutusa, ma le manatu e mafai ona toe fa'afo'i atu laina fa'alua atoa. Ae ui i lea, i le olaga moni, e le manaʻomia ni laina faʻalua atoa ma taumafai tagata e faʻaumatia.

Se'i o tatou fa'atusatusaina fuafuaga fa'atino o fesili nei e lua:

mo CROSS JOIN o le averesi o le taimi o le faʻatinoga o le 195 sekone:
mo INNER JOIN-UNION ole averesi ole taimi ole faatinoga e itiiti ifo ile 24 sekone:

Как видно из результатов, для двух таблиц с миллионами записей оптимизированный LINQ-запрос работает в разы быстрее, чем неоптимизированный.

Для варианта с И в условиях LINQ-запрос вида:

LINQ fesili

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

почти всегда будет сгенерирован правильный SQL-запрос, который будет выполняться в среднем примерно 1 сек:

Также для манипуляций LINQ to Objects вместо запроса вида:

LINQ-запрос (1-й вариант)

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

можно использовать запрос вида:

LINQ-запрос (2-й вариант)

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

pe afai:

Определение двух массивов

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

, а тип Para определяется следующим образом:

Определение типа Para

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

Таким образом мы рассмотрели некоторые аспекты в оптимизации LINQ-запросов к MS SQL Server.

К сожалению даже опытные и ведущие .NET-разработчики забывают о том, что необходимо понимать что делают за кадром те инструкции, которые они используют. Иначе они становятся конфигураторами и могут заложить бомбу замедленного действия в будущем как при масштабировании программного решения, так и при незначительных изменениях внешних условий среды.

Также небольшой обзор проводился и iinei.

Исходники для теста-сам проект, создание таблиц в базе данных TEST, а также наполнение данными этих таблиц находится iinei.
I totonu foi o lenei fale teu oloa, i totonu o le Fuafuaga faila, o loʻo i ai fuafuaga mo le faʻatinoina o faʻamatalaga ma tulaga OR.

puna: www.habr.com

O nisi vaega o le fa'asilisiliina ole LINQ fesili ile C#.NET mo MS SQL Server

Faaopoopo i ai se faamatalaga faalēaogāina tali