LINQ gick in i .NET som ett kraftfullt nytt datamanipuleringsspråk. LINQ till SQL som en del av det låter dig kommunicera ganska bekvämt med ett DBMS med hjälp av till exempel Entity Framework. Men med hjälp av det ganska ofta glömmer utvecklare att titta på vilken typ av SQL-fråga den frågebara leverantören, i ditt fall Entity Framework, kommer att generera.
Låt oss titta på två huvudpunkter med hjälp av ett exempel.
För att göra detta, skapa en testdatabas i SQL Server och skapa två tabeller i den med hjälp av följande fråga:
Skapa tabeller
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
Låt oss nu fylla i Ref-tabellen genom att köra följande skript:
Fyller Ref tabellen
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åt oss på samma sätt fylla Kundtabellen med följande skript:
Fyller i kundtabellen
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
Således fick vi två tabeller, varav den ena har mer än 1 miljon rader med data och den andra har mer än 10 miljoner rader med data.
Nu i Visual Studio måste du skapa ett testprojekt för Visual C# Console App (.NET Framework):
Därefter måste du lägga till ett bibliotek för att Entity Framework ska kunna interagera med databasen.
För att lägga till det, högerklicka på projektet och välj Hantera NuGet-paket från snabbmenyn:
Sedan, i NuGet-pakethanteringsfönstret som visas, skriv in ordet "Entity Framework" i sökfönstret och välj Entity Framework-paketet och installera det:
Därefter, i filen App.config, efter att ha stängt configSections-elementet, måste du lägga till följande block:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
I connectionString måste du ange anslutningssträngen.
Låt oss nu skapa 3 gränssnitt i separata filer:
- Implementering av gränssnittet IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementering av gränssnittet IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementering av gränssnittet IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Och i en separat fil kommer vi att skapa en basklass BaseEntity för våra två enheter, som kommer att inkludera vanliga fält:
Implementering av basklassen BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Därefter kommer vi att skapa våra två enheter i separata filer:
- Implementering av Ref-klassen
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementering av kundklassen
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; } } }
Låt oss nu skapa en UserContext-kontext i en separat fil:
Implementering av UserContex-klassen
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; }
}
}
Vi fick en färdig lösning för att genomföra optimeringstester med LINQ till SQL via EF för MS SQL Server:
Ange nu följande kod i filen Program.cs:
Program.cs-filen
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();
}
}
}
}
Låt oss sedan lansera vårt projekt.
I slutet av arbetet kommer följande att visas på konsolen:
Genererad SQL-fråga
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])
Det vill säga, i allmänhet genererade LINQ-frågan en SQL-fråga till MS SQL Server DBMS ganska bra.
Låt oss nu ändra AND-villkoret till OR i LINQ-frågan:
LINQ-fråga
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 };
Och låt oss starta vår applikation igen.
Körningen kommer att krascha med ett fel på grund av att kommandots körtid överstiger 30 sekunder:
Om du tittar på frågan som genererades av LINQ:
, då kan du se till att urvalet sker genom den kartesiska produkten av två uppsättningar (tabeller):
Genererad SQL-fråga
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]
Låt oss skriva om LINQ-frågan enligt följande:
Optimerad LINQ-fråga
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 });
Då får vi följande SQL-fråga:
SQL-fråga
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]
Tyvärr, i LINQ-frågor kan det bara finnas ett kopplingsvillkor, så här är det möjligt att göra en likvärdig fråga med två frågor för varje villkor och sedan kombinera dem genom Union för att ta bort dubbletter bland raderna.
Ja, frågorna kommer i allmänhet inte att vara likvärdiga, med hänsyn till att fullständiga dubbletter av rader kan returneras. Men i verkliga livet behövs inte kompletta dubbletter av linjer och människor försöker bli av med dem.
Låt oss nu jämföra genomförandeplanerna för dessa två frågor:
- för CROSS JOIN är den genomsnittliga exekveringstiden 195 sekunder:
- för INNER JOIN-UNION är den genomsnittliga exekveringstiden mindre än 24 sekunder:
Som du kan se av resultaten, för två tabeller med miljontals poster, är den optimerade LINQ-frågan många gånger snabbare än den ooptimerade.
För alternativet med OCH i villkoren, en LINQ-fråga i formen:
LINQ-fråga
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 };
Den korrekta SQL-frågan kommer nästan alltid att genereras, som körs i genomsnitt på cirka 1 sekund:
Även för LINQ to Objects-manipulationer istället för en fråga som:
LINQ-fråga (första alternativet)
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 };
du kan använda en fråga som:
LINQ-fråga (första alternativet)
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 };
där:
Definiera två arrayer
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" } };
, och Para-typen definieras enligt följande:
Para Type Definition
class Para
{
public int Key1, Key2;
public string Data;
}
Därför undersökte vi några aspekter av att optimera LINQ-frågor till MS SQL Server.
Tyvärr glömmer även erfarna och ledande .NET-utvecklare att de måste förstå vad instruktionerna de använder gör bakom kulisserna. Annars blir de konfiguratorer och kan plantera en tidsinställd bomb i framtiden både vid skalning av mjukvarulösningen och med mindre förändringar i yttre miljöförhållanden.
En kort genomgång gjordes också .
Källorna för testet - själva projektet, skapandet av tabeller i TEST-databasen, samt att fylla dessa tabeller med data finns .
Även i det här arkivet, i mappen Planer, finns det planer på att köra frågor med OR-villkor.
Källa: will.com