ProHoster > Blog > Amministrazione > Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server

Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server

LINQ è entrato in .NET come un nuovo potente linguaggio per la manipolazione dei dati. LINQ to SQL come parte di questo consente di interagire in modo abbastanza conveniente con i database, ad esempio tramite Entity Framework. Tuttavia, molto spesso, gli sviluppatori, applicandolo, dimenticano di controllare quale specifica query SQL genererà il provider queryable, nel vostro caso — Entity Framework.

Analizziamo due punti principali con un esempio.
Per questo, nel SQL Server, creeremo un database Test, e al suo interno creeremo due tabelle con il seguente comando:

Creazione delle tabelle

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

Ora popoleremo la tabella Ref eseguendo il seguente script:

Popolamento della tabella 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

In modo analogo, popoleremo la tabella Customer attraverso il seguente script:

Popolamento della tabella Customer

USA [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=0) SET @ind_ref=4;
	ELSE IF (@ind=0) SET @ind_ref=5;
	ELSE IF (@ind=0) SET @ind_ref=6;
	ELSE IF (@ind=0) SET @ind_ref=7;
	ELSE IF (@ind=0) SET @ind_ref=8;
	ELSE IF (@ind=0) SET @ind_ref=9;
	ELSE IF (@ind=0) SET @ind_ref=10;
	ELSE IF (@ind=0) SET @ind_ref=11;
	ELSE SET @ind_ref=@ind90000;
	
	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

Così abbiamo ottenuto due tabelle, una delle quali contiene oltre 1 milione di righe di dati, mentre l'altra oltre 10 milioni di righe di dati.

Ora in Visual Studio è necessario creare un progetto di test Visual C# Console App (.NET Framework):

Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server

In seguito, è necessario aggiungere una libreria per Entity Framework per l'interazione con il database.
Per aggiungerla, facciamo clic destro sul progetto e scegliamo nel menu contestuale Gestisci pacchetti NuGet:

Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server

Poi, nella finestra di gestione dei pacchetti NuGet che appare, inseriamo la parola "Entity Framework" nella finestra di ricerca, selezioniamo il pacchetto Entity Framework e lo installiamo:

Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server

Successivamente, nel file App.config, dopo la chiusura dell'elemento configSections, dobbiamo aggiungere il seguente blocco:

Nel connectionString bisogna inserire la stringa di connessione.

Ora creiamo in file separati 3 interfacce:

  1. Implementazione dell'interfaccia IBaseEntityID
    namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

  2. Implementazione dell'interfaccia IBaseEntityName
    namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

  3. Implementazione dell'interfaccia IBaseNameInsertUTCDate
    namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

E in un file separato creiamo la classe base BaseEntity per le nostre due entità, che conterrà i campi comuni:

Implementazione della classe base BaseEntity

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

Dopo creiamo in file separati le nostre due entità:

  1. Implementazione della classe Ref
    using System.ComponentModel.DataAnnotations.Schema;

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

  2. Implementazione della classe 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; }
    }
}

Ora creiamo in un file separato il contesto UserContext:

Implementazione della classe UserContext

using System.Data.Entity;

namespace TestLINQ
{
    public class UserContext : DbContext
    {
        public UserContext()
            : base("DbConnection")
        {
            Database.SetInitializer(null);
        }

        public DbSet Customer { get; set; }
        public DbSet Ref { get; set; }
    }
}

Abbiamo ottenuto una soluzione pronta per condurre test di ottimizzazione con LINQ to SQL attraverso EF per MS SQL Server:

Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server

Ora inseriamo il seguente codice nel file Program.cs:

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

Ora avviamo il nostro progetto.

Alla fine dell'esecuzione verrà stampato sulla console:

La query SQL generata

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

Cioè, in generale, la query LINQ ha generato un buon SQL per il DBMS MS SQL Server.

Ora cambiamo la condizione da E a O nella query LINQ:

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

E di nuovo avviamo la nostra applicazione.

