LINQ ynfierd .NET as in krêftige nije gegevens manipulaasje taal. LINQ nei SQL as ûnderdiel dêrfan kinne jo frij maklik kommunisearje mei in DBMS mei help fan, bygelyks, Entity Framework. Troch it lykwols frij faak te brûken, ferjitte ûntwikkelders om te sjen nei hokker soarte SQL-query de queryable provider, yn jo gefal Entity Framework, sil generearje.
Litte wy nei twa haadpunten sjen mei in foarbyld.
Om dit te dwaan, meitsje in testdatabase yn SQL Server, en meitsje twa tabellen dêryn mei de folgjende query:
It meitsjen fan tabellen
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
Litte wy no de Ref-tabel ynfolje troch it folgjende skript út te fieren:
It ynfoljen fan de Ref tafel
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
Litte wy de klanttabel op deselde manier folje mei it folgjende skript:
Befolking fan de klant tabel
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
Sa krigen wy twa tabellen, wêrfan ien mear as 1 miljoen rigen gegevens hat, en de oare hat mear as 10 miljoen rigen gegevens.
No yn Visual Studio moatte jo in test Visual C# Console App (.NET Framework) projekt meitsje:
Dêrnei moatte jo in bibleteek tafoegje foar it Entity Framework om te ynteraksje mei de databank.
Om it ta te foegjen, klikje jo mei de rechtermuisknop op it projekt en selektearje NuGet-pakketten beheare út it kontekstmenu:
Fier dan yn it finster fan NuGet pakketbehear dat ferskynt it wurd "Entity Framework" yn yn it sykfinster en selektearje it Entity Framework-pakket en ynstallearje it:
Folgjende, yn 'e App.config-bestân, nei it sluten fan it elemint configSections, moatte jo it folgjende blok tafoegje:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Yn connectionString moatte jo de ferbiningsstring ynfiere.
Litte wy no 3 ynterfaces meitsje yn aparte bestannen:
- It ymplementearjen fan de IBaseEntityID-ynterface
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementaasje fan de IBaseEntityName ynterface
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementaasje fan de IBaseNameInsertUTCDate-ynterface
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
En yn in apart bestân sille wy in basisklasse BaseEntity meitsje foar ús twa entiteiten, dy't mienskiplike fjilden sille omfetsje:
Implementaasje fan 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; }
}
}
Folgjende sille wy ús twa entiteiten oanmeitsje yn aparte bestannen:
- Útfiering fan de Ref klasse
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Útfiering fan de klant klasse
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; } } }
Litte wy no in UserContext-kontekst oanmeitsje yn in apart bestân:
Implementaasje fan 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; }
}
}
Wy krigen in klearmakke oplossing foar it útfieren fan optimisaasjetests mei LINQ nei SQL fia EF foar MS SQL Server:
Fier no de folgjende koade yn yn it Program.cs-bestân:
Program.cs triem
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();
}
}
}
}
Litte wy dan ús projekt lansearje.
Oan 'e ein fan it wurk sil it folgjende wurde werjûn op' e konsole:
Generearre 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 is, yn 't algemien, de LINQ-query generearre in SQL-query nei de MS SQL Server DBMS frij goed.
Litte wy no de EN-betingst feroarje nei OR yn 'e LINQ-query:
LINQ fraach
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 litte wy ús applikaasje opnij starte.
De útfiering sil crashe mei in flater fanwege de kommando-útfiertiid fan mear as 30 sekonden:
As jo nei de query sjogge dy't waard generearre troch LINQ:
, dan kinne jo derfoar soargje dat de seleksje bart troch it Cartesyske produkt fan twa sets (tabellen):
Generearre 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]
Litte wy de LINQ-query sa herskriuwe:
Optimalisearre 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 krije wy de folgjende 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]
Och, yn LINQ-fragen kin d'r mar ien join-betingst wêze, dus hjir is it mooglik om in lykweardige query te meitsjen mei twa queries foar elke betingst en se dan te kombinearjen fia Union om duplikaten tusken de rigen te ferwiderjen.
Ja, de fragen sille oer it generaal net lykweardich wêze, rekken hâldend mei dat folsleine dûbele rigen weromjûn wurde kinne. Yn it echte libben binne folsleine dûbele rigels lykwols net nedich en minsken besykje se kwyt te reitsjen.
Litte wy no de útfieringsplannen fan dizze twa fragen fergelykje:
- foar CROSS JOIN is de gemiddelde útfieringstiid 195 sekonden:
- foar INNER JOIN-UNION is de gemiddelde útfieringstiid minder dan 24 sekonden:
Lykas jo kinne sjen út 'e resultaten, foar twa tabellen mei miljoenen records, is de optimalisearre LINQ-query in protte kearen rapper dan de net-optimisearre.
Foar de opsje mei EN yn 'e betingsten, in LINQ-fraach fan it formulier:
LINQ fraach
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 };
De juste SQL-query sil hast altyd wurde generearre, dy't gemiddeld yn sawat 1 sekonde sil rinne:
Ek foar LINQ to Objects manipulaasjes ynstee fan in query lykas:
LINQ-fraach (1e opsje)
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 };
jo kinne in query brûke lykas:
LINQ-fraach (2e opsje)
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 };
wêr:
It definiearjen fan twa arrays
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 it Para-type wurdt as folget definiearre:
Para Type Definysje
class Para
{
public int Key1, Key2;
public string Data;
}
Sa hawwe wy guon aspekten ûndersocht by it optimalisearjen fan LINQ-fragen nei MS SQL Server.
Spitigernôch ferjitte sels betûfte en liedende .NET-ûntwikkelders dat se moatte begripe wat de ynstruksjes dy't se brûke dogge efter de skermen. Oars wurde se konfigurators en kinne yn 'e takomst in tiidbom plantsje, sawol by it skaaljen fan de software-oplossing as mei lytse feroaringen yn eksterne omjouwingsomstannichheden.
Der is ek in koarte besprek dien .
De boarnen foar de test - it projekt sels, it oanmeitsjen fan tabellen yn 'e TEST-databank, lykas it ynfoljen fan dizze tabellen mei gegevens lizze .
Ek yn dit repository, yn 'e map Plans, binne d'r plannen foar it útfieren fan queries mei OR-betingsten.
Boarne: www.habr.com