LINQ vstúpil do .NET ako nový výkonný jazyk na manipuláciu s údajmi. LINQ to SQL ako jeho súčasť umožňuje celkom pohodlne komunikovať s DBMS napríklad pomocou Entity Framework. Pri jeho pomerne častom používaní sa však vývojári zabúdajú pozerať na to, aký SQL dotaz vygeneruje dopytovateľný poskytovateľ, vo vašom prípade Entity Framework.
Na príklade sa pozrime na dva hlavné body.
Ak to chcete urobiť, vytvorte testovaciu databázu na serveri SQL Server a vytvorte v nej dve tabuľky pomocou nasledujúceho dotazu:
Vytváranie tabuliek
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
Teraz vyplňte tabuľku Ref spustením nasledujúceho skriptu:
Vyplnenie tabuľky 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
Podobne vyplníme tabuľku Zákazník pomocou nasledujúceho skriptu:
Vypĺňa sa tabuľka 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
Dostali sme teda dve tabuľky, z ktorých jedna má viac ako 1 milión riadkov údajov a druhá má viac ako 10 miliónov riadkov údajov.
Teraz vo Visual Studiu musíte vytvoriť testovací projekt Visual C# Console App (.NET Framework):
Ďalej musíte pridať knižnicu pre Entity Framework na interakciu s databázou.
Ak ho chcete pridať, kliknite pravým tlačidlom myši na projekt a v kontextovej ponuke vyberte položku Spravovať balíky NuGet:
Potom v okne správy balíkov NuGet, ktoré sa zobrazí, zadajte do vyhľadávacieho okna slovo „Entity Framework“ a vyberte balík Entity Framework a nainštalujte ho:
Ďalej, v súbore App.config, po zatvorení prvku configSections, musíte pridať nasledujúci blok:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
V connectionString musíte zadať reťazec pripojenia.
Teraz vytvorte 3 rozhrania v samostatných súboroch:
- Implementácia rozhrania IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Implementácia rozhrania IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Implementácia rozhrania IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
A v samostatnom súbore vytvoríme základnú triedu BaseEntity pre naše dve entity, ktorá bude obsahovať spoločné polia:
Implementácia základnej triedy BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Ďalej vytvoríme naše dve entity v samostatných súboroch:
- Implementácia triedy Ref
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Implementácia triedy 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; } } }
Teraz vytvorte kontext UserContext v samostatnom súbore:
Implementácia triedy 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; }
}
}
Dostali sme hotové riešenie na vykonávanie optimalizačných testov s LINQ to SQL cez EF pre MS SQL Server:
Teraz zadajte nasledujúci kód do súboru Program.cs:
Súbor 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();
}
}
}
}
Ďalej spustíme náš projekt.
Na konci práce sa na konzole zobrazí nasledovné:
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 vo všeobecnosti dotaz LINQ vygeneroval dotaz SQL do MS SQL Server DBMS celkom dobre.
Teraz zmeňme podmienku AND na OR v dotaze 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 };
A opäť spustíme našu aplikáciu.
Vykonávanie zlyhá s chybou v dôsledku času vykonávania príkazu presahujúceho 30 sekúnd:
Ak sa pozriete na dotaz, ktorý vygeneroval LINQ:
, potom sa môžete uistiť, že výber prebieha prostredníctvom karteziánskeho súčinu dvoch množín (tabuľiek):
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]
Prepíš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 });
Potom dostaneme nasledujúci 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žiaľ, v dotazoch LINQ môže existovať iba jedna podmienka spojenia, takže tu je možné vytvoriť ekvivalentný dotaz pomocou dvoch dotazov pre každú podmienku a potom ich skombinovať cez Union, aby sa odstránili duplikáty medzi riadkami.
Áno, dopyty budú vo všeobecnosti neekvivalentné, berúc do úvahy, že sa môžu vrátiť úplné duplicitné riadky. V reálnom živote však nie sú potrebné úplné duplicitné čiary a ľudia sa ich snažia zbaviť.
Teraz porovnajme plány vykonávania týchto dvoch dotazov:
- pre CROSS JOIN je priemerný čas vykonania 195 sekúnd:
- pre INNER JOIN-UNION je priemerný čas vykonania menej ako 24 sekúnd:
Ako môžete vidieť z výsledkov, pre dve tabuľky s miliónmi záznamov je optimalizovaný dotaz LINQ mnohonásobne rýchlejší ako ten neoptimalizovaný.
Pre možnosť s AND v podmienkach dopyt LINQ vo formulári:
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 };
Takmer vždy sa vygeneruje správny SQL dotaz, ktorý sa spustí v priemere asi za 1 sekundu:
Tiež pre manipulácie LINQ to Objects namiesto dotazu, ako napríklad:
LINQ dotaz (1. možnosť)
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žiť dotaz ako:
LINQ dotaz (2. možnosť)
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:
Definovanie dvoch 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 definovaný takto:
Definícia typu para
class Para
{
public int Key1, Key2;
public string Data;
}
Preto sme preskúmali niektoré aspekty pri optimalizácii LINQ dotazov na MS SQL Server.
Bohužiaľ, aj skúsení a poprední .NET vývojári zabúdajú, že musia rozumieť tomu, čo inštrukcie, ktoré používajú, robia v zákulisí. V opačnom prípade sa stanú konfigurátormi a môžu v budúcnosti zasadiť časovanú bombu ako pri škálovaní softvérového riešenia, tak aj pri menších zmenách vonkajších podmienok prostredia.
Uskutočnil sa aj krátky prehľad .
Zdroje pre test - samotný projekt, vytváranie tabuliek v databáze TEST, ako aj napĺňanie týchto tabuliek údajmi sa nachádzajú .
Aj v tomto úložisku sa v priečinku Plány nachádzajú plány na vykonávanie dotazov s podmienkami ALEBO.
Zdroj: hab.com