L'esecuzione genererà un errore relativo al superamento del tempo di esecuzione della query di 30 secondi:

Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server

Se guardiamo quale query è stata generata in questo caso da LINQ:

Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server
, si può capire che la selezione avviene tramite il prodotto cartesiano di due insiemi (tabelle):

La query SQL generata

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]

Riscriviamo la query LINQ nel modo seguente:

Query LINQ ottimizzata

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

Allora otterremo la seguente query SQL:

Query SQL

SELEZIONARE 
    [Limit1].[C1] AS [C1], 
    [Limit1].[C2] AS [C2], 
    [Limit1].[C3] AS [C3]
    DA ( SELEZIONARE DISTINTI TOP (1000) 
        [UnionAll1].[C1] AS [C1], 
        [UnionAll1].[Name] AS [C2], 
        [UnionAll1].[Name1] AS [C3]
        DA  (SELEZIONARE 
            1 AS [C1], 
            [Extent1].[Name] AS [Name], 
            [Extent2].[Name] AS [Name1]
            DA  [dbo].[Customer] AS [Extent1]
            INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
        UNIONE TUTTO
            SELEZIONARE 
            1 AS [C1], 
            [Extent3].[Name] AS [Name], 
            [Extent4].[Name] AS [Name1]
            DA  [dbo].[Customer] AS [Extent3]
            INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
    )  AS [Limit1]

Purtroppo, ma nelle query LINQ la condizione di join può essere solo una, quindi è possibile fare una query equivalente tramite due query per ciascuna condizione con successiva unione tramite Union per rimuovere i duplicati tra le righe.
Sì, le query saranno in generale non equivalenti considerando che potrebbero essere restituiti duplicati completi delle righe. Tuttavia, nella vita reale le righe duplicate complete non sono necessarie e si cerca di eliminarle.

Ora confrontiamo i piani di esecuzione di queste due query:

  1. per il CROSS JOIN in media il tempo di esecuzione è di 195 secondi:
    Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server
  2. per l'INNER JOIN-UNION in media il tempo di esecuzione è inferiore a 24 secondi:
    Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server

Come si vede dai risultati, per due tabelle con milioni di record, la query LINQ ottimizzata funziona molte volte più velocemente rispetto a quella non ottimizzata.

Per l'opzione con E nelle condizioni, la query LINQ ha il seguente formato:

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

nella maggior parte dei casi verrà generata una query SQL corretta, che sarà eseguita in media in circa 1 secondo:

Alcuni aspetti dell'ottimizzazione delle query LINQ in C#.NET per MS SQL Server
Inoltre, per le manipolazioni LINQ to Objects al posto della query del tipo:

Query LINQ (1ª opzione)

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

si può utilizzare una query del tipo:

Query LINQ (2ª opzione)

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

dove:

Definizione di due array

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

, e il tipo Para è definito come segue:

Definizione del tipo Para

classe Para
{
        pubblico int Key1, Key2;
        pubblico string Data;
}

Abbiamo quindi esaminato alcuni aspetti dell'ottimizzazione delle query LINQ per MS SQL Server.

Sfortunatamente, anche gli sviluppatori .NET esperti e di punta dimenticano che è necessario comprendere cosa fanno in background le istruzioni che utilizzano. Altrimenti, diventano semplicemente configuratori e possono inserire una bomba ad orologeria in futuro sia durante la scalabilità della soluzione software che per piccole modifiche alle condizioni ambientali.

È stata inoltre effettuata una breve revisione e qui.

I sorgenti per il test - il progetto stesso, la creazione di tabelle nel database TEST e la loro popolazione con dati si trova qui.
Inoltre, in questo repository nella cartella Piani ci sono i piani di esecuzione delle query con condizioni O.

Fonte: habr.com

Acquista hosting affidabile per siti web con protezione DDoS, VPS VDS server 🔥 Acquista hosting affidabile per siti web con protezione DDoS, VPS VDS server | ProHoster