ProHoster > Blog > Administrasi > Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server

Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server

LINQ diasupkeun .NET salaku basa manipulasi data anyar kuat. LINQ ka SQL salaku bagian tina eta ngidinan Anjeun pikeun komunikasi rada merenah sareng DBMS ngagunakeun, contona, Entity Framework. Sanajan kitu, ngagunakeun eta rada mindeng, pamekar poho pikeun nempo jenis query SQL panyadia queryable, bisi anjeun Entity Framework, bakal ngahasilkeun.

Hayu urang nempo dua titik utama ngagunakeun conto.
Jang ngalampahkeun ieu, nyieun database Test dina SQL Server, sarta nyieun dua tabel di dinya ngagunakeun query handap:

Nyieun tabél

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

Ayeuna hayu urang ngeusian tabel Ref ku ngajalankeun skrip ieu:

Ngeusian méja 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

Hayu urang sami-sami ngeusian tabel Pelanggan nganggo skrip ieu:

Populating tabel Palanggan

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

Ku kituna, kami narima dua tabel, salah sahiji nu boga leuwih ti 1 juta jajar data, sarta séjén boga leuwih ti 10 juta jajar data.

Ayeuna di Visual Studio anjeun kedah ngadamel tés Visual C# Console App (.NET Framework) proyék:

Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server

Salajengna, anjeun kedah nambihan perpustakaan pikeun Entity Framework pikeun berinteraksi sareng pangkalan data.
Pikeun nambihanana, klik katuhu dina proyék sareng pilih Atur Paket NuGet tina ménu kontéks:

Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server

Teras, dina jandela manajemén pakét NuGet anu ditembongkeun, lebetkeun kecap "Kerangka Badan" dina jandela panéangan teras pilih pakét Entity Framework sareng pasang:

Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server

Salajengna, dina file App.config, saatos nutup unsur configSections, anjeun kedah nambihan blok ieu:

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

Dina connectionString anjeun kudu ngasupkeun string sambungan.

Ayeuna hayu urang nyieun 3 interfaces dina file misah:

  1. Ngalaksanakeun panganteur IBaseEntityID 
    namespace TestLINQ
{
    public interface IBaseEntityID
    {
        int ID { get; set; }
    }
}

  2. Palaksanaan panganteur IBaseEntityName 
    namespace TestLINQ
{
    public interface IBaseEntityName
    {
        string Name { get; set; }
    }
}

  3. Palaksanaan panganteur IBaseNameInsertUTCDate 
    namespace TestLINQ
{
    public interface IBaseNameInsertUTCDate
    {
        DateTime InsertUTCDate { get; set; }
    }
}

Sareng dina file anu misah kami bakal nyiptakeun BaseEntity kelas dasar pikeun dua éntitas kami, anu bakal kalebet widang umum:

Palaksanaan kelas dasar BaseEntity

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

Salajengna, urang bakal nyiptakeun dua éntitas dina file anu misah:

  1. Palaksanaan kelas Ref 
    using System.ComponentModel.DataAnnotations.Schema;

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

  2. Palaksanaan kelas Palanggan 
    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; }
    }
}

Ayeuna hayu urang ngadamel kontéks UserContext dina file anu misah:

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

Kami nampi solusi anu siap-siap pikeun ngalaksanakeun tés optimasi sareng LINQ ka SQL ngalangkungan EF pikeun MS SQL Server:

Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server

Ayeuna lebetkeun kodeu di handap ieu kana file Program.cs:

Program.cs file

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

Salajengna, hayu urang ngajalankeun proyék urang.

Dina ahir pagawéan, ieu bakal ditingalikeun dina konsol:

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

Hartina, sacara umum, query LINQ ngahasilkeun query SQL ka MS SQL Server DBMS lumayan alus.

Ayeuna hayu urang robih kaayaan AND kana ATAWA dina pamundut LINQ:

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

Sareng hayu urang ngaluncurkeun aplikasi urang deui.

Eksekusi bakal nabrak sareng kasalahan kusabab waktos palaksanaan paréntah langkung ti 30 detik:

Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server

Upami anjeun ningali pamundut anu didamel ku LINQ:

Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server
, teras anjeun tiasa mastikeun yén pamilihan lumangsung ngaliwatan produk Cartesian tina dua sét (tabel):

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

Hayu urang nyerat deui pamundut LINQ sapertos kieu:

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

Teras we kéngingkeun pamundut SQL di handap ieu:

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

Alas, dina queries LINQ ngan aya hiji kaayaan gabung, jadi didieu kasebut nyaéta dimungkinkeun pikeun nyieun hiji query sarimbag ngagunakeun dua queries pikeun tiap kaayaan lajeng ngagabungkeun aranjeunna ngaliwatan Uni pikeun miceun duplikat diantara barisan.
Leres, patarosan umumna henteu sarimbag, kalayan ngitung yén barisan duplikat lengkep tiasa dipulangkeun. Nanging, dina kahirupan nyata, garis duplikat lengkep henteu diperyogikeun sareng jalma-jalma nyobian ngaleungitkeunana.

Ayeuna hayu urang ngabandingkeun rencana palaksanaan dua queries ieu:

  1. pikeun CROSS JOIN rata-rata waktos palaksanaan nyaéta 195 detik:
    Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server
  2. pikeun INNER JOIN-UNION rata-rata waktos palaksanaan kirang ti 24 detik:
    Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server

Как видно из результатов, для двух таблиц с миллионами записей оптимизированный LINQ-запрос работает в разы быстрее, чем неоптимизированный.

Для варианта с И в условиях LINQ-запрос вида:

patarosan 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 сек:

Sababaraha aspék ngaoptimalkeun queries LINQ dina C#.NET pikeun MS SQL Server
Также для манипуляций 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 };

dimana:

Определение двух массивов

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 определяется следующим образом:

Определение типа Para

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

Таким образом мы рассмотрели некоторые аспекты в оптимизации LINQ-запросов к MS SQL Server.

К сожалению даже опытные и ведущие .NET-разработчики забывают о том, что необходимо понимать что делают за кадром те инструкции, которые они используют. Иначе они становятся конфигураторами и могут заложить бомбу замедленного действия в будущем как при масштабировании программного решения, так и при незначительных изменениях внешних условий среды.

Также небольшой обзор проводился и di dieu.

Исходники для теста-сам проект, создание таблиц в базе данных TEST, а также наполнение данными этих таблиц находится di dieu.
Ogé dina gudang ieu, dina folder Plans, aya rencana pikeun executing queries kalawan kaayaan OR.

sumber: www.habr.com

Tambahkeun komentar