LINQ introducerades i .NET som ett nytt kraftfullt sprĂ„k för datamanipulering. LINQ to SQL som en del av det gör det ganska enkelt att kommunicera med databaser via till exempel Entity Framework. Men, nĂ€r utvecklare anvĂ€nder det ofta, glömmer de att fundera pĂ„ vilken SQL-frĂ„ga som kommer att genereras av queryable provider, i ditt fall â Entity Framework.
Vi kommer att gÄ igenom tvÄ huvudpunkter med ett exempel.
För detta ÀndamÄl skapar vi en databas i SQL Server som heter Test, och i den skapar vi tvÄ tabeller med följande frÄga:
Skapande av 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
Nu fyller vi tabellen Ref genom att köra följande skript:
Fyllning av tabellen 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
PÄ liknande sÀtt fyller vi tabellen Customer med hjÀlp av följande skript:
Fyllning av tabellen Customer
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=0) SET @ind_ref=4;
ELSE IF (@ind=0) SET @ind_ref=5;
ELSE IF (@ind=0) SET @ind_ref=6;
ELSE IF (@ind=0) SET @ind_ref=7;
ELSE IF (@ind=0) SET @ind_ref=8;
ELSE IF (@ind=0) SET @ind_ref=9;
ELSE IF (@ind=0) SET @ind_ref=10;
ELSE IF (@ind=0) SET @ind_ref=11;
ELSE SET @ind_ref=@ind90000;
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 har vi fÄtt tvÄ tabeller, dÀr den ena innehÄller mer Àn 1 miljon rader data och den andra mer Àn 10 miljoner rader data.
Nu i Visual Studio mÄste du skapa ett testprojekt Visual C# Console App (.NET Framework):
Sedan mÄste du lÀgga till ett bibliotek för Entity Framework för att interagera med databasen.
För att lÀgga till den, högerklicka pÄ projektet och vÀlj "Hantera NuGet-paket" frÄn kontextmenyn:
I det följande fönstret för NuGet-pakethanterare skriver vi "Entity Framework" i sökfÀltet, vÀljer paketet Entity Framework och installerar det:
Sedan i filen App.config efter slutet av elementet configSections mÄste vi lÀgga till följande block:
I connectionString mÄste vi ange anslutningsstrÀngen.
Nu skapar vi tre 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 skapar vi basklass BaseEntity för vÄra tvÄ entiteter, som kommer att innehÄlla gemensamma fÀlt:
Implementering av basklass BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Sedan i separata filer skapar vi vÄra tvÄ entiteter:
- Implementering av klassen Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementering av klassen Customer
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; } } }
Nu skapar vi kontexten UserContext i en separat fil:
Implementering av klassen UserContext
using System.Data.Entity;
namespace TestLINQ
{
public class UserContext : DbContext
{
public UserContext()
: base("DbConnection")
{
Database.SetInitializer(null);
}
public DbSet Customer { get; set; }
public DbSet Ref { get; set; }
}
}
Vi har fÄtt en fÀrdig lösning för att genomföra tester för optimering med LINQ to SQL via EF för MS SQL Server:
Nu skriver vi följande kod i filen Program.cs:
Fil 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();
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 nu köra vÄrt projekt.
I slutet av körningen kommer följande att skrivas ut i 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, genererade SQL-frÄgan frÄn LINQ Àr i allmÀnhet ganska bra för MS SQL Server.
Nu ska vi Àndra villkoret frÄn OCH till ELLER i LINQ-frÄgan:
LINQ-frÄgan
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 vi kör vÄrt program igen.
Exekveringen kommer att krascha med ett fel relaterat till överskridande av körningstiden för kommandot pÄ 30 sekunder:
Om vi ser vilken frÄga som genererades av LINQ:
, kan vi konstatera att urvalet görs genom kartesiskt produkt av tvÄ mÀngder (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 pÄ följande sÀtt:
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 kan det i LINQ-frÄgor bara finnas en anslutningsvillkor, sÄ hÀr kan en ekvivalent frÄga göras genom tvÄ frÄgor för varje villkor, och sedan förenas de med Union för att ta bort dubbletter bland rader.
Ja, frÄgorna blir i allmÀnhet inte ekvivalenta med hÀnsyn till att fullstÀndiga dubbletter av rader kan returneras. Men i verkliga livet behövs inte fullstÀndiga dublicerade rader och man försöker bli av med dem.
LÄt oss nu jÀmföra exekveringsplanerna för dessa tvÄ frÄgor:
- för CROSS JOIN Àr den genomsnittliga exekveringstiden 195 sek:
- för INNER JOIN-UNION Àr den genomsnittliga exekveringstiden mindre Àn 24 sek:
Som vi kan se frÄn resultaten, sÄ fungerar den optimerade LINQ-frÄgan för tvÄ tabeller med miljoner poster ett flertal gÄnger snabbare Àn den icke optimerade.
För alternativet med OCH i villkoren för LINQ-frÄgan av typen:
LINQ-frÄgan
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 };
kommer nÀstan alltid att en korrekt SQL-frÄga att genereras, vilket i genomsnitt kommer att köras pÄ ungefÀr 1 sek:
Ăven för manipulationer av LINQ till objekt istĂ€llet för en frĂ„ga av typen:
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 };
kan en frÄga av typen anvÀndas:
LINQ-frÄga (andra 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:
Definition av 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 typen Para definieras som följer:
Definition av typen Para
class Para
{
public int Key1, Key2;
public string Data;
}
SÄledes har vi behandlat nÄgra aspekter av optimeringen av LINQ-frÄgor mot MS SQL Server.
TyvÀrr glömmer till och med erfarna och ledande .NET-utvecklare att de mÄste förstÄ vad som hÀnder bakom kulisserna av de instruktioner de anvÀnder. Annars blir de endast konfiguratörer och kan lÀgga en tidsinstÀlld bomb för framtiden bÄde vid skalning av programlösningen och vid mindre förÀndringar i miljöns externa förhÄllanden.
En liten översikt gjordes ocksÄ .
KĂ€llkoden för testet â sjĂ€lva projektet, skapandet av tabeller i databasen TEST, samt ifyllandet av dessa tabeller finns .
I detta arkiv finns Àven en mapp som heter Plans dÀr planerna för att utföra förfrÄgningar med OCH-villkor finns.
KĂ€lla: habr.com
