🥇ບາງດ້ານຂອງການເພີ່ມປະສິດທິພາບການສອບຖາມ LINQ ໃນ C#.NET ສໍາລັບ MS SQL Server

LINQ ເຂົ້າ .NET ເປັນພາສາການຈັດການຂໍ້ມູນໃໝ່ທີ່ມີປະສິດທິພາບ. LINQ ກັບ SQL ເປັນສ່ວນຫນຶ່ງຂອງມັນຊ່ວຍໃຫ້ທ່ານສາມາດສື່ສານຢ່າງສະດວກກັບ DBMS ໂດຍໃຊ້, ສໍາລັບການຍົກຕົວຢ່າງ, Entity Framework. ຢ່າງໃດກໍຕາມ, ການນໍາໃຊ້ມັນເລື້ອຍໆ, ນັກພັດທະນາລືມເບິ່ງສິ່ງທີ່ປະເພດຂອງ SQL query ຜູ້ໃຫ້ບໍລິການສອບຖາມ, ໃນກໍລະນີຂອງ Entity Framework ຂອງທ່ານຈະສ້າງ.

ໃຫ້ເບິ່ງສອງຈຸດຕົ້ນຕໍໂດຍໃຊ້ຕົວຢ່າງ.
ເພື່ອເຮັດສິ່ງນີ້, ສ້າງຖານຂໍ້ມູນທົດສອບໃນ SQL Server, ແລະສ້າງສອງຕາຕະລາງໃນມັນໂດຍໃຊ້ຄໍາຖາມຕໍ່ໄປນີ້:

ການສ້າງຕາຕະລາງ

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

ຕອນນີ້ໃຫ້ພວກເຮົາສ້າງຕາຕະລາງ Ref ໂດຍແລ່ນສະຄິບຕໍ່ໄປນີ້:

ການຕື່ມຕາຕະລາງ 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

ໃຫ້ຕື່ມຂໍ້ມູນໃສ່ຕາຕະລາງລູກຄ້າເຊັ່ນດຽວກັນໂດຍໃຊ້ສະຄິບຕໍ່ໄປນີ້:

ກຳລັງສ້າງຕາຕະລາງລູກຄ້າ

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

ດັ່ງນັ້ນ, ພວກເຮົາໄດ້ຮັບສອງຕາຕະລາງ, ຫນຶ່ງໃນນັ້ນມີຂໍ້ມູນຫຼາຍກວ່າ 1 ລ້ານແຖວ, ແລະອີກອັນຫນຶ່ງມີຂໍ້ມູນຫຼາຍກວ່າ 10 ລ້ານແຖວ.

ຕອນນີ້ຢູ່ໃນ Visual Studio ທ່ານຕ້ອງສ້າງໂຄງການ Visual C# Console App (.NET Framework) ທົດສອບ:

ຕໍ່ໄປ, ທ່ານຈໍາເປັນຕ້ອງເພີ່ມຫ້ອງສະຫມຸດສໍາລັບ Entity Framework ເພື່ອພົວພັນກັບຖານຂໍ້ມູນ.
ເພື່ອເພີ່ມມັນ, ໃຫ້ຄລິກຂວາໃສ່ໂຄງການແລະເລືອກ Manage NuGet Packages ຈາກເມນູສະພາບການ:

ຫຼັງຈາກນັ້ນ, ໃນປ່ອງຢ້ຽມການຄຸ້ມຄອງຊຸດ NuGet ທີ່ປາກົດ, ໃຫ້ໃສ່ຄໍາວ່າ "Entity Framework" ໃນປ່ອງຢ້ຽມຄົ້ນຫາແລະເລືອກຊຸດ Entity Framework ແລະຕິດຕັ້ງມັນ:

ຕໍ່ໄປ, ໃນໄຟລ໌ App.config, ຫຼັງຈາກປິດອົງປະກອບ configSections, ທ່ານຈໍາເປັນຕ້ອງເພີ່ມບລັອກຕໍ່ໄປນີ້:

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

ໃນ connectionString ທ່ານຈໍາເປັນຕ້ອງໃສ່ສາຍເຊື່ອມຕໍ່.

ຕອນນີ້ໃຫ້ພວກເຮົາສ້າງ 3 ການໂຕ້ຕອບໃນໄຟລ໌ແຍກຕ່າງຫາກ:

ການປະຕິບັດການໂຕ້ຕອບ IBaseEntityID

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

ການປະຕິບັດການໂຕ້ຕອບ IBaseEntityName

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

ການປະຕິບັດການໂຕ້ຕອບ IBaseNameInsertUTCDate

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

ແລະໃນໄຟລ໌ແຍກຕ່າງຫາກພວກເຮົາຈະສ້າງຊັ້ນພື້ນຖານ BaseEntity ສໍາລັບສອງຫນ່ວຍງານຂອງພວກເຮົາ, ເຊິ່ງຈະປະກອບມີພາກສະຫນາມທົ່ວໄປ:

