Gen kèk aspè nan optimize demann LINQ nan C#.NET pou MS SQL sèvè

LINQ te antre nan .NET kòm yon pwisan nouvo langaj manipilasyon done. LINQ to SQL kòm yon pati nan li pèmèt ou kominike byen fasil ak yon DBMS lè l sèvi avèk, pou egzanp, Entity Framework. Sepandan, lè l sèvi avèk li byen souvan, devlopè bliye gade ki kalite rechèch SQL founisè a keryable, nan ka w Entity Framework, pral jenere.

Ann gade de pwen prensipal lè l sèvi avèk yon egzanp.
Pou fè sa, kreye yon baz done tès nan SQL sèvè, epi kreye de tab nan li lè l sèvi avèk rechèch sa a:

Kreye tab

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

Koulye a, ann peple tab la Ref pa kouri script sa a:

Ranpli tab la 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

An menm jan an nou ranpli tab Kliyan an lè l sèvi avèk script sa a:

Peple tab Kliyan an

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

Kidonk, nou te resevwa de tab, youn nan ki gen plis pase 1 milyon ranje done, ak lòt la gen plis pase 10 milyon ranje done.

Koulye a, nan Visual Studio ou bezwen kreye yon tès Visual C# Console App (.NET Framework) pwojè:

Apre sa, ou bezwen ajoute yon bibliyotèk pou Entity Framework pou kominike avèk baz done a.
Pou ajoute li, klike sou pwojè a epi chwazi Jere pakè NuGet nan meni kontèks la:

Lè sa a, nan fenèt jesyon pake NuGet ki parèt, antre mo "Entity Framework" nan fenèt rechèch la epi chwazi pake Entity Framework epi enstale li:

Apre sa, nan dosye a App.config, apre yo fin fèmen eleman nan configSections, ou bezwen ajoute blòk sa a:

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

Nan connectionString ou bezwen antre nan fisèl koneksyon an.

Koulye a, ann kreye 3 interfaces nan dosye separe:

1. Aplike koòdone IBaseEntityID la

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

2. Aplikasyon koòdone IBaseEntityName

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

3. Aplikasyon koòdone IBaseNameInsertUTCDate

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

Ak nan yon dosye separe nou pral kreye yon klas de baz BaseEntity pou de antite nou yo, ki pral gen ladan jaden komen:

Aplikasyon klas de baz BaseEntity

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

Apre sa, nou pral kreye de antite nou yo nan dosye separe:

1. Aplikasyon klas Ref

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

2. Aplikasyon klas Kliyan an

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

Koulye a, ann kreye yon kontèks UserContext nan yon dosye separe:

Aplikasyon klas UserContex la

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

Nou te resevwa yon solisyon pare pou fè tès optimize ak LINQ to SQL atravè EF pou MS SQL sèvè:

Koulye a, antre kòd sa a nan dosye Program.cs la:

Program.cs dosye

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

Apre sa, ann lanse pwojè nou an.

Nan fen travay la, sa ki annapre yo pral parèt sou konsole a:

Jenere SQL Rekèt

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

Sa vle di, an jeneral, rechèch LINQ la te pwodwi yon rechèch SQL nan MS SQL Server DBMS byen byen.

Koulye a, ann chanje kondisyon AND an OSWA nan rekèt LINQ la:

Rekèt 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 };

Epi ann lanse aplikasyon nou an ankò.

Ekzekisyon an pral fè aksidan ak yon erè akòz tan an egzekisyon lòd ki depase 30 segonn:

Si w gade rechèch ki te pwodwi pa LINQ la:

, Lè sa a, ou ka asire w ke seleksyon an fèt atravè pwodwi katezyen de seri (tab):

Jenere SQL Rekèt

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]

Ann reekri rechèch LINQ la jan sa a:

Optimize rechèch LINQ

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

Lè sa a, nou jwenn rechèch SQL sa a:

Rekèt 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]

Ay, nan demann LINQ kapab genyen sèlman yon sèl kondisyon rantre, kidonk isit la li posib pou fè yon rechèch ekivalan lè l sèvi avèk de demann pou chak kondisyon ak Lè sa a, konbine yo atravè Inyon pou retire kopi nan mitan ranje yo.
Wi, demann yo pral jeneralman pa ekivalan, lè nou konsidere ke ranje kopi konplè yo ka retounen. Sepandan, nan lavi reyèl, liy kopi konplè yo pa nesesè epi moun eseye debarase m de yo.

Koulye a, ann konpare plan ekzekisyon de demann sa yo:

1. pou CROSS JOIN tan an mwayèn ekzekisyon se 195 segonn:
   
2. pou INNER JOIN-UNION tan an mwayèn ekzekisyon se mwens pase 24 segonn:
   

Kòm ou ka wè nan rezilta yo, pou de tab ki gen plizyè milyon dosye, rekèt LINQ optimize a anpil fwa pi rapid pase youn ki pa optimize a.

Pou opsyon ki gen AND nan kondisyon yo, yon demann LINQ nan fòm lan:

Rekèt 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 };

Rekèt SQL kòrèk la pral prèske toujou pwodwi, ki pral kouri an mwayèn nan apeprè 1 segonn:

Epitou pou manipilasyon LINQ to Objects olye de yon rechèch tankou:

Rekèt LINQ (1ye opsyon)

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

ou ka itilize yon rechèch tankou:

Rekèt LINQ (2ye opsyon)

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

kote:

Defini de etalaj

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

, epi yo defini kalite Para a jan sa a:

Para Kalite Definisyon

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

Kidonk, nou te egzamine kèk aspè nan optimize demann LINQ nan MS SQL sèvè.

Malerezman, menm devlopè .NET ki gen eksperyans ak dirijan yo bliye ke yo bezwen konprann ki sa enstriksyon yo itilize fè dèyè sèn nan. Sinon, yo vin konfigirateur epi yo ka plante yon bonm tan nan tan kap vini an tou de lè dekale solisyon an lojisyèl ak chanjman minè nan kondisyon anviwònman ekstèn.

Yo te fè yon revizyon tou kout isit la.

Sous yo pou tès la - pwojè a tèt li, kreyasyon tab nan baz done TEST la, osi byen ke ranpli tab sa yo ak done yo sitiye. isit la.
Epitou nan depo sa a, nan katab la Plans, gen plan pou egzekite demann ak kondisyon OSWA.

Sous: www.habr.com