ProHoster > Blogs > Administrācija > Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server

Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server

LINQ ievadīja .NET kā jaudīgu jaunu datu apstrādes valodu. LINQ to SQL kā daļa no tā ļauj diezgan ērti sazināties ar DBVS, izmantojot, piemēram, Entity Framework. Tomēr, izmantojot to diezgan bieži, izstrādātāji aizmirst paskatīties, kādu SQL vaicājumu ģenerēs vaicājuma nodrošinātājs, jūsu gadījumā Entity Framework.

Apskatīsim divus galvenos punktus, izmantojot piemēru.
Lai to izdarītu, SQL Server izveidojiet testa datu bāzi un izveidojiet tajā divas tabulas, izmantojot šādu vaicājumu:

Tabulu veidošana

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

Tagad aizpildīsim tabulu Ref, izpildot šādu skriptu:

Ref tabulas aizpildīšana

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

Līdzīgi aizpildīsim tabulu Klients, izmantojot šādu skriptu:

Klientu tabulas aizpildīšana

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

Tādējādi mēs saņēmām divas tabulas, no kurām vienā ir vairāk nekā 1 miljons datu rindu, bet otrā ir vairāk nekā 10 miljonu datu rindu.

Tagad programmā Visual Studio jums ir jāizveido testa Visual C# konsoles lietojumprogrammas (.NET Framework) projekts:

Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server

Pēc tam jums ir jāpievieno bibliotēka Entity Framework mijiedarbībai ar datu bāzi.
Lai to pievienotu, ar peles labo pogu noklikšķiniet uz projekta un konteksta izvēlnē atlasiet Pārvaldīt NuGet pakotnes:

Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server

Pēc tam parādītajā NuGet pakotņu pārvaldības logā meklēšanas logā ievadiet vārdu “Entity Framework” un atlasiet Entity Framework pakotni un instalējiet to:

Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server

Pēc tam failā App.config pēc elementa configSections aizvēršanas jāpievieno šāds bloks:

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

Savienojuma virknē ir jāievada savienojuma virkne.

Tagad izveidosim 3 saskarnes atsevišķos failos:

  1. IBaseEntityID saskarnes ieviešana 
    namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

  2. IBaseEntityName saskarnes ieviešana 
    namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

  3. IBaseNameInsertUTCDate saskarnes ieviešana 
    namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

Un atsevišķā failā mēs izveidosim pamatklasi BaseEntity mūsu divām entītijām, kurā būs iekļauti kopējie lauki:

Bāzes klases BaseEntity ieviešana

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

Tālāk mēs izveidosim abas entītijas atsevišķos failos:

  1. Ref klases ieviešana 
    using System.ComponentModel.DataAnnotations.Schema;

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

  2. Klientu klases ieviešana 
    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; }
    }
}

Tagad izveidosim UserContext kontekstu atsevišķā failā:

UserContex klases ieviešana

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

Mēs saņēmām gatavu risinājumu optimizācijas testu veikšanai ar LINQ uz SQL, izmantojot EF for MS SQL Server:

Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server

Tagad failā Program.cs ievadiet šādu kodu:

Program.cs fails

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

Tālāk sāksim savu projektu.

Darba beigās konsolē tiks parādīts:

Ģenerēts SQL vaicājums

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

Tas nozīmē, ka kopumā LINQ vaicājums diezgan labi ģenerēja SQL vaicājumu MS SQL Server DBVS.

Tagad LINQ vaicājumā mainīsim nosacījumu UN uz VAI:

LINQ vaicājums

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

Un atkal palaidīsim savu lietojumprogrammu.

Izpilde avarēs ar kļūdu, jo komandas izpildes laiks pārsniedz 30 sekundes:

Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server

Ja skatāties uz vaicājumu, ko ģenerēja LINQ:

Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server
, tad varat pārliecināties, ka atlase notiek, izmantojot divu kopu (tabulu) Dekarta reizinājumu:

Ģenerēts SQL vaicājums

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]

Pārrakstīsim LINQ vaicājumu šādi:

Optimizēts LINQ vaicājums

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

Tad mēs saņemam šādu SQL vaicājumu:

SQL vaicājums

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]

Diemžēl LINQ vaicājumos var būt tikai viens savienojuma nosacījums, tāpēc šeit ir iespējams izveidot līdzvērtīgu vaicājumu, izmantojot divus vaicājumus katram nosacījumam un pēc tam apvienojot tos, izmantojot Union, lai noņemtu dublikātus starp rindām.
Jā, vaicājumi parasti nebūs līdzvērtīgi, ņemot vērā, ka var tikt atgrieztas pilnīgas dublētās rindas. Tomēr reālajā dzīvē pilnīgas dublētās līnijas nav vajadzīgas, un cilvēki cenšas no tām atbrīvoties.

Tagad salīdzināsim šo divu vaicājumu izpildes plānus:

  1. CROSS JOIN vidējais izpildes laiks ir 195 sekundes:
    Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server
  2. INNER JOIN-UNION vidējais izpildes laiks ir mazāks par 24 sekundēm:
    Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server

Kā redzat no rezultātiem, divām tabulām ar miljoniem ierakstu optimizētais LINQ vaicājums ir daudzkārt ātrāks nekā neoptimizētais.

Opcijai ar nosacījumu UN, LINQ vaicājums šādā formā:

LINQ vaicājums

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

Gandrīz vienmēr tiks ģenerēts pareizais SQL vaicājums, kas tiks izpildīts vidēji aptuveni 1 sekundē:

Daži LINQ vaicājumu optimizēšanas aspekti C#.NET MS SQL Server
Arī manipulācijām ar LINQ to Objects, nevis vaicājumu, piemēram:

LINQ vaicājums (1. iespēja)

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

varat izmantot vaicājumu, piemēram:

LINQ vaicājums (2. iespēja)

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

ja:

Divu masīvu definēšana

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

, un Para tips ir definēts šādi:

Para tipa definīcija

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

Tādējādi mēs pārbaudījām dažus aspektus, optimizējot LINQ vaicājumus uz MS SQL Server.

Diemžēl pat pieredzējuši un vadošie .NET izstrādātāji aizmirst, ka viņiem ir jāsaprot, ko aizkulisēs dara viņu izmantotās instrukcijas. Pretējā gadījumā tie kļūst par konfiguratoriem un nākotnē var ielikt bumbu ar laika degli, gan mērogojot programmatūras risinājumu, gan ar nelielām izmaiņām ārējās vides apstākļos.

Tika veikts arī īss pārskats šeit.

Atrodas testa avoti - pats projekts, tabulu izveide TEST datu bāzē, kā arī šo tabulu aizpildīšana ar datiem šeit.
Arī šajā repozitorijā, mapē Plāni, ir plāni vaicājumu izpildei ar VAI nosacījumiem.

Avots: www.habr.com

Pievieno komentāru