LINQ vstoupil do .NET jako nový výkonný jazyk pro manipulaci s daty. LINQ to SQL jako jeho součást umožňuje poměrně pohodlně komunikovat s DBMS pomocí např. Entity Framework. Při jeho poměrně častém používání se však vývojáři zapomínají dívat na to, jaký SQL dotaz dotazovatelný poskytovatel vygeneruje, ve vašem případě Entity Framework.
Podívejme se na dva hlavní body s příkladem.
Za tímto účelem v SQL Server vytvoříme databázi Test a v ní vytvoříme dvě tabulky pomocí následujícího dotazu:
Vytvoření tabulky
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
Nyní naplníme tabulku Ref spuštěním následujícího skriptu:
Vyplnění tabulky č.j
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
Vyplňte tabulku Zákazník stejným způsobem pomocí následujícího skriptu:
Vyplnění tabulky Zákazník
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
Získali jsme tedy dvě tabulky, z nichž jedna má více než 1 milion řádků dat a druhá má více než 10 milionů řádků dat.
Nyní ve Visual Studiu musíte vytvořit testovací projekt Visual C# Console App (.NET Framework):
Dále musíte přidat knihovnu pro Entity Framework pro interakci s databází.
Chcete-li jej přidat, klikněte pravým tlačítkem na projekt a z kontextové nabídky vyberte Spravovat balíčky NuGet:
Poté v okně správy balíčků NuGet, které se zobrazí, zadejte do vyhledávacího pole slovo „Entity Framework“ a vyberte balíček Entity Framework a nainstalujte jej:
Dále do souboru App.config po zavření prvku configSections přidejte následující blok:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Do connectionString musíte zadat připojovací řetězec.
Nyní vytvoříme 3 rozhraní v samostatných souborech:
- Implementace rozhraní IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementace rozhraní IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementace rozhraní IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
A v samostatném souboru vytvoříme základní třídu BaseEntity pro naše dvě entity, která bude obsahovat společná pole:
Implementace základní třídy 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ále v samostatných souborech vytvoříme naše dvě entity:
- Implementace referenční třídy
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementace třídy Zákazník
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; } } }
Nyní vytvoříme kontext UserContext v samostatném souboru:
Implementace třídy UserContex
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; }
}
}
Získali jsme hotové řešení pro provádění optimalizačních testů s LINQ to SQL přes EF pro MS SQL Server:
Nyní do souboru Program.cs zadejte následující kód:
Soubor 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<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();
}
}
}
}
Dále spusťte náš projekt.
Na konci práce se na konzole zobrazí následující:
Generovaný SQL dotaz
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])
To znamená, že obecně dotaz LINQ generoval SQL dotaz do MS SQL Server DBMS docela dobře.
Nyní změňme podmínku AND na OR v dotazu LINQ:
LINQ dotaz
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 };
Spusťte naši aplikaci znovu.
Provádění se zhroutí s chybou související s dobou provádění příkazu přesahující 30 sekund:
Pokud se podíváte na to, který dotaz vygeneroval LINQ:
, pak se můžete ujistit, že výběr probíhá prostřednictvím kartézského součinu dvou množin (tabulek):
Generovaný SQL dotaz
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]
Přepišme dotaz LINQ takto:
Optimalizovaný dotaz LINQ
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 });
Poté dostaneme následující SQL dotaz:
SQL dotaz
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]
Bohužel, v dotazech LINQ může být pouze jedna podmínka spojení, proto je možné vytvořit ekvivalentní dotaz prostřednictvím dvou dotazů pro každou podmínku, po kterém následuje jejich sjednocení prostřednictvím Union, aby se odstranily duplikáty mezi řádky.
Ano, dotazy budou obecně neekvivalentní, protože mohou být vráceny úplné duplicitní řádky. V reálném životě však nejsou úplné duplicitní řádky potřeba a snaží se jich zbavit.
Nyní porovnejme plány provádění těchto dvou dotazů:
- pro CROSS JOIN je průměrná doba provedení 195 sekund:
- pro INNER JOIN-UNION je průměrná doba provedení kratší než 24 sekund:
Jak je z výsledků patrné, pro dvě tabulky s miliony záznamů je optimalizovaný LINQ dotaz mnohonásobně rychlejší než neoptimalizovaný.
Pro variantu s AND v podmínkách LINQ dotazu formuláře:
LINQ dotaz
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 };
téměř vždy se vygeneruje správný SQL dotaz, který poběží v průměru asi 1 sekundu:
Také pro manipulace LINQ to Objects namísto dotazování na pohled:
LINQ dotaz (1. možnost)
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 };
Můžete použít dotaz jako:
LINQ dotaz (2. možnost)
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 };
kde:
Definování dvou polí
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" } };
a typ Para je definován takto:
Definice typu para
class Para
{
public int Key1, Key2;
public string Data;
}
Zvážili jsme tedy některé aspekty při optimalizaci LINQ dotazů na MS SQL Server.
Bohužel i zkušení a přední .NET vývojáři zapomínají, že je nutné rozumět tomu, co návody, které používají, dělají v zákulisí. V opačném případě se z nich stanou konfigurátory a mohou v budoucnu položit časovanou bombu, a to jak při škálování softwarového řešení, tak při drobných změnách vnějších podmínek prostředí.
Nechyběla ani malá recenze .
Zdroje pro test - samotný projekt, vytváření tabulek v databázi TEST, stejně jako plnění těchto tabulek daty se nachází .
Také v tomto úložišti ve složce Plány jsou plány pro provádění dotazů s podmínkami NEBO.
Zdroj: www.habr.com