Xi aspetti tal-ottimizzazzjoni tal-mistoqsijiet LINQ f'C#.NET għal MS SQL Server

LINQ daħal .NET bħala lingwa ġdida qawwija ta' manipulazzjoni tad-dejta. LINQ to SQL bħala parti minnu jippermettilek tikkomunika b'mod pjuttost konvenjenti ma' DBMS billi tuża, pereżempju, Entity Framework. Madankollu, meta tużah spiss, l-iżviluppaturi jinsew li jħarsu lejn x'tip ta' mistoqsija SQL se jiġġenera l-fornitur li jista' jiġi mistoqsija, fil-każ tiegħek Entity Framework.

Ejja nħarsu lejn żewġ punti ewlenin billi tuża eżempju.
Biex tagħmel dan, oħloq database tat-Test f'SQL Server, u oħloq żewġ tabelli fiha billi tuża l-mistoqsija li ġejja:

Ħolqien ta' tabelli

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

Issa ejja timla t-tabella Ref billi tħaddem l-iskrittura li ġejja:

Imla t-tabella ta' 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

Ejja bl-istess mod imla t-tabella tal-Klijent billi tuża l-iskrittura li ġejja:

Il-popolazzjoni tat-tabella tal-Klijent

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

Għalhekk, irċevejna żewġ tabelli, li waħda minnhom għandha aktar minn 1 miljun ringiela ta 'dejta, u l-oħra għandha aktar minn 10 miljun ringiela ta' dejta.

Issa fil-Visual Studio trid toħloq proġett tat-test Visual C# Console App (.NET Framework):

Sussegwentement, trid iżżid librerija għall-Qafas tal-Entità biex jinteraġixxi mad-database.
Biex iżżidha, ikklikkja bil-lemin fuq il-proġett u agħżel Immaniġġja l-Pakketti NuGet mill-menu tal-kuntest:

Imbagħad, fit-tieqa tal-ġestjoni tal-pakketti NuGet li tidher, daħħal il-kelma "Qafas tal-Entità" fit-tieqa tat-tfittxija u agħżel il-pakkett tal-Qafas tal-Entitajiet u installah:

Sussegwentement, fil-fajl App.config, wara li tagħlaq l-element configSections, trid iżżid il-blokk li ġej:

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

F'connectionString trid iddaħħal is-sekwenza tal-konnessjoni.

Issa ejja noħolqu 3 interfaces f'fajls separati:

1. Timplimenta l-interface IBaseEntityID

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

2. Implimentazzjoni tal-interface IBaseEntityName

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

3. Implimentazzjoni tal-interface IBaseNameInsertUTCDate

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

U f'fajl separat se noħolqu BaseEntity tal-klassi bażi għaż-żewġ entitajiet tagħna, li se tinkludi oqsma komuni:

Implimentazzjoni tal-klassi bażi BaseEntity

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

Sussegwentement, se noħolqu ż-żewġ entitajiet tagħna f'fajls separati:

1. Implimentazzjoni tal-klassi Ref

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

2. Implimentazzjoni tal-klassi tal-Klijent

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

Issa ejja noħolqu kuntest UserContext f'fajl separat:

Implimentazzjoni tal-klassi UserContex

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

Irċevejna soluzzjoni lesta għat-twettiq ta' testijiet ta' ottimizzazzjoni b'LINQ to SQL permezz ta' EF għal MS SQL Server:

Issa daħħal il-kodiċi li ġej fil-fajl Program.cs:

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

Sussegwentement, ejja nniedu l-proġett tagħna.

Fi tmiem ix-xogħol, dan li ġej se jintwera fuq il-console:

Mistoqsija SQL Ġenerata

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

Jiġifieri, b'mod ġenerali, il-mistoqsija LINQ ġġenerat mistoqsija SQL lill-DBMS MS SQL Server pjuttost tajjeb.

Issa ejja nbiddlu l-kundizzjoni AND għal JEW fil-mistoqsija LINQ:

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

