Qee yam ntawm optimizing LINQ cov lus nug hauv C#.NET rau MS SQL Server

LINQ nkag mus rau .NET raws li cov ntaub ntawv muaj zog tshiab tswj cov lus. LINQ rau SQL yog ib feem ntawm nws tso cai rau koj sib txuas lus yooj yim heev nrog DBMS siv, piv txwv li, Qhov Chaw Ua Haujlwm. Txawm li cas los xij, siv nws ntau zaus, cov neeg tsim khoom tsis nco qab saib dab tsi ntawm SQL query tus neeg muab kev pabcuam queryable, hauv koj rooj plaub Entity Framework, yuav tsim.

Cia peb saib ob lub ntsiab lus tseem ceeb uas siv ib qho piv txwv.
Txhawm rau ua qhov no, tsim qhov Test database hauv SQL Server, thiab tsim ob lub rooj hauv nws siv cov lus nug hauv qab no:

Tsim cov rooj

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

Tam sim no cia peb populate lub rooj Ref los ntawm kev khiav cov ntawv hauv qab no:

Sau lub rooj 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

Cia peb zoo ib yam li cov neeg siv khoom siv cov ntawv hauv qab no:

Populating cov neeg siv khoom

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

Yog li, peb tau txais ob lub rooj, ib qho muaj ntau tshaj 1 lab kab ntawm cov ntaub ntawv, thiab lwm yam muaj ntau tshaj 10 lab kab ntawm cov ntaub ntawv.

Tam sim no hauv Visual Studio koj yuav tsum tsim qhov kev sim Visual C# Console App (.NET Framework) project:

Tom ntej no, koj yuav tsum tau ntxiv ib lub tsev qiv ntawv rau Cov Txheej Txheem Txheej Txheem kom cuam tshuam nrog cov ntaub ntawv.
Txhawm rau ntxiv nws, nyem rau ntawm qhov project thiab xaiv Tswj NuGet Packages los ntawm cov ntawv qhia zaub mov:

Tom qab ntawd, hauv NuGet pob tswj qhov rais uas tshwm sim, nkag mus rau lo lus "Entity Framework" hauv qhov rai tshawb nrhiav thiab xaiv lub pob Entity Framework thiab nruab nws:

Tom ntej no, hauv App.config cov ntaub ntawv, tom qab kaw lub configSections caij, koj yuav tsum ntxiv cov block hauv qab no:

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

Hauv connectionString koj yuav tsum nkag mus rau txoj hlua txuas.

Tam sim no cia peb tsim 3 interfaces hauv cov ntaub ntawv cais:

1. Ua raws li IBaseEntityID interface

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

2. Kev nqis tes ua ntawm IBaseEntityName interface

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

3. Kev ua tiav ntawm IBaseNameInsertUTCDate interface

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

Thiab nyob rau hauv ib daim ntawv cais peb yuav tsim ib lub hauv paus chav kawm BaseEntity rau peb ob lub koom haum, uas yuav suav nrog ntau cov teb:

Kev nqis tes ua ntawm chav kawm BaseEntity

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

Tom ntej no, peb yuav tsim peb ob qhov chaw hauv cov ntaub ntawv cais:

1. Kev ua raws li chav kawm Ref

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

2. Kev ua raws li cov neeg siv khoom

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

Tam sim no cia peb tsim UserContext cov ntsiab lus hauv cov ntaub ntawv cais:

Kev ua tiav ntawm chav kawm 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; }
    }
}

Peb tau txais cov kev daws teeb meem rau kev ua qhov kev ntsuam xyuas zoo nrog LINQ rau SQL ntawm EF rau MS SQL Server:

Tam sim no sau cov cai hauv qab no rau hauv Program.cs cov ntaub ntawv:

Program.cs cov ntaub ntawv

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

Tom ntej no, cia peb pib peb qhov project.

Thaum kawg ntawm kev ua haujlwm, cov hauv qab no yuav tshwm sim ntawm lub console:

Tsim SQL Query

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

Ntawd yog, feem ntau, cov lus nug LINQ tau tsim cov lus nug SQL rau MS SQL Server DBMS zoo heev.