ການປະຕິບັດຂອງຊັ້ນພື້ນຖານ BaseEntity

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

ຕໍ່ໄປ, ພວກເຮົາຈະສ້າງສອງຫນ່ວຍງານຂອງພວກເຮົາໃນໄຟລ໌ແຍກຕ່າງຫາກ:

ການປະຕິບັດຂອງຫ້ອງຮຽນ Ref

using System.ComponentModel.DataAnnotations.Schema;

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

ການປະຕິບັດຫ້ອງຮຽນລູກຄ້າ

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

ຕອນນີ້ໃຫ້ສ້າງບໍລິບົດ UserContext ໃນໄຟລ໌ແຍກຕ່າງຫາກ:

ການປະຕິບັດຫ້ອງຮຽນ 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; }
    }
}

ພວກເຮົາໄດ້ຮັບການແກ້ໄຂທີ່ກຽມພ້ອມສໍາລັບການດໍາເນີນການທົດສອບການເພີ່ມປະສິດທິພາບດ້ວຍ LINQ ກັບ SQL ຜ່ານ EF ສໍາລັບ MS SQL Server:

ຕອນນີ້ໃສ່ລະຫັດຕໍ່ໄປນີ້ໃສ່ໄຟລ໌ Program.cs:

ໄຟລ໌ 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();
            }
        }
    }
}

ຕໍ່ໄປ, ໃຫ້ເປີດໂຄງການຂອງພວກເຮົາ.

ໃນຕອນທ້າຍຂອງການເຮັດວຽກ, ຕໍ່ໄປນີ້ຈະຖືກສະແດງຢູ່ໃນ console:

ສ້າງ 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])

ນັ້ນແມ່ນ, ໂດຍທົ່ວໄປແລ້ວ, ການສອບຖາມ LINQ ໄດ້ສ້າງແບບສອບຖາມ SQL ກັບ MS SQL Server DBMS ຂ້ອນຂ້າງດີ.

ຕອນນີ້ໃຫ້ປ່ຽນເງື່ອນໄຂ AND ເປັນ OR ໃນການສອບຖາມ LINQ:

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

ແລະໃຫ້ພວກເຮົາເປີດຄໍາຮ້ອງສະຫມັກຂອງພວກເຮົາອີກເທື່ອຫນຶ່ງ.

ການປະຕິບັດຈະຂັດກັບຄວາມຜິດພາດເນື່ອງຈາກທີ່ໃຊ້ເວລາປະຕິບັດຄໍາສັ່ງເກີນ 30 ວິນາທີ:

ຖ້າທ່ານເບິ່ງແບບສອບຖາມທີ່ສ້າງຂຶ້ນໂດຍ LINQ:

, ຫຼັງຈາກນັ້ນທ່ານສາມາດໃຫ້ແນ່ໃຈວ່າການຄັດເລືອກເກີດຂຶ້ນໂດຍຜ່ານຜະລິດຕະພັນ Cartesian ຂອງສອງຊຸດ (ຕາຕະລາງ):

ສ້າງ 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]

ໃຫ້ພວກເຮົາຂຽນຄືນຄໍາຖາມ LINQ ດັ່ງຕໍ່ໄປນີ້:

ການສອບຖາມ 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 });

ຫຼັງຈາກນັ້ນ, ພວກເຮົາໄດ້ຮັບການສອບຖາມ SQL ຕໍ່ໄປນີ້:

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]

ອະນິຈາ, ໃນການສອບຖາມ LINQ ມີພຽງແຕ່ເງື່ອນໄຂການເຂົ້າຮ່ວມເທົ່ານັ້ນ, ດັ່ງນັ້ນໃນທີ່ນີ້ມັນເປັນໄປໄດ້ທີ່ຈະເຮັດການສອບຖາມທີ່ທຽບເທົ່າໂດຍໃຊ້ສອງແບບສອບຖາມສໍາລັບແຕ່ລະເງື່ອນໄຂແລະຫຼັງຈາກນັ້ນສົມທົບພວກມັນຜ່ານສະຫະພັນເພື່ອເອົາການຊ້ໍາກັນລະຫວ່າງແຖວ.
ແມ່ນແລ້ວ, ໂດຍທົ່ວໄປແລ້ວການສອບຖາມຈະບໍ່ທຽບເທົ່າ, ໂດຍຄໍານຶງເຖິງແຖວທີ່ຊ້ໍາກັນທີ່ສົມບູນອາດຈະຖືກສົ່ງຄືນ. ຢ່າງໃດກໍ່ຕາມ, ໃນຊີວິດຈິງ, ເສັ້ນຊ້ໍາຊ້ອນທີ່ສົມບູນແມ່ນບໍ່ຈໍາເປັນແລະປະຊາຊົນພະຍາຍາມກໍາຈັດພວກມັນ.

