Sommige aspekte van LINQ-navraagoptimalisering in C#.NET vir MS SQL Server

LINQ het .NET as 'n kragtige nuwe datamanipulasietaal ingeskryf. LINQ na SQL as deel daarvan laat jou toe om redelik gerieflik met die DBBS te kommunikeer deur byvoorbeeld die Entiteitsraamwerk te gebruik. Deur dit egter gereeld te gebruik, vergeet ontwikkelaars om te kyk na watter soort SQL-navraag die navraagbare verskaffer sal genereer, in jou geval, die Entiteitsraamwerk.

Kom ons kyk na twee hoofpunte met 'n voorbeeld.
Om dit te doen, sal ons in SQL Server 'n databasistoets skep, en daarin sal ons twee tabelle skep deur die volgende navraag te gebruik:

Die skep van 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

Kom ons vul nou die Ref-tabel in deur die volgende skrif uit te voer:

Vul die tabel Verw

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

Kom ons vul die klantetabel op dieselfde manier deur die volgende skrif te gebruik:

Vul die klantetabel in

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

Ons het dus twee tabelle, waarvan een meer as 1 miljoen rye data het, en die ander het meer as 10 miljoen rye data.

Nou in Visual Studio, moet jy 'n toets Visual C# Console App (.NET Framework) projek skep:

Vervolgens moet u 'n biblioteek byvoeg vir die Entiteitsraamwerk om met die databasis te kommunikeer.
Om dit by te voeg, klik met die rechtermuisknop op die projek en kies Bestuur NuGet-pakkette in die kontekskieslys:

Dan, in die NuGet-pakketbestuursvenster wat verskyn, in die soekkassie, voer die woord "Entity Framework" in en kies die Entity Framework-pakket en installeer dit:

Volgende, in die App.config-lêer, nadat die configSections-element gesluit is, voeg die volgende blok by:

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

In connectionString moet jy die verbindingstring invoer.

Kom ons skep nou 3 koppelvlakke in aparte lêers:

1. Implementering van die IBaseEntityID-koppelvlak

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

2. Implementering van die IBaseEntityName-koppelvlak

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

3. Implementering van die IBaseNameInsertUTCDate-koppelvlak

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

En in 'n aparte lêer sal ons 'n BaseEntity-basisklas vir ons twee entiteite skep, wat algemene velde sal insluit:

Implementering van die basisklas BaseEntity

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

Vervolgens, in aparte lêers, sal ons ons twee entiteite skep:

1. Verwys klas implementering

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

2. Implementering van die Kliëntklas

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

Kom ons skep nou 'n UserContext konteks in 'n aparte lêer:

Implementering van die UserContex-klas

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

Ons het 'n klaargemaakte oplossing vir die uitvoer van optimeringstoetse met LINQ na SQL via EF vir MS SQL Server:

Voer nou die volgende kode in die Program.cs-lêer in:

Program.cs lêer

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

Kom ons voer dan ons projek uit.

Aan die einde van die werk sal die volgende op die konsole vertoon word:

Gegenereerde SQL-navraag

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

Dit wil sê, in die algemeen het die LINQ-navraag 'n SQL-navraag na die MS SQL Server DBMS gegenereer.

Kom ons verander nou die EN-voorwaarde na OF in die LINQ-navraag:

LINQ-navraag

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

Kom ons laat ons aansoek weer hardloop.

Die uitvoering sal ineenstort met 'n fout wat verband hou met die opdraguitvoeringstyd wat 30 sekondes oorskry:

As jy kyk watter navraag deur LINQ gegenereer is:

, dan kan jy seker maak dat die seleksie geskied deur die Cartesiese produk van twee stelle (tabelle):

Gegenereerde SQL-navraag

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]

Kom ons herskryf die LINQ-navraag soos volg:

Geoptimaliseerde LINQ-navraag

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

Dan kry ons die volgende SQL-navraag:

SQL-navraag

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]

Helaas, in LINQ-navrae kan daar net een aansluitingsvoorwaarde wees, daarom is dit moontlik om 'n ekwivalente navraag te maak deur twee navrae vir elke toestand, gevolg deur hul vereniging deur Unie om duplikate tussen rye te verwyder.
Ja, die navrae sal oor die algemeen nie-ekwivalent wees, aangesien volledige duplikaatrye teruggestuur kan word. In die werklike lewe is volledige duplikaatlyne egter nie nodig nie en hulle probeer om daarvan ontslae te raak.

Kom ons vergelyk nou die uitvoeringsplanne van hierdie twee navrae:

1. vir CROSS JOIN is die gemiddelde uitvoeringstyd 195 sek.
   
2. vir INNER JOIN-UNION is die gemiddelde uitvoeringstyd minder as 24 sek.
   

Soos uit die resultate gesien kan word, is die geoptimaliseerde LINQ-navraag vir twee tabelle met miljoene rekords baie keer vinniger as die nie-geoptimaliseerde een.

Vir die variant met EN in die voorwaardes van 'n LINQ-navraag van die vorm:

LINQ-navraag

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

byna altyd sal 'n korrekte SQL-navraag gegenereer word, wat gemiddeld vir ongeveer 1 sekonde sal loop:

Ook vir LINQ to Objects-manipulasies in plaas daarvan om die aansig te bevraagteken:

LINQ-navraag (1ste opsie)

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

Jy kan 'n navraag gebruik soos:

LINQ-navraag (2ste opsie)

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

waar:

Definieer twee skikkings

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

, en die Para-tipe word soos volg gedefinieer:

Para tipe definisie

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

Ons het dus 'n paar aspekte oorweeg in die optimalisering van LINQ-navrae na MS SQL Server.

Ongelukkig vergeet selfs ervare en toonaangewende .NET-ontwikkelaars dat dit nodig is om te verstaan ​​wat die instruksies wat hulle gebruik agter die skerms doen. Andersins word hulle konfigureerders en kan hulle in die toekoms 'n tydbom lê, beide wanneer 'n sagteware-oplossing skaal, en met geringe veranderinge in eksterne omgewingstoestande.

Daar was ook 'n klein resensie hier.

Bronne vir die toets - die projek self, die skep van tabelle in die TEST-databasis, sowel as die vul van hierdie tabelle met data is geleë hier.
Ook in hierdie bewaarplek in die Planne-lêergids is planne vir die uitvoering van navrae met OF-toestande.

Bron: will.com