Tam sim no cia peb hloov AND mob rau LOSSIS hauv cov lus nug LINQ:

LINQ lus nug

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

Thiab cia peb tso peb daim ntawv thov dua.

Qhov kev ua tiav yuav poob nrog qhov yuam kev vim lub sijhawm ua tiav ntau dua 30 vib nas this:

Yog tias koj saib cov lus nug uas tau tsim los ntawm LINQ:

, ces koj tuaj yeem paub tseeb tias qhov kev xaiv tshwm sim los ntawm Cartesian cov khoom ntawm ob pawg (cov rooj):

Tsim SQL Query

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]

Cia peb rov sau cov lus nug LINQ raws li hauv qab no:

Optimized LINQ lus nug

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

Tom qab ntawd peb tau txais cov lus nug SQL nram qab no:

SQL lus nug

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, hauv LINQ cov lus nug tsuas yog tuaj yeem muaj ib qho kev koom nrog, yog li ntawm no nws muaj peev xwm ua qhov sib npaug ntawm cov lus nug uas siv ob cov lus nug rau txhua tus mob thiab tom qab ntawd muab lawv los ntawm Union kom tshem tawm cov sib npaug ntawm cov kab.
Yog lawm, cov lus nug feem ntau yuav tsis sib npaug, suav nrog tias ua tiav cov kab sib tw yuav raug xa rov qab. Txawm li cas los xij, hauv lub neej tiag tiag, cov kab sib tw ua tiav tsis xav tau thiab tib neeg sim tshem tawm lawv.

Tam sim no cia peb sib piv cov phiaj xwm ua tiav ntawm ob cov lus nug no:

1. rau CROSS JOIN lub sijhawm nruab nrab yog 195 vib nas this:
   
2. rau INNER JOIN-UNION lub sijhawm ua haujlwm nruab nrab yog tsawg dua 24 vib nas this:
   

Raws li koj tuaj yeem pom los ntawm cov txiaj ntsig, rau ob lub rooj nrog ntau lab cov ntaub ntawv, cov lus nug LINQ optimized yog ntau zaus sai dua li qhov tsis zoo.

Rau qhov kev xaiv nrog THIAB nyob rau hauv cov xwm txheej, cov lus nug LINQ ntawm daim ntawv:

LINQ lus nug

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

Qhov tseeb SQL lus nug yuav luag ib txwm raug tsim, uas yuav khiav ntawm qhov nruab nrab hauv li 1 thib ob:

Tsis tas li ntawd rau LINQ rau Objects manipulations es tsis txhob nug xws li:

LINQ query (1st xaiv)

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

koj tuaj yeem siv cov lus nug xws li:

LINQ query (2st xaiv)

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

qhov twg:

Defining ob arrays

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

, thiab hom Para yog txhais raws li hauv qab no:

Para hom txhais

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

Yog li, peb tau tshuaj xyuas qee yam hauv kev ua kom zoo LINQ cov lus nug rau MS SQL Server.

Hmoov tsis zoo, txawm tias muaj kev paub dhau los thiab ua .NET cov tsim tawm tsis nco qab tias lawv yuav tsum nkag siab tias cov lus qhia lawv siv ua tom qab qhov xwm txheej. Txwv tsis pub, lawv dhau los ua tus tsim thiab tuaj yeem cog lub sijhawm foob pob rau yav tom ntej ob qho tib si thaum ntsuas qhov kev daws teeb meem software thiab nrog cov kev hloov me me hauv ib puag ncig sab nraud.

Kev tshuaj xyuas luv luv kuj tau ua tiav no.

Cov peev txheej rau kev xeem - qhov project nws tus kheej, tsim cov ntxhuav hauv TEST database, nrog rau sau cov ntxhuav nrog cov ntaub ntawv nyob hauv no.
Tsis tas li ntawd nyob rau hauv qhov chaw cia khoom no, nyob rau hauv Daim Ntawv Qhia Folder, muaj cov phiaj xwm rau kev ua cov lus nug nrog LOSSIS cov xwm txheej.

Tau qhov twg los: www.hab.com