ProHoster > Блог > Захиргаа > MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал

MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал

LINQ .NET-д өгөгдөл боловсруулах шинэ хүчирхэг хэл болгон нэвтэрсэн. Үүний нэг хэсэг болох LINQ to SQL нь жишээлбэл, Entity Framework-ийг ашиглан DBMS-тэй маш тохиромжтой харилцах боломжийг олгодог. Гэсэн хэдий ч, үүнийг ихэвчлэн ашигласнаар хөгжүүлэгчид асууж болох үйлчилгээ үзүүлэгч, таны хувьд Entity Framework ямар төрлийн SQL асуулга үүсгэхийг харахаа мартдаг.

Жишээ ашиглан хоёр гол зүйлийг авч үзье.
Үүнийг хийхийн тулд 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) туршилтын төслийг үүсгэх хэрэгтэй:

MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал

Дараа нь та мэдээллийн сантай харилцахын тулд Entity Framework-д зориулж номын сан нэмэх хэрэгтэй.
Үүнийг нэмэхийн тулд төсөл дээр хулганы баруун товчийг дараад контекст цэснээс Manage NuGet багцуудыг сонгоно уу.

MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал

Дараа нь гарч ирэх NuGet багцын удирдлагын цонхонд хайлтын цонхонд "Entity Framework" гэсэн үгийг оруулаад Entity Framework багцыг сонгоод суулгана уу.

MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал

Дараа нь App.config файлд configSections элементийг хаасны дараа та дараах блокийг нэмэх хэрэгтэй.

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

ConnectionString дээр та холболтын мөрийг оруулах хэрэгтэй.

Одоо тусдаа файлд 3 интерфейс үүсгэцгээе:

  1. IBaseEntityID интерфейсийг хэрэгжүүлж байна 
    namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

  2. IBaseEntityName интерфейсийн хэрэгжилт 
    namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

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

Дараа нь бид хоёр аж ахуйн нэгжийг тусдаа файлд үүсгэх болно:

  1. Ref ангийн хэрэгжилт 
    using System.ComponentModel.DataAnnotations.Schema;

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

  2. Хэрэглэгчийн ангиллын хэрэгжилт 
    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; }
    }
}

Бид MS SQL серверт зориулсан EF-ээр дамжуулан LINQ to SQL-тэй оновчлолын тест хийх бэлэн шийдлийг хүлээн авлаа.

MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал

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

Дараа нь төслөө эхлүүлцгээе.

Ажлын төгсгөлд консол дээр дараахь зүйлийг харуулах болно.

Үүсгэсэн 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 асуулга нь MS SQL Server DBMS-д SQL query-г маш сайн үүсгэсэн.

Одоо LINQ асуулгын AND нөхцөлийг OR болгож өөрчилье:

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 секундээс хэтэрсэн тул гүйцэтгэл алдаа гаргана:

MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал

Хэрэв та LINQ-ийн үүсгэсэн асуулгыг харвал:

MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал
, дараа нь сонголт нь хоёр багц (хүснэгт) -ийн декарт үржвэрээр явагдах эсэхийг шалгаж болно:

Үүсгэсэн 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 асуулгад зөвхөн нэг нэгдэх нөхцөл байж болох тул энд нөхцөл бүрийн хоёр асуулга ашиглан ижил төстэй асуулга хийж, дараа нь Union-ээр дамжуулан нэгтгэж мөр хоорондын давхардлыг арилгах боломжтой.
Тийм ээ, бүрэн давхардсан мөрүүдийг буцаах боломжтойг харгалзан асуулга нь ерөнхийдөө адилгүй байх болно. Гэсэн хэдий ч бодит амьдрал дээр бүрэн давхардсан мөрүүд шаардлагагүй бөгөөд хүмүүс үүнийг арилгахыг хичээдэг.

Одоо эдгээр хоёр асуулгын гүйцэтгэлийн төлөвлөгөөг харьцуулж үзье.

  1. CROSS JOIN-ийн гүйцэтгэлийн дундаж хугацаа 195 секунд байна:
    MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал
  2. INNER JOIN-UNION-ийн хувьд гүйцэтгэлийн дундаж хугацаа 24 секундээс бага байна:
    MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал

Үр дүнгээс харахад сая сая бичлэг бүхий хоёр хүснэгтийн хувьд оновчтой 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 секундын дотор ажиллах болно:

MS SQL серверт зориулсан C#.NET дээр LINQ асуулга оновчтой болгох зарим асуудал
Мөн LINQ to 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 };

хаана:

Хоёр массивыг тодорхойлох

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 төрлийг дараах байдлаар тодорхойлно.

Пара төрлийн тодорхойлолт

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

Тиймээс бид MS SQL серверт LINQ асуулга оновчтой болгох зарим талыг судалсан.

Харамсалтай нь, туршлагатай, тэргүүлэгч .NET хөгжүүлэгчид ч гэсэн ашигладаг заавар нь хөшигний ард юу байдгийг ойлгох хэрэгтэй гэдгээ мартдаг. Үгүй бол тэд тохируулагч болж, програм хангамжийн шийдлийг өргөжүүлэх, гадаад орчны нөхцөл байдалд бага зэрэг өөрчлөлт оруулах үед ч гэсэн цагийн бөмбөг суулгаж болно.

Мөн богино хэмжээний тойм хийсэн энд.

Туршилтын эх сурвалжууд - төсөл өөрөө, TEST мэдээллийн санд хүснэгт үүсгэх, мөн эдгээр хүснэгтийг мэдээллээр дүүргэх зэрэгт байрладаг. энд.
Мөн энэ агуулахын Төлөвлөгөөний хавтсанд OR нөхцлөөр асуулга явуулах төлөвлөгөө байдаг.

Эх сурвалж: www.habr.com

сэтгэгдэл нэмэх