ຕອນນີ້ໃຫ້ປຽບທຽບແຜນການປະຕິບັດຂອງສອງຄໍາຖາມເຫຼົ່ານີ້:

ສໍາລັບ CROSS JOIN ເວລາປະຕິບັດໂດຍສະເລ່ຍແມ່ນ 195 ວິນາທີ:
ສໍາລັບການເຂົ້າຮ່ວມພາຍໃນສະຫະພາບທີ່ໃຊ້ເວລາປະຕິບັດໂດຍສະເລ່ຍແມ່ນຫນ້ອຍກ່ວາ 24 ວິນາທີ:

ດັ່ງທີ່ທ່ານສາມາດເຫັນໄດ້ຈາກຜົນໄດ້ຮັບ, ສໍາລັບສອງຕາຕະລາງທີ່ມີບັນທຶກຫຼາຍລ້ານ, ການສອບຖາມ LINQ ທີ່ຖືກປັບປຸງແມ່ນໄວກວ່າຫຼາຍເທົ່າທີ່ບໍ່ໄດ້ຮັບການປັບປຸງ.

ສໍາລັບທາງເລືອກທີ່ມີ AND ໃນເງື່ອນໄຂ, ຄໍາຖາມ LINQ ຂອງແບບຟອມ:

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

ການສອບຖາມ SQL ທີ່ຖືກຕ້ອງເກືອບຈະຖືກສ້າງຂຶ້ນຕະຫຼອດເວລາ, ເຊິ່ງຈະດໍາເນີນການໂດຍສະເລ່ຍໃນປະມານ 1 ວິນາທີ:

ນອກຈາກນີ້ສໍາລັບການຫມູນໃຊ້ LINQ ກັບ Objects ແທນການສອບຖາມເຊັ່ນ:

ການສອບຖາມ LINQ (ທາງເລືອກທີ 1)

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

ທ່ານສາມາດນໍາໃຊ້ຄໍາຖາມເຊັ່ນ:

ການສອບຖາມ LINQ (ທາງເລືອກທີ 2)

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

ບ່ອນທີ່:

ການກໍານົດສອງ 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" } };

, ແລະປະເພດ Para ແມ່ນກໍານົດດັ່ງຕໍ່ໄປນີ້:

ຄໍານິຍາມປະເພດ Para

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

ດັ່ງນັ້ນ, ພວກເຮົາໄດ້ກວດເບິ່ງບາງດ້ານໃນການເພີ່ມປະສິດທິພາບການສອບຖາມ LINQ ກັບ MS SQL Server.

ແຕ່ຫນ້າເສຍດາຍ, ເຖິງແມ່ນວ່າຜູ້ພັດທະນາ .NET ທີ່ມີປະສົບການແລະຊັ້ນນໍາກໍ່ລືມວ່າພວກເຂົາຈໍາເປັນຕ້ອງເຂົ້າໃຈສິ່ງທີ່ຄໍາແນະນໍາທີ່ພວກເຂົາໃຊ້ເຮັດຢູ່ເບື້ອງຫຼັງ. ຖ້າບໍ່ດັ່ງນັ້ນ, ພວກມັນຈະກາຍເປັນຕົວຕັ້ງຕົວຕີແລະສາມາດປູກລູກລະເບີດເວລາໃນອະນາຄົດທັງໃນເວລາທີ່ປັບຂະຫນາດການແກ້ໄຂຊອບແວແລະມີການປ່ຽນແປງເລັກນ້ອຍໃນສະພາບແວດລ້ອມພາຍນອກ.

ການທົບທວນສັ້ນຍັງໄດ້ດໍາເນີນ ທີ່ນີ້.

ແຫຼ່ງຂໍ້ມູນສໍາລັບການທົດສອບ - ໂຄງການຕົວມັນເອງ, ການສ້າງຕາຕະລາງໃນຖານຂໍ້ມູນ TEST, ເຊັ່ນດຽວກັນກັບການຕື່ມຂໍ້ມູນຕາຕະລາງເຫຼົ່ານີ້ຢູ່. ທີ່ນີ້.
ນອກຈາກນັ້ນ, ໃນບ່ອນເກັບມ້ຽນນີ້, ໃນໂຟນເດີ Plans, ມີແຜນການປະຕິບັດການສອບຖາມທີ່ມີເງື່ອນໄຂ OR.

ແຫຼ່ງຂໍ້ມູນ: www.habr.com

ບາງດ້ານຂອງການເພີ່ມປະສິດທິພາບການສອບຖາມ LINQ ໃນ C#.NET ສໍາລັບ MS SQL Server

ເພີ່ມຄວາມຄິດເຫັນ Отменитьответ