🥇 Sumir þættir við fínstillingu LINQ fyrirspurna í C#.NET fyrir MS SQL Server

LINQ kom inn í .NET sem öflugt nýtt gagnavinnslutungumál. LINQ yfir í SQL sem hluti af því gerir þér kleift að eiga samskipti nokkuð þægilegt við DBMS með því að nota til dæmis Entity Framework. Hins vegar, með því að nota það nokkuð oft, gleyma forritarar að skoða hvers konar SQL fyrirspurn fyrirspurnaraðilinn, í þínu tilviki Entity Framework, mun búa til.

Við skulum skoða tvö meginatriði með því að nota dæmi.
Til að gera þetta skaltu búa til prófunargagnagrunn í SQL Server og búa til tvær töflur í honum með því að nota eftirfarandi fyrirspurn:

Að búa til töflur

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

Nú skulum við fylla út Ref töfluna með því að keyra eftirfarandi skriftu:

Að fylla Ref borðið

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

Við skulum á sama hátt fylla viðskiptavinatöfluna með því að nota eftirfarandi forskrift:

Fylla út viðskiptavinatöfluna

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

Þannig fengum við tvær töflur, önnur þeirra hefur meira en 1 milljón raðir af gögnum og hin hefur meira en 10 milljónir raðir af gögnum.

Nú í Visual Studio þarftu að búa til prófunarverkefni Visual C# Console App (.NET Framework):

Næst þarftu að bæta við bókasafni fyrir Entity Framework til að hafa samskipti við gagnagrunninn.
Til að bæta því við skaltu hægrismella á verkefnið og velja Manage NuGet Packages úr samhengisvalmyndinni:

Síðan, í NuGet pakkastjórnunarglugganum sem birtist, sláðu inn orðið „Entity Framework“ í leitarglugganum og veldu Entity Framework pakkann og settu hann upp:

Næst, í App.config skránni, eftir að hafa lokað configSections einingunni, þarftu að bæta við eftirfarandi blokk:

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

Í connectionString þarftu að slá inn tengistrenginn.

Nú skulum við búa til 3 viðmót í aðskildum skrám:

Innleiðing á IBaseEntityID viðmótinu

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

Innleiðing á IBaseEntityName viðmótinu

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

Innleiðing á IBaseNameInsertUTCDate viðmótinu

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

Og í sérstakri skrá munum við búa til grunnflokk BaseEntity fyrir tvær einingar okkar, sem mun innihalda algenga reiti:

Innleiðing grunnflokks BaseEntity

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

Næst munum við búa til tvær einingar okkar í aðskildum skrám:

Framkvæmd Ref flokks

using System.ComponentModel.DataAnnotations.Schema;

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

Framkvæmd viðskiptavinaflokks

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

Nú skulum við búa til UserContext samhengi í sérstakri skrá:

Innleiðing á UserContex bekknum

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

Við fengum tilbúna lausn til að framkvæma hagræðingarpróf með LINQ til SQL í gegnum EF fyrir MS SQL Server:

Sláðu nú inn eftirfarandi kóða í Program.cs skrána:

Program.cs skrá

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

Næst skulum við hefja verkefnið okkar.

Í lok verksins mun eftirfarandi birtast á stjórnborðinu:

Mynduð SQL fyrirspurn

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

Það er almennt séð, LINQ fyrirspurnin bjó til SQL fyrirspurn til MS SQL Server DBMS nokkuð vel.

Nú skulum við breyta OG ástandinu í OR í LINQ fyrirspurninni:

LINQ fyrirspurn

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

Og við skulum ræsa forritið okkar aftur.

Framkvæmdin mun hrynja með villu vegna þess að framkvæmdartími skipunar fer yfir 30 sekúndur:

Ef þú skoðar fyrirspurnina sem var búin til af LINQ:

, þá geturðu gengið úr skugga um að valið eigi sér stað í gegnum kartesíska vöru af tveimur settum (töflum):

Mynduð SQL fyrirspurn

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]

Við skulum endurskrifa LINQ fyrirspurnina sem hér segir:

Fínstillt LINQ fyrirspurn

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

Þá fáum við eftirfarandi SQL fyrirspurn:

SQL fyrirspurn

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]

Því miður, í LINQ fyrirspurnum getur aðeins verið eitt tengingarskilyrði, svo hér er hægt að gera jafngilda fyrirspurn með því að nota tvær fyrirspurnir fyrir hvert skilyrði og sameina þær síðan í gegnum Union til að fjarlægja tvítekningar á milli raðanna.
Já, fyrirspurnirnar verða almennt ekki jafngildar, að teknu tilliti til þess að heilar tvíteknar línur gætu skilað sér. Hins vegar, í raunveruleikanum, er ekki þörf á heilum afritum línum og fólk reynir að losna við þær.

Nú skulum við bera saman framkvæmdaráætlanir þessara tveggja fyrirspurna:

fyrir CROSS JOIN er meðalframkvæmdartími 195 sekúndur:
fyrir INNER JOIN-UNION er meðalframkvæmdartími innan við 24 sekúndur:

Eins og þú sérð af niðurstöðunum, fyrir tvær töflur með milljón færslur, er fínstillta LINQ fyrirspurnin margfalt hraðari en sú óbjartsýni.

Fyrir valkostinn með OG í skilyrðunum, LINQ fyrirspurn á formi:

LINQ fyrirspurn

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

Rétt SQL fyrirspurn verður næstum alltaf búin til, sem mun keyra að meðaltali á um það bil 1 sekúndu:

Einnig fyrir LINQ to Objects meðferð í stað fyrirspurnar eins og:

LINQ fyrirspurn (1. valkostur)

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

þú getur notað fyrirspurn eins og:

LINQ fyrirspurn (2. valkostur)

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

þar sem:

Að skilgreina tvö fylki

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

, og Para tegundin er skilgreind sem hér segir:

Para Type Skilgreining

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

Þannig skoðuðum við nokkra þætti í fínstillingu LINQ fyrirspurna á MS SQL Server.

Því miður gleyma jafnvel reyndir og leiðandi .NET forritarar að þeir þurfa að skilja hvað leiðbeiningarnar sem þeir nota gera á bak við tjöldin. Að öðrum kosti verða þeir stillingar og geta komið fyrir tímasprengju í framtíðinni, bæði þegar hugbúnaðarlausnin er stækkuð og með minniháttar breytingum á ytri umhverfisaðstæðum.

Einnig var gerð stutt úttekt hér.

Heimildir fyrir prófið - verkefnið sjálft, gerð taflna í TEST gagnagrunninum, auk þess að fylla þessar töflur með gögnum eru staðsettar hér.
Einnig í þessari geymslu, í Plans möppunni, eru áætlanir um að framkvæma fyrirspurnir með OR-skilyrðum.

Heimild: www.habr.com

Sumir þættir við að fínstilla LINQ fyrirspurnir í C#.NET fyrir MS SQL Server

Bæta við athugasemd Hætta við svar