🥇E puer Aspekter vun der Optimisatioun vun LINQ Ufroen am C#.NET fir MS SQL Server

LINQ huet .NET als eng mächteg nei Datemanipulatiounssprooch aginn. LINQ zu SQL als Deel dovun erlaabt Iech ganz bequem mat engem DBMS ze kommunizéieren andeems Dir zum Beispill Entity Framework benotzt. Wéi och ëmmer, wann Dir et zimmlech dacks benotzt, vergiessen d'Entwéckler ze kucken wéi eng SQL-Ufro de queryable Provider, an Ärem Fall Entity Framework, generéiert.

Loosst eis zwee Haaptpunkte mat engem Beispill kucken.
Fir dëst ze maachen, erstellt eng Testdatenbank am SQL Server, a erstellt zwee Dëscher dran mat der folgender Ufro:

Schafen Dëscher

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

Loosst eis elo d'Ref Tabelle populéieren andeems Dir de folgende Skript ausféiert:

Fëllt de Ref Dësch

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

Loosst eis ähnlech de Clientstabell ausfëllen mat dem folgenden Skript:

Populatioun vum Client Dësch

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

Also hu mir zwou Dëscher kritt, vun deenen eng méi wéi 1 Millioun Zeile vun Daten huet, an déi aner méi wéi 10 Millioune Reihen vun Daten.

Elo am Visual Studio musst Dir en Test Visual C# Console App (.NET Framework) Projet erstellen:

Als nächst musst Dir eng Bibliothéik fir den Entity Framework addéieren fir mat der Datebank ze interagéieren.
Fir et derbäizefügen, klickt riets op de Projet a wielt Manage NuGet Packages aus dem Kontextmenü:

Dann, an der NuGet Package Management Fënster déi erschéngt, gitt d'Wuert "Entity Framework" an der Sichfenster a wielt den Entity Framework Package an installéiert et:

Als nächst, an der App.config Datei, nodeems Dir de ConfigSections Element zougemaach hutt, musst Dir de folgende Block derbäi:

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

An connectionString musst Dir d'Verbindungsstring aginn.

Loosst eis elo 3 Interfaces a getrennten Dateien erstellen:

Ëmsetzung vun der IBaseEntityID Interface

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

Ëmsetzung vun der IBaseEntityName Interface

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

Ëmsetzung vun der IBaseNameInsertUTCDate Interface

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

An an enger separater Datei erstelle mir eng Basisklass BaseEntity fir eis zwou Entitéiten, déi gemeinsam Felder enthalen:

Ëmsetzung vun der Basis Klass BaseEntity

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

Als nächst wäerte mir eis zwou Entitéiten an getrennten Dateien erstellen:

Ëmsetzung vun der Klass Ref

using System.ComponentModel.DataAnnotations.Schema;

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

Ëmsetzung vun der Client Klass

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

Loosst eis elo e UserContext Kontext an enger separater Datei erstellen:

Ëmsetzung vun der UserContex Klass

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

Mir kruten eng fäerdeg Léisung fir Optimisatiounstester mat LINQ op SQL iwwer EF fir MS SQL Server ze maachen:

Gitt elo de folgende Code an d'Programm.cs Datei:

Programm.cs Datei

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

Als nächst loosse mer eise Projet starten.

Um Enn vun der Aarbecht gëtt déi folgend op der Konsol ugewisen:

Generéiert 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])

Dat ass, allgemeng, d'LINQ Ufro generéiert eng SQL Ufro un de MS SQL Server DBMS ganz gutt.

Loosst eis elo den AND Conditioun op ODER an der LINQ Ufro änneren:

LINQ Ufro

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

A loosst eis eis Applikatioun erëm starten.

D'Ausféierung crasht mat engem Feeler wéinst der Kommando Ausféierungszäit iwwer 30 Sekonnen:

Wann Dir d'Ufro kuckt, déi vum LINQ generéiert gouf:

, da kënnt Dir sécher sinn datt d'Auswiel duerch de Cartesian Produkt vun zwee Sätz (Tabellen) geschitt:

Generéiert 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]

Loosst eis d'LINQ Ufro wéi follegt nei schreiwen:

Optimiséiert LINQ Ufro

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

Da kréie mir déi folgend SQL Ufro:

SQL Ufro

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]

Och, an LINQ Ufroen kann et nëmmen eng Bäiträg Konditioun sinn, also hei ass et méiglech eng gläichwäerteg Ufro ze maachen mat zwou Ufroe fir all Bedingung an dann duerch d'Union ze kombinéieren fir Duplikate tëscht de Reihen ze läschen.
Jo, d'Ufroe wäerten allgemeng net gläichwäerteg sinn, berécksiichtegt datt komplett duplizéiert Reihen zréckginn. Wéi och ëmmer, am richtege Liewen sinn komplett duplizéiert Linnen net gebraucht a Leit probéieren se lass ze ginn.

Loosst eis elo d'Ausféierungspläng vun dësen zwou Ufroen vergläichen:

fir CROSS JOIN ass déi duerchschnëttlech Ausféierungszäit 195 Sekonnen:
fir INNER JOIN-UNION ass déi duerchschnëttlech Ausféierungszäit manner wéi 24 Sekonnen:

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

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

LINQ Ufro

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

wou:

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

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-разработчики забывают о том, что необходимо понимать что делают за кадром те инструкции, которые они используют. Иначе они становятся конфигураторами и могут заложить бомбу замедленного действия в будущем как при масштабировании программного решения, так и при незначительных изменениях внешних условий среды.

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

Исходники для теста-сам проект, создание таблиц в базе данных TEST, а также наполнение данными этих таблиц находится hei.
Och an dësem Repository, am Pläng Dossier, ginn et Pläng fir Ufroe mat ODER Bedéngungen auszeféieren.

Source: will.com

E puer Aspekter vun der Optimisatioun vun LINQ Ufroen am C#.NET fir MS SQL Server

Setzt e Commentaire annuléieren reply