LINQ introduceerde .NET als een krachtige nieuwe taal voor gegevensmanipulatie. Met LINQ to SQL, als onderdeel ervan, kunt u heel gemakkelijk communiceren met een DBMS met behulp van bijvoorbeeld Entity Framework. Als ontwikkelaars het echter vaak gebruiken, vergeten ze te kijken naar wat voor soort SQL-query de doorzoekbare provider, in jouw geval Entity Framework, zal genereren.
Laten we twee hoofdpunten bekijken aan de hand van een voorbeeld.
Om dit te doen, maakt u een testdatabase in SQL Server en maakt u daarin twee tabellen met behulp van de volgende query:
Tabellen maken
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
Laten we nu de Ref-tabel vullen door het volgende script uit te voeren:
Het invullen van de Ref-tabel
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
Laten we op dezelfde manier de tabel Klant vullen met het volgende script:
Het invullen van de tabel Klanten
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
We hebben dus twee tabellen ontvangen, waarvan er één meer dan 1 miljoen rijen met gegevens bevat, en de andere meer dan 10 miljoen rijen met gegevens.
Nu moet u in Visual Studio een testproject voor de Visual C# Console App (.NET Framework) maken:
Vervolgens moet u een bibliotheek toevoegen zodat het Entity Framework met de database kan communiceren.
Om het toe te voegen, klikt u met de rechtermuisknop op het project en selecteert u NuGet-pakketten beheren in het contextmenu:
Voer vervolgens in het NuGet-pakketbeheervenster dat verschijnt het woord "Entity Framework" in het zoekvenster in, selecteer het Entity Framework-pakket en installeer het:
Vervolgens moet u in het App.config-bestand, na het sluiten van het configSections-element, het volgende blok toevoegen:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
In ConnectionString moet u de verbindingsreeks invoeren.
Laten we nu 3 interfaces in afzonderlijke bestanden maken:
- Implementatie van de IBaseEntityID-interface
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementatie van de IBaseEntityName-interface
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementatie van de IBaseNameInsertUTCDate-interface
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
En in een apart bestand zullen we een basisklasse BaseEntity maken voor onze twee entiteiten, die gemeenschappelijke velden zal bevatten:
Implementatie van de basisklasse BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Vervolgens zullen we onze twee entiteiten in afzonderlijke bestanden maken:
- Implementatie van de Ref-klasse
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementatie van de klasse Klant
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; } } }
Laten we nu een UserContext-context in een apart bestand maken:
Implementatie van de UserContex-klasse
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; }
}
}
We ontvingen een kant-en-klare oplossing voor het uitvoeren van optimalisatietests met LINQ naar SQL via EF voor MS SQL Server:
Voer nu de volgende code in het Program.cs-bestand in:
Program.cs-bestand
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();
}
}
}
}
Laten we vervolgens ons project lanceren.
Aan het einde van het werk wordt het volgende op de console weergegeven:
Gegenereerde 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])
Dat wil zeggen dat de LINQ-query over het algemeen vrij goed een SQL-query naar de MS SQL Server DBMS genereerde.
Laten we nu de AND-voorwaarde wijzigen in OR in de LINQ-query:
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 };
En laten we onze applicatie opnieuw lanceren.
De uitvoering crasht met een fout omdat de uitvoeringstijd van de opdracht langer duurt dan 30 seconden:
Als je kijkt naar de query die door LINQ is gegenereerd:
, dan kun je ervoor zorgen dat de selectie plaatsvindt via het cartesiaanse product van twee sets (tabellen):
Gegenereerde 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]
Laten we de LINQ-query als volgt herschrijven:
Geoptimaliseerde LINQ-query
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 });
Dan krijgen we de volgende SQL-query:
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]
Helaas kan er in LINQ-query's slechts één samenvoegvoorwaarde zijn, dus hier is het mogelijk om een gelijkwaardige query te maken door twee query's voor elke voorwaarde te gebruiken en deze vervolgens te combineren via Union om duplicaten tussen de rijen te verwijderen.
Ja, de zoekopdrachten zijn over het algemeen niet-equivalent, rekening houdend met het feit dat volledige dubbele rijen kunnen worden geretourneerd. In het echte leven zijn volledige dubbele regels echter niet nodig en proberen mensen er vanaf te komen.
Laten we nu de uitvoeringsplannen van deze twee query's vergelijken:
- voor CROSS JOIN is de gemiddelde uitvoeringstijd 195 seconden:
- voor INNER JOIN-UNION is de gemiddelde uitvoeringstijd minder dan 24 seconden:
Zoals u uit de resultaten kunt zien, is de geoptimaliseerde LINQ-query voor twee tabellen met miljoenen records vele malen sneller dan de niet-geoptimaliseerde.
Voor de optie met AND in de voorwaarden een LINQ-query van de vorm:
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 };
Bijna altijd wordt de juiste SQL-query gegenereerd, die gemiddeld in ongeveer 1 seconde wordt uitgevoerd:
Ook voor LINQ to Objects-manipulaties in plaats van een query zoals:
LINQ-query (1e optie)
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 };
je kunt een query gebruiken zoals:
LINQ-query (2e optie)
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 };
waar:
Twee arrays definiëren
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" } };
, en het Para-type wordt als volgt gedefinieerd:
Definitie van paratype
class Para
{
public int Key1, Key2;
public string Data;
}
Daarom hebben we enkele aspecten onderzocht bij het optimaliseren van LINQ-query's naar MS SQL Server.
Helaas vergeten zelfs ervaren en toonaangevende .NET-ontwikkelaars dat ze moeten begrijpen wat de instructies die ze gebruiken achter de schermen doen. Anders worden ze configuratoren en kunnen ze in de toekomst een tijdbom plaatsen, zowel bij het opschalen van de softwareoplossing als bij kleine veranderingen in externe omgevingsomstandigheden.
Er heeft ook een korte review plaatsgevonden .
De bronnen voor de test - het project zelf, het maken van tabellen in de TEST-database en het vullen van deze tabellen met gegevens bevinden zich .
Ook in deze repository, in de map Plannen, staan plannen voor het uitvoeren van query's met OR-voorwaarden.
Bron: www.habr.com