Някои аспекти на оптимизацията на LINQ заявки в C#.NET за MS SQL Server

LINQ навлезе в .NET като мощен нов език за манипулиране на данни. LINQ to SQL като част от него ви позволява да комуникирате със СУБД доста удобно, като използвате, например, Entity Framework. Въпреки това, използвайки го доста често, разработчиците забравят да погледнат какъв вид SQL заявка ще генерира доставчикът, който подлежи на запитване, във вашия случай, Entity Framework.

Нека разгледаме две основни точки с пример.
За да направим това, в SQL Server ще създадем база данни Test и в нея ще създадем две таблици, използвайки следната заявка:

Създаване на таблици

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

Сега нека попълним таблицата 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

Нека попълним таблицата Customer по същия начин, като използваме следния скрипт:

Попълване на таблицата Клиент

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

Така получихме две таблици, едната от които има повече от 1 милион реда с данни, а другата има повече от 10 милиона реда с данни.

Сега във Visual Studio трябва да създадете тестов проект за конзолно приложение на Visual C# (.NET Framework):

След това трябва да добавите библиотека за Entity Framework, за да взаимодейства с базата данни.
За да го добавите, щракнете с десния бутон върху проекта и изберете Управление на пакети NuGet от контекстното меню:

След това в прозореца за управление на пакети NuGet, който се показва, в полето за търсене въведете думата „Entity Framework“ и изберете пакета Entity Framework и го инсталирайте:

След това във файла App.config, след като затворите елемента configSections, добавете следния блок:

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

В connectionString трябва да въведете низа за връзка.

Сега нека създадем 3 интерфейса в отделни файлове:

1. Внедряване на интерфейса IBaseEntityID

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

2. Внедряване на интерфейса IBaseEntityName

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

3. Внедряване на интерфейса IBaseNameInsertUTCDate

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

И в отделен файл ще създадем базов клас BaseEntity за нашите два обекта, който ще включва общи полета:

Реализация на базовия клас BaseEntity

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

След това в отделни файлове ще създадем нашите два обекта:

1. Реализация на референтен клас

   using System.ComponentModel.DataAnnotations.Schema;
   
   namespace TestLINQ
   {
       [Table("Ref")]
       public class Ref : BaseEntity
       {
           public int ID2 { get; set; }
       }
   }
   

2. Реализация на клас Клиент

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

Сега нека създадем контекст на UserContext в отделен файл:

Реализация на класа 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; }
    }
}

Получихме готово решение за провеждане на оптимизационни тестове с LINQ to SQL през EF за MS SQL Server:

Сега във файла Program.cs въведете следния код:

Файл 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<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();
            }
        }
    }
}

След това нека изпълним нашия проект.

В края на работата на конзолата ще се покаже следното:

Генерирана SQL заявка

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

Това означава, че като цяло LINQ заявката генерира доста добре SQL заявка към СУБД на MS SQL Server.

Сега нека променим условието И на ИЛИ в LINQ заявката:

LINQ заявка

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

Нека стартираме нашето приложение отново.

Изпълнението ще се срине с грешка, свързана с времето за изпълнение на командата, надвишаващо 30 секунди:

Ако погледнете коя заявка е генерирана от LINQ:

, тогава можете да се уверите, че изборът става чрез декартовия продукт на два набора (таблици):

Генерирана SQL заявка

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]

Нека пренапишем LINQ заявката така:

Оптимизирана LINQ заявка

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

След това получаваме следната SQL заявка:

SQL заявка

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]

Уви, в LINQ заявките може да има само едно условие за свързване, следователно е възможно да се направи еквивалентна заявка чрез две заявки за всяко условие, последвано от тяхното обединяване чрез Union за премахване на дубликатите между редовете.
Да, заявките обикновено ще бъдат нееквивалентни, като се има предвид, че могат да бъдат върнати пълни дублирани редове. В реалния живот обаче пълните дублирани линии не са необходими и те се опитват да се отърват от тях.

Сега нека сравним плановете за изпълнение на тези две заявки:

1. за CROSS JOIN средното време за изпълнение е 195 секунди:
   
2. за INNER JOIN-UNION средното време за изпълнение е по-малко от 24 секунди:
   

Както се вижда от резултатите, за две таблици с милиони записи, оптимизираната LINQ заявка е в пъти по-бърза от неоптимизираната.

За варианта с И в условията на LINQ заявка от формата:

LINQ заявка

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 заявка (първа опция)

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 заявка (първа опция)

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

където:

Дефиниране на два масива

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 е дефиниран както следва:

Дефиниция на пара тип

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

По този начин разгледахме някои аспекти при оптимизирането на LINQ заявки към MS SQL Server.

За съжаление дори опитни и водещи .NET разработчици забравят, че е необходимо да разберат какво правят инструкциите, които използват зад кулисите. В противен случай те стават конфигуратори и могат да поставят бомба със закъснител в бъдеще, както при мащабиране на софтуерно решение, така и при незначителни промени във външните условия на околната среда.

Имаше и малък преглед тук.

Източници за теста - самият проект, създаването на таблици в базата данни TEST, както и попълването на тези таблици с данни се намира тук.
Също така в това хранилище в папката Планове има планове за изпълнение на заявки с ИЛИ условия.

Източник: www.habr.com