Baadhi ya vipengele vya kuboresha hoja za LINQ katika C#.NET kwa Seva ya MS SQL

LINQ imeingia .NET kama lugha mpya yenye nguvu ya upotoshaji wa data. LINQ hadi SQL kama sehemu yake hukuruhusu kuwasiliana kwa urahisi na DBMS kwa kutumia, kwa mfano, Mfumo wa Taasisi. Walakini, kwa kuitumia mara nyingi, watengenezaji husahau kuangalia ni aina gani ya swala la SQL ambalo mtoaji anayeulizwa, kwa upande wako Mfumo wa Taasisi, atatoa.

Hebu tuangalie mambo makuu mawili kwa kutumia mfano.
Ili kufanya hivyo, tengeneza hifadhidata ya Jaribio katika Seva ya SQL, na uunda meza mbili ndani yake kwa kutumia swali lifuatalo:

Kujenga meza

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

Sasa wacha tujaze jedwali la Ref kwa kuendesha hati ifuatayo:

Kujaza jedwali la Marejeleo

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

Wacha tujaze jedwali la Wateja vile vile kwa kutumia hati ifuatayo:

Kujaza jedwali la Wateja

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

Kwa hivyo, tulipokea meza mbili, moja ambayo ina safu zaidi ya milioni 1 ya data, na nyingine ina safu zaidi ya milioni 10 za data.

Sasa katika Visual Studio unahitaji kuunda mradi wa Visual C# Console App (.NET Framework):

Kisha, unahitaji kuongeza maktaba kwa Mfumo wa Huluki ili kuingiliana na hifadhidata.
Ili kuiongeza, bonyeza kulia kwenye mradi na uchague Dhibiti Vifurushi vya NuGet kutoka kwa menyu ya muktadha:

Kisha, katika dirisha la usimamizi wa kifurushi cha NuGet linaloonekana, ingiza neno "Mfumo wa Taasisi" kwenye dirisha la utafutaji na uchague kifurushi cha Mfumo wa Taasisi na usakinishe:

Ifuatayo, katika faili ya App.config, baada ya kufunga kipengele cha configSections, unahitaji kuongeza kizuizi kifuatacho:

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

Katika uhusianoString unahitaji kuingiza kamba ya uunganisho.

Sasa wacha tuunde miingiliano 3 katika faili tofauti:

1. Utekelezaji wa kiolesura cha IBaseEntityID

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

2. Utekelezaji wa kiolesura cha IBaseEntityName

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

3. Utekelezaji wa kiolesura cha IBaseNameInsertUTCDate

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

Na katika faili tofauti tutaunda darasa la msingi la BaseEntity kwa vyombo vyetu viwili, ambavyo vitajumuisha nyanja za kawaida:

Utekelezaji wa darasa la msingi la BaseEntity

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

Ifuatayo, tutaunda vyombo vyetu viwili katika faili tofauti:

1. Utekelezaji wa darasa la Marejeleo

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

2. Utekelezaji wa darasa la Wateja

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

Sasa wacha tuunde muktadha wa UserContext katika faili tofauti:

Utekelezaji wa darasa la 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; }
    }
}

Tulipokea suluhisho lililotengenezwa tayari la kufanya majaribio ya uboreshaji na LINQ hadi SQL kupitia EF ya Seva ya MS SQL:

Sasa ingiza msimbo ufuatao kwenye faili ya Program.cs:

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

Ifuatayo, tuzindua mradi wetu.

Mwisho wa kazi, yafuatayo yataonyeshwa kwenye koni:

Hoja ya SQL Inayozalishwa

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

Hiyo ni, kwa ujumla, swala la LINQ lilitoa swali la SQL kwa MS SQL Server DBMS vizuri kabisa.

Sasa wacha tubadilishe NA hali kuwa AU katika swali la LINQ:

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

Na tuzindue programu yetu tena.

Utekelezaji utaanguka na hitilafu kutokana na muda wa utekelezaji wa amri unaozidi sekunde 30:

Ukiangalia swali ambalo lilitolewa na LINQ:

, basi unaweza kuhakikisha kuwa uteuzi unatokea kupitia bidhaa ya Cartesian ya seti mbili (meza):

Hoja ya SQL Inayozalishwa

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]

Wacha tuandike upya swali la LINQ kama ifuatavyo:

Imeboresha hoja ya 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 });

Kisha tunapata swali lifuatalo la SQL:

Swali la 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]

Ole, katika maswali ya LINQ kunaweza kuwa na hali moja tu ya kujiunga, kwa hivyo hapa inawezekana kufanya swala sawa kwa kutumia maswali mawili kwa kila hali na kisha kuyachanganya kupitia Muungano ili kuondoa nakala kati ya safu.
Ndiyo, hoja kwa ujumla hazitakuwa sawa, kwa kuzingatia kwamba safu mlalo kamili zinaweza kurejeshwa. Walakini, katika maisha halisi, mistari kamili ya duplicate haihitajiki na watu hujaribu kuwaondoa.

Sasa hebu tulinganishe mipango ya utekelezaji ya maswali haya mawili:

1. kwa CROSS JOIN muda wa wastani wa utekelezaji ni sekunde 195:
   
2. kwa INNER JOIN-UNION muda wa wastani wa utekelezaji ni chini ya sekunde 24:
   

Kama unavyoona kutoka kwa matokeo, kwa jedwali mbili zilizo na mamilioni ya rekodi, hoja iliyoboreshwa ya LINQ ni haraka mara nyingi kuliko ile ambayo haijaboreshwa.

Kwa chaguo na NA katika masharti, swali la LINQ la fomu:

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

Hoja sahihi ya SQL karibu kila wakati itatolewa, ambayo itaendesha wastani katika sekunde 1:

Pia kwa LINQ kwa udanganyifu wa Vitu badala ya swala kama:

Swali la LINQ (chaguo la 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 };

unaweza kutumia swala kama:

Swali la LINQ (chaguo la 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 };

ambapo:

Kufafanua safu mbili

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

, na aina ya Para imefafanuliwa kama ifuatavyo:

Ufafanuzi wa Aina ya Para

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

Kwa hivyo, tulichunguza baadhi ya vipengele katika kuboresha maswali ya LINQ kwa Seva ya MS SQL.

Kwa bahati mbaya, hata watengenezaji wenye uzoefu na wanaoongoza wa NET wanasahau kwamba wanahitaji kuelewa maagizo wanayotumia hufanya nyuma ya pazia. Vinginevyo, wanakuwa wasanidi na wanaweza kupanda bomu la wakati katika siku zijazo wakati wa kuongeza suluhisho la programu na kwa mabadiliko madogo katika hali ya mazingira ya nje.

Tathmini fupi pia ilifanywa hapa.

Vyanzo vya jaribio - mradi wenyewe, uundaji wa jedwali kwenye hifadhidata ya TEST, na pia kujaza meza hizi na data ziko. hapa.
Pia katika hazina hii, kwenye folda ya Mipango, kuna mipango ya kutekeleza maswali na masharti AU.

Chanzo: mapenzi.com