A LINQ a .NET-et hatékony új adatkezelési nyelvként lépett fel. A LINQ to SQL részeként lehetővé teszi, hogy kényelmesen kommunikáljon egy DBMS-sel, például az Entity Framework használatával. Azonban gyakran használva a fejlesztők elfelejtik megnézni, hogy a lekérdezhető szolgáltató, az Ön esetében az Entity Framework milyen SQL lekérdezést generál.
Nézzünk meg két fő pontot egy példa segítségével.
Ehhez hozzon létre egy tesztadatbázist az SQL Serverben, és hozzon létre benne két táblát a következő lekérdezéssel:
Táblázatok készítése
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
Most töltsük fel a Ref táblát a következő szkript futtatásával:
A Ref táblázat kitöltése
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
Hasonló módon töltsük ki az Ügyfél táblát a következő szkript segítségével:
Az Ügyfél tábla feltöltése
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
Így két táblát kaptunk, amelyek közül az egyik több mint 1 millió, a másik több mint 10 millió sornyi adatot tartalmaz.
Most a Visual Studioban létre kell hoznia egy teszt Visual C# Console App (.NET-keretrendszer) projektet:
Ezután hozzá kell adnia egy könyvtárat az Entity Framework számára az adatbázissal való interakcióhoz.
Hozzáadásához kattintson a jobb gombbal a projektre, és válassza ki a NuGet-csomagok kezelése a helyi menüből:
Ezután a megjelenő NuGet csomagkezelő ablakban írja be az „Entity Framework” szót a keresőablakba, válassza ki az Entity Framework csomagot, és telepítse:
Ezután az App.config fájlban a configSections elem bezárása után hozzá kell adnia a következő blokkot:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
A connectString mezőben meg kell adnia a kapcsolati karakterláncot.
Most hozzunk létre 3 interfészt külön fájlokban:
- Az IBaseEntityID felület megvalósítása
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Az IBaseEntityName felület megvalósítása
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Az IBaseNameInsertUTCDate interfész megvalósítása
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
És egy külön fájlban létrehozunk egy BaseEntity alaposztályt a két entitásunkhoz, amely közös mezőket tartalmaz:
A BaseEntity alaposztály megvalósítása
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Ezután a két entitásunkat külön fájlban hozzuk létre:
- A Ref osztály megvalósítása
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Az Ügyfél osztály megvalósítása
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; } } }
Most hozzunk létre egy UserContext kontextust egy külön fájlban:
A UserContex osztály megvalósítása
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; }
}
}
Kaptunk egy kész megoldást a LINQ to SQL optimalizálási tesztek elvégzésére az EF for MS SQL Server rendszeren keresztül:
Most írja be a következő kódot a Program.cs fájlba:
Program.cs fájl
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();
}
}
}
}
Ezután indítsuk el projektünket.
A munka végén a következők jelennek meg a konzolon:
Generált SQL lekérdezés
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])
Azaz általában a LINQ lekérdezés elég jól generált egy SQL lekérdezést az MS SQL Server DBMS-hez.
Most változtassuk meg az ÉS feltételt OR-ra a LINQ lekérdezésben:
LINQ lekérdezés
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 };
És indítsuk újra az alkalmazásunkat.
A végrehajtás hibával összeomlik, mivel a parancs végrehajtási ideje meghaladja a 30 másodpercet:
Ha megnézi a LINQ által generált lekérdezést:
, akkor meggyőződhet arról, hogy a kiválasztás két halmaz (táblázat) derékszögű szorzatán keresztül történik:
Generált SQL lekérdezés
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]
Írjuk át a LINQ lekérdezést a következőképpen:
Optimalizált LINQ lekérdezés
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 });
Ekkor a következő SQL lekérdezést kapjuk:
SQL lekérdezés
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]
Sajnos a LINQ-lekérdezésekben csak egy összekapcsolási feltétel lehet, így itt lehetőség van egy ekvivalens lekérdezésre minden feltételhez két lekérdezéssel, majd az Unión keresztül kombinálva a duplikátumok eltávolításához a sorok között.
Igen, a lekérdezések általában nem egyenértékűek, figyelembe véve, hogy teljes ismétlődő sorok jelenhetnek meg. A való életben azonban nincs szükség teljes ismétlődő sorokra, és az emberek megpróbálnak megszabadulni tőlük.
Hasonlítsuk össze e két lekérdezés végrehajtási terveit:
- A CROSS JOIN esetében az átlagos végrehajtási idő 195 másodperc:
- az INNER JOIN-UNION esetében az átlagos végrehajtási idő kevesebb, mint 24 másodperc:
Amint az eredményekből látható, két millió rekordot tartalmazó tábla esetén az optimalizált LINQ lekérdezés sokszor gyorsabb, mint az optimalizálatlan.
A feltételekben az ÉS opcióhoz egy LINQ-lekérdezés a következő formában:
LINQ lekérdezés
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 };
Szinte mindig a megfelelő SQL lekérdezés jön létre, amely átlagosan körülbelül 1 másodperc alatt fut le:
A LINQ to Objects manipulációkhoz is olyan lekérdezések helyett, mint például:
LINQ-lekérdezés (1. lehetőség)
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 };
használhat olyan lekérdezést, mint:
LINQ-lekérdezés (2. lehetőség)
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 };
ahol:
Két tömb meghatározása
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" } };
, és a Para típus meghatározása a következő:
Para Type Definition
class Para
{
public int Key1, Key2;
public string Data;
}
Így megvizsgáltunk néhány szempontot a LINQ lekérdezések MS SQL Serverre való optimalizálása során.
Sajnos még a tapasztalt és vezető .NET-fejlesztők is elfelejtik, hogy meg kell érteniük, mit tesznek az általuk használt utasítások a színfalak mögött. Ellenkező esetben konfigurátorokká válnak, és a jövőben időzített bombát ültethetnek a szoftveres megoldás méretezésekor és a külső környezeti feltételek kisebb változtatásaival is.
Egy rövid áttekintést is végeztek .
A teszt forrásai - maga a projekt, táblák létrehozása a TEST adatbázisban, valamint ezeknek a tábláknak az adatokkal való kitöltése található .
Szintén ebben a lerakatban, a Tervek mappában vannak tervek a VAGY feltételekkel rendelkező lekérdezések végrehajtására.
Forrás: will.com