🥇MS SQL सर्वर के लिए C#.NET में LINQ क्वेरीज़ को अनुकूलित करने के कुछ पहलू

LINQ ने एक शक्तिशाली नई डेटा हेरफेर भाषा के रूप में .NET में प्रवेश किया। इसके भाग के रूप में LINQ से SQL आपको उदाहरण के लिए, एंटिटी फ्रेमवर्क का उपयोग करके DBMS के साथ काफी आसानी से संचार करने की अनुमति देता है। हालाँकि, अक्सर इसका उपयोग करते हुए, डेवलपर्स यह देखना भूल जाते हैं कि क्वेरी करने योग्य प्रदाता, आपके मामले में एंटिटी फ्रेमवर्क, किस प्रकार की SQL क्वेरी उत्पन्न करेगा।

आइए एक उदाहरण का उपयोग करके दो मुख्य बिंदुओं को देखें।
ऐसा करने के लिए, SQL सर्वर में एक टेस्ट डेटाबेस बनाएं और निम्नलिखित क्वेरी का उपयोग करके उसमें दो टेबल बनाएं:

टेबल बनाना

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 तालिका को पॉप्युलेट करें:

रेफरी तालिका भरना

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 मिलियन से अधिक पंक्तियाँ हैं।

अब विज़ुअल स्टूडियो में आपको एक परीक्षण विज़ुअल C# कंसोल ऐप (.NET फ्रेमवर्क) प्रोजेक्ट बनाने की आवश्यकता है:

इसके बाद, आपको डेटाबेस के साथ इंटरैक्ट करने के लिए एंटिटी फ्रेमवर्क के लिए एक लाइब्रेरी जोड़ने की जरूरत है।
इसे जोड़ने के लिए, प्रोजेक्ट पर राइट-क्लिक करें और संदर्भ मेनू से NuGet पैकेज प्रबंधित करें चुनें:

फिर, दिखाई देने वाली NuGet पैकेज प्रबंधन विंडो में, खोज विंडो में "एंटिटी फ्रेमवर्क" शब्द दर्ज करें और एंटिटी फ्रेमवर्क पैकेज का चयन करें और इसे इंस्टॉल करें:

इसके बाद, App.config फ़ाइल में, configSections तत्व को बंद करने के बाद, आपको निम्नलिखित ब्लॉक जोड़ना होगा:

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

कनेक्शनस्ट्रिंग में आपको कनेक्शन स्ट्रिंग दर्ज करना होगा।

आइए अब अलग-अलग फाइलों में 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; }
    }
}

इसके बाद, हम अलग-अलग फाइलों में अपनी दो इकाइयां बनाएंगे:

रेफरी वर्ग का कार्यान्वयन

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

हमें MS SQL सर्वर के लिए EF के माध्यम से LINQ से SQL के साथ अनुकूलन परीक्षण आयोजित करने के लिए एक तैयार समाधान प्राप्त हुआ:

अब निम्नलिखित कोड को प्रोग्राम.सीएस फ़ाइल में दर्ज करें:

प्रोग्राम.सीएस फ़ाइल

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 क्वेरी

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 सर्वर DBMS के लिए काफी अच्छी तरह से SQL क्वेरी उत्पन्न की।

अब 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 सेकंड से अधिक होने के कारण निष्पादन एक त्रुटि के साथ क्रैश हो जाएगा:

यदि आप LINQ द्वारा उत्पन्न की गई क्वेरी को देखें:

, तो आप यह सुनिश्चित कर सकते हैं कि चयन दो सेटों (तालिकाओं) के कार्टेशियन उत्पाद के माध्यम से होता है:

जेनरेट की गई SQL क्वेरी

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 क्वेरी मिलती है:

एसक्यूएल क्वेरी

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 क्वेरीज़ में केवल एक जुड़ने की स्थिति हो सकती है, इसलिए यहां प्रत्येक स्थिति के लिए दो क्वेरीज़ का उपयोग करके एक समतुल्य क्वेरी बनाना और फिर पंक्तियों के बीच डुप्लिकेट को हटाने के लिए उन्हें यूनियन के माध्यम से संयोजित करना संभव है।
हाँ, क्वेरीज़ आम तौर पर गैर-समतुल्य होंगी, यह ध्यान में रखते हुए कि पूरी डुप्लिकेट पंक्तियाँ वापस आ सकती हैं। हालाँकि, वास्तविक जीवन में, पूर्ण डुप्लिकेट लाइनों की आवश्यकता नहीं होती है और लोग उनसे छुटकारा पाने की कोशिश करते हैं।

आइए अब इन दो प्रश्नों की निष्पादन योजनाओं की तुलना करें:

क्रॉस जॉइन के लिए औसत निष्पादन समय 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 से ऑब्जेक्ट मैनिपुलेशन के लिए:

LINQ क्वेरी (पहला विकल्प)

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 क्वेरी (पहला विकल्प)

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

, और पैरा प्रकार को इस प्रकार परिभाषित किया गया है:

पैरा प्रकार परिभाषा

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

इस प्रकार, हमने MS SQL सर्वर पर LINQ क्वेरीज़ को अनुकूलित करने में कुछ पहलुओं की जांच की।

दुर्भाग्य से, अनुभवी और अग्रणी .NET डेवलपर भी भूल जाते हैं कि उन्हें यह समझने की ज़रूरत है कि वे जिन निर्देशों का उपयोग करते हैं वे पर्दे के पीछे क्या करते हैं। अन्यथा, वे विन्यासकर्ता बन जाते हैं और भविष्य में सॉफ़्टवेयर समाधान को स्केल करते समय और बाहरी पर्यावरणीय स्थितियों में मामूली बदलाव के साथ टाइम बम लगा सकते हैं।

एक संक्षिप्त समीक्षा भी की गई यहां.

परीक्षण के स्रोत - परियोजना ही, परीक्षण डेटाबेस में तालिकाओं का निर्माण, साथ ही इन तालिकाओं को डेटा से भरना स्थित है यहां.
इसके अलावा इस रिपॉजिटरी में, प्लान फ़ोल्डर में, OR शर्तों के साथ प्रश्नों को निष्पादित करने की योजनाएँ हैं।

स्रोत: www.habr.com

MS SQL सर्वर के लिए C#.NET में LINQ क्वेरीज़ को अनुकूलित करने के कुछ पहलू

एक टिप्पणी जोड़ें उत्तर रद्द