🥇MS SQL सर्व्हरसाठी C#.NET मध्ये LINQ क्वेरी ऑप्टिमाइझ करण्याचे काही पैलू

LINQ ने .NET मध्ये एक शक्तिशाली नवीन डेटा हाताळणी भाषा म्हणून प्रवेश केला. त्याचा भाग म्हणून LINQ ते SQL तुम्हाला DBMS सह अगदी सोयीस्करपणे संवाद साधण्याची परवानगी देते, उदाहरणार्थ, Entity Framework. तथापि, बर्‍याचदा याचा वापर करून, डेव्हलपर आपल्या बाबतीत, एंटिटी फ्रेमवर्क, क्वेरी करण्यायोग्य प्रदाता कोणत्या प्रकारची 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

आता खालील स्क्रिप्ट चालवून रेफ टेबल पॉप्युलेट करू.

रेफ टेबल भरत आहे

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 सह ऑप्टिमायझेशन चाचण्या आयोजित करण्यासाठी आम्हाला एक तयार समाधान प्राप्त झाले:

आता खालील कोड 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 क्वेरी

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

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 सेकंद आहे:
INNER JOIN-UNION साठी सरासरी अंमलबजावणी वेळ 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;
}

अशा प्रकारे, आम्ही एमएस SQL सर्व्हरवर LINQ क्वेरी ऑप्टिमाइझ करण्याच्या काही पैलूंचे परीक्षण केले.

दुर्दैवाने, अगदी अनुभवी आणि आघाडीचे .NET विकासक हे विसरतात की ते वापरत असलेल्या सूचना पडद्यामागे काय करतात हे समजून घेणे आवश्यक आहे. अन्यथा, ते कॉन्फिगरेटर बनतात आणि सॉफ्टवेअर सोल्यूशन स्केल करताना आणि बाह्य पर्यावरणीय परिस्थितीत किरकोळ बदलांसह भविष्यात टाईम बॉम्ब लावू शकतात.

एक छोटासा आढावाही घेण्यात आला येथे.

चाचणीचे स्रोत - प्रकल्प स्वतःच, TEST डेटाबेसमध्ये सारण्या तयार करणे, तसेच या सारण्या डेटासह भरणे येथे आहे. येथे.
तसेच या भांडारात, प्लॅन्स फोल्डरमध्ये, OR अटींसह क्वेरी कार्यान्वित करण्याच्या योजना आहेत.

स्त्रोत: www.habr.com

एमएस SQL सर्व्हरसाठी C#.NET मध्ये LINQ क्वेरी ऑप्टिमाइझ करण्याचे काही पैलू

एक टिप्पणी जोडा Отменить ответ

एमएस SQL ​​सर्व्हरसाठी C#.NET मध्ये LINQ क्वेरी ऑप्टिमाइझ करण्याचे काही पैलू

एक टिप्पणी जोडा Отменить ответ

एमएस SQL सर्व्हरसाठी C#.NET मध्ये LINQ क्वेरी ऑप्टिमाइझ करण्याचे काही पैलू