🥇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:

Wéi Dir aus de Resultater kënnt gesinn, fir zwee Dëscher mat Millioune Rekorder, ass déi optimiséiert LINQ Ufro vill Mol méi séier wéi déi onoptimiséiert.

Fir d'Optioun mat AN an de Konditiounen, eng LINQ Ufro vun der Form:

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

Déi richteg SQL Ufro gëtt bal ëmmer generéiert, déi am Duerchschnëtt an ongeféier 1 Sekonn leeft:

Och fir LINQ to Objects Manipulatiounen amplaz vun enger Ufro wéi:

LINQ Ufro (1st Optioun)

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

Dir kënnt eng Ufro benotzen wéi:

LINQ Ufro (2st Optioun)

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:

Zwee Arrays definéieren

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

, an de Para Typ ass wéi follegt definéiert:

Para Typ Definitioun

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

Also hu mir e puer Aspekter iwwerpréift bei der Optimisatioun vun LINQ Ufroen op MS SQL Server.

Leider vergiessen och erfuerene a führend .NET Entwéckler datt se musse verstoen wat d'Instruktiounen déi se benotzen hannert de Kulisse maachen. Soss gi se Konfigurateuren a kënnen an Zukunft eng Zäitbomm planzen souwuel beim Skaléieren vun der Softwareléisung a mat klengen Ännerungen an externen Ëmweltbedéngungen.

Eng kuerz Iwwerpréiwung gouf och gemaach hei.

Quelle fir den Test - de Projet selwer, d'Schafung vun Dëscher an der TEST Datebank, wéi och dës Tabelle mat Daten ausfëllen 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