🥇Некои аспекти на оптимизирање на барањата за LINQ во C#.NET за MS SQL Server

LINQ влезе во .NET како нов моќен јазик за манипулација со податоци. LINQ на SQL како дел од него ви овозможува да комуницирате прилично удобно со DBMS користејќи, на пример, Entity Framework. Меѓутоа, користејќи го доста често, програмерите забораваат да погледнат каков вид на SQL барање ќе генерира давателот на барање, во вашиот случај Entity Framework.

Ајде да погледнеме две главни точки користејќи пример.
За да го направите ова, креирајте тест база на податоци во SQL Server и креирајте две табели во неа користејќи го следново барање:

Креирање табели

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 со извршување на следната скрипта:

Пополнување на табелата 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

Ајде на сличен начин да ја пополниме табелата за клиенти користејќи ја следната скрипта:

Пополнување на табелата за клиенти

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# Console (.NET Framework):

Следно, треба да додадете библиотека за Entity Framework за интеракција со базата на податоци.
За да го додадете, кликнете со десното копче на проектот и изберете Manage NuGet Packages од контекстното мени:

Потоа, во прозорецот за управување со пакети 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 интерфејси во посебни датотеки:

Имплементирање на интерфејсот IBaseEntityID

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

Имплементација на интерфејсот IBaseEntityName

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

Имплементација на интерфејсот 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; }
    }
}

Следно, ќе ги создадеме нашите два ентитета во посебни датотеки:

Имплементација на класата Ref

using System.ComponentModel.DataAnnotations.Schema;

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

Имплементација на класата 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; }
    }
}

Сега ајде да создадеме контекст 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 DBMS.

Сега да го смениме условот И во ИЛИ во барањето 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 може да има само еден услов за приклучување, така што овде е можно да се направи еквивалентно барање користејќи две барања за секој услов и потоа да се комбинираат преку Унијата за да се отстранат дупликатите меѓу редовите.
Да, прашањата генерално нема да бидат еквивалентни, имајќи предвид дека може да се вратат целосните дупликати редови. Меѓутоа, во реалниот живот, не се потребни целосни дупликат линии и луѓето се обидуваат да се ослободат од нив.

Сега да ги споредиме плановите за извршување на овие две прашања:

за CROSS JOIN просечното време на извршување е 195 секунди:
за ВНАТРЕШЕН ПРИКЛУЧУВАЊЕ-УНИЈА просечното време на извршување е помало од 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 (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 };

каде што:

Дефинирање на две низи

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 забораваат дека треба да разберат што прават инструкциите што ги користат зад сцената. Во спротивно, тие стануваат конфигуратори и можат да постават темпирана бомба во иднина и при скалирање на софтверското решение и со мали промени во надворешните услови на животната средина.

Беше направен и краток преглед тука.

Изворите за тестот - самиот проект, креирањето табели во базата на податоци ТЕСТ, како и пополнувањето на овие табели со податоци се наоѓаат тука.
Исто така во ова складиште, во папката Планови, има планови за извршување на барања со услови ИЛИ.

Извор: www.habr.com

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

Додадете коментар Откажи одговор