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:
.
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