🥇Quaedam rationes optimizing LINQ queries in C#. NET pro MS SQL Server

LINQ intravit .NET ut valida notitiae novae linguae manipulationis. LINQ ad SQL ut pars eius permittit te satis commode communicare cum usura DBMS, exempli gratia, Entity Framework. Tamen, utendo saepius, tincidunt obliviscuntur inspicere quale SQL quaesitum est provisor quaesitus, in casu tuo Entity Framework, generabit.

Intueamur duo praecipua utens exemplo.
Ad hoc facere, database in SQL Server crea et in eo duas tabulas crea in interrogatione sequenti utens:

Tabulas creando

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

Nunc celebremus mensam Ref currentem scriptum:

Implens mensam 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

Let's similarly fill Customer table using the following scriptum:

Populatio Customer mensam

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

Ita duas tabulas accepimus, quarum una plus quam 1 decies centena milia ordines datarum, altera plus quam 10 decies centena milia notitiarum habet.

Nunc in Visual Studio creare debes experimentum Visual C# Console App (.NET Framework) project:

Deinde, debes bibliothecam addere pro Entity Framework ut correspondeat datorum.
Ad illud, ius-click in incepto et selectis Curo NuGet Sarcinas e tabula contexta:

Deinde, in fenestra procuratio sarcinae NuGet quae apparet, verbum "entity Framework" in fenestra quaerendi inscribe et sarcina Entity Framework elige et eam institue:

Deinde in tabula App.config, postquam elementi configSections claudendo, sequenti clausula addere debes:

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

In nexuString filum nexum intrare debes.

Nunc 3 interfaces in singulis fasciculis faciamus:

Exsequens IBaseEntityID interface

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

Exsecutio IBaseEntityName interface

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

Exsecutio IBaseNameInsertUTCDate interface

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

Et in fasciculo separato genus baseEntitatis basis pro duobus entibus nostris creabimus, quae agros communes complectentur;

Classis basis exsequendam BaseEntity

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

Deinde duo entia in singulis fasciculis creabimus:

Exsequendam Ref classis

using System.ComponentModel.DataAnnotations.Schema;

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

Exsequendam Customer classis

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

Nunc faciamus contextum UserContext in singulis fasciculis:

Exsequendam UserContex classis

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

Praeparatam solutionem accepimus pro optimizatione probationibus faciendis cum LINQ ad SQL per EF pro Servo MS SQL:

Nunc sequentem codicem intrant in fasciculum 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();
            }
        }
    }
}

Deinceps in consilium nostrum Lorem.

Ad calcem operis sequentia in console ostendentur.

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

Hoc est, generatim, quaesitum LINQ quaesitum SQL ex MS SQL Server DBMS bene admodum generatum.

Nunc mutemus condicionem AND ad OR in interrogatione LINQ:

LINQ query

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

Et rursus applicationem demus.

Executio ruinabit errori ob executionem temporis XXX secundis excedentibus;

Si quaesitionem spectes quae ab LINQ generata est:

, tunc efficere potes ut electio fiat per productum cartesianum duarum (tabularum);

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

Quaestionem LINQ rescribe ut sequitur:

Optimized LINQ quaesitum

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

Sequentes deinde SQL interrogationem dabimus:

SQL query

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]

Eheu, in LINQ queries non potest nisi una conditio copulare, ergo hic potest interrogationem aequipollentem facere utentes duas interrogationes pro unaquaque condicione et tunc per Unionem coniungens ut duplicata inter ordines removeat.
Etiam interrogationes generaliter erunt non aequipollentes, habita ratione ut ordines duplicati perfecti reddantur. Attamen, in vita reali, lineae duplicatae completae non sunt necessariae et eas abicere conantur.

Iam consilia exsecutionis harum duarum quaestionum comparemus:

CRUCIS JOIN medium executionis tempus est 195 secundis;
pro INTERIOR CONIUNCTIO mediocris exsecutionis tempus minus est quam 24 secundis;

Ut videre potes ex eventibus, duabus tabulis cum decies centena millia monumentorum, quaestio LINQ optimized multoties velocior est quam una optima.

Ad optionem cum ET in condicionibus, interrogatione LINQ formae:

LINQ query

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

Recta SQL quaesitio fere semper generabitur, quae in medio circiter secundo 1 current;

Item pro LINQ ad objecta motiva loco quaesiti similis;

LINQ quaesitum (1 optio)

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

quaesitum est ut possis uti:

LINQ quaesitum (2 optio)

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

ubi:

Definiens duo vestit

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

ac Para genus definitur;

Para Type Definition

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

Sic rationes nonnullas examinavimus in quaestionibus optimizing LINQ ad MS SQL Server.

Infeliciter, etiam periti et praecipui .NET tincidunt obliviscuntur se intelligere indigentes instructiones quae utuntur post scaenas facere. Alioquin conformatores efficiuntur et in futuro tempore bombam plantare possunt sive solutionem programmatum scandentem cum minoribus mutationibus in condicionibus extraneis circumiectis.

Brevis recensionem etiam peractum est hic.

Fontes ad experimentum - ipsum propositum, creatio mensarum in datorum TESTA, necnon has tabulas cum notitia implentes siti sunt. hic.
Etiam in hoc reposito, in consiliorum folder, consilia sunt quaerendi cum OR conditionibus faciendis.

Source: www.habr.com

Quidam rationes optimizing LINQ queries in C#. NET pro MS SQL Server

Add a comment Отменить ответ