U ejja nniedu l-applikazzjoni tagħna mill-ġdid.

L-eżekuzzjoni tiġġarraf bi żball minħabba l-ħin tal-eżekuzzjoni tal-kmand li jaqbeż it-30 sekonda:

Jekk tħares lejn il-mistoqsija li ġiet iġġenerata minn LINQ:

, allura tista 'tagħmel ċert li l-għażla sseħħ permezz tal-prodott Kartesjan ta' żewġ settijiet (tabelli):

Mistoqsija SQL Ġenerata

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]

Ejja nikteb mill-ġdid il-mistoqsija LINQ kif ġej:

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

Imbagħad irridu nġibu l-mistoqsija SQL li ġejja:

Mistoqsija SQL

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]

Alas, fil-mistoqsijiet LINQ jista 'jkun hemm biss kundizzjoni ta' tingħaqad, għalhekk hawnhekk huwa possibbli li ssir mistoqsija ekwivalenti billi tuża żewġ mistoqsijiet għal kull kundizzjoni u mbagħad tgħaqqadhom permezz ta 'Union biex tneħħi d-duplikati fost ir-ringieli.
Iva, il-mistoqsijiet ġeneralment ikunu mhux ekwivalenti, b'kont meħud li ringieli duplikati sħaħ jistgħu jintbagħtu lura. Madankollu, fil-ħajja reali, linji duplikati kompluti mhumiex meħtieġa u n-nies jippruvaw jeħilsu minnhom.

Issa ejja nqabblu l-pjanijiet ta 'eżekuzzjoni ta' dawn iż-żewġ mistoqsijiet:

1. għal CROSS JOIN il-ħin medju ta' eżekuzzjoni huwa ta' 195 sekonda:
   
2. għal INNER JOIN-UNION il-ħin medju ta' eżekuzzjoni huwa inqas minn 24 sekonda:
   

Kif tistgħu taraw mir-riżultati, għal żewġ tabelli b'miljuni ta' rekords, il-mistoqsija LINQ ottimizzata hija ħafna drabi aktar mgħaġġla minn dik mhux ottimizzata.

Għall-għażla b'AND fil-kundizzjonijiet, mistoqsija LINQ tal-forma:

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

Il-mistoqsija SQL korretta kważi dejjem tiġi ġġenerata, li se taħdem bħala medja f'madwar sekonda:

Ukoll għal manipulazzjonijiet ta' LINQ to Objects minflok mistoqsija bħal:

Mistoqsija LINQ (l-ewwel għażla)

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

tista' tuża mistoqsija bħal:

Mistoqsija LINQ (l-ewwel għażla)

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

fejn:

Jiddefinixxu żewġ matriċi

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

, u t-tip Para huwa definit kif ġej:

Definizzjoni tat-Tip Para

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

Għalhekk, eżaminajna xi aspetti fl-ottimizzazzjoni tal-mistoqsijiet LINQ għal MS SQL Server.

Sfortunatament, anke żviluppaturi .NET b'esperjenza u ewlenin jinsew li jridu jifhmu x'jagħmlu l-istruzzjonijiet li jużaw wara l-kwinti. Inkella, isiru konfiguraturi u jistgħu jħawlu bomba tal-ħin fil-futur kemm meta jkabbru s-soluzzjoni tas-softwer kif ukoll b'bidliet żgħar fil-kundizzjonijiet ambjentali esterni.

Saret ukoll reviżjoni qasira hawn.

Is-sorsi għat-test - il-proġett innifsu, il-ħolqien ta 'tabelli fid-database TEST, kif ukoll il-mili ta' dawn it-tabelli bid-dejta jinsabu hawn.
F'dan ir-repożitorju wkoll, fil-folder tal-Pjanijiet, hemm pjanijiet għall-eżekuzzjoni ta 'mistoqsijiet b'kundizzjonijiet OR.

Sors: www.habr.com