LINQ tuli .NET:iin tehokkaaksi uudeksi tiedonkäsittelykieleksi. LINQ to SQL osana sitä mahdollistaa varsin kätevän kommunikoinnin DBMS:n kanssa käyttämällä esimerkiksi Entity Frameworkia. Käyttämällä sitä melko usein kehittäjät kuitenkin unohtavat katsoa, millaisen SQL-kyselyn kyselyn tarjoaja, sinun tapauksessasi Entity Framework, luo.
Tarkastellaan kahta pääkohtaa esimerkin avulla.
Voit tehdä tämän luomalla testitietokanta SQL Serverissä ja luomalla siihen kaksi taulukkoa käyttämällä seuraavaa kyselyä:
Taulukoiden luominen
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
Täytä nyt Ref-taulukko suorittamalla seuraava komentosarja:
Ref-taulukon täyttäminen
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
Täytetään samalla tavalla Asiakastaulukko seuraavalla skriptillä:
Asiakastaulukon täyttäminen
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
Näin ollen saimme kaksi taulukkoa, joista toisessa on yli miljoona riviä ja toisessa yli 1 miljoonaa riviä tietoja.
Nyt Visual Studiossa sinun on luotava testi Visual C# Console App (.NET Framework) -projekti:
Seuraavaksi sinun on lisättävä Entity Frameworkille kirjasto, jotta se voi olla vuorovaikutuksessa tietokannan kanssa.
Lisää se napsauttamalla projektia hiiren kakkospainikkeella ja valitsemalla pikavalikosta Manage NuGet Packages:
Kirjoita sitten näkyviin tulevaan NuGet-pakettien hallintaikkunaan hakuikkunaan sana "Entity Framework" ja valitse Entity Framework -paketti ja asenna se:
Seuraavaksi App.config-tiedostoon, kun olet sulkenut configSections-elementin, sinun on lisättävä seuraava lohko:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Yhteysmerkkijonoon on syötettävä yhteysmerkkijono.
Luodaan nyt 3 käyttöliittymää erillisiin tiedostoihin:
- IBaseEntityID-rajapinnan käyttöönotto
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - IBaseEntityName-rajapinnan käyttöönotto
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - IBaseNameInsertUTCDate-rajapinnan toteutus
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Ja erillisessä tiedostossa luomme kahdelle entiteetillemme perusluokan BaseEntity, joka sisältää yhteiset kentät:
Perusluokan BaseEntity toteutus
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Seuraavaksi luomme kaksi entiteettiämme erillisiin tiedostoihin:
- Ref-luokan toteutus
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Asiakasluokan toteutus
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; } } }
Luodaan nyt UserContext-konteksti erilliseen tiedostoon:
UserContex-luokan toteutus
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; }
}
}
Saimme valmiin ratkaisun optimointitestien suorittamiseen LINQ:lla SQL:ään EF:n kautta MS SQL Serverille:
Kirjoita seuraava koodi Program.cs-tiedostoon:
Program.cs-tiedosto
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();
}
}
}
}
Aloitetaan seuraavaksi projektimme.
Työn päätyttyä konsolissa näytetään seuraavaa:
Luotu SQL-kysely
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])
Eli yleensä LINQ-kysely loi SQL-kyselyn MS SQL Server DBMS:ään melko hyvin.
Muutetaan nyt AND-ehto OR:ksi LINQ-kyselyssä:
LINQ-kysely
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 };
Ja käynnistetään sovelluksemme uudelleen.
Suoritus kaatuu virheellä, koska komennon suoritusaika ylittää 30 sekuntia:
Jos katsot LINQ:n luomaa kyselyä:
, voit varmistaa, että valinta tapahtuu kahden joukon (taulukon) suorakulmaisella tulolla:
Luotu SQL-kysely
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]
Kirjoitetaan LINQ-kysely uudelleen seuraavasti:
Optimoitu LINQ-kysely
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 });
Sitten saamme seuraavan SQL-kyselyn:
SQL-kysely
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]
Valitettavasti LINQ-kyselyissä voi olla vain yksi liitosehto, joten tässä on mahdollista tehdä vastaava kysely käyttämällä kahta kyselyä kullekin ehdolle ja yhdistämällä ne sitten Unionin kautta kaksoiskappaleiden poistamiseksi rivien joukosta.
Kyllä, kyselyt eivät yleensä ole samanarvoisia, kun otetaan huomioon, että täydellisiä päällekkäisiä rivejä voidaan palauttaa. Todellisessa elämässä täydellisiä päällekkäisiä rivejä ei kuitenkaan tarvita ja ihmiset yrittävät päästä eroon niistä.
Verrataan nyt näiden kahden kyselyn toteutussuunnitelmia:
- CROSS JOINin keskimääräinen suoritusaika on 195 sekuntia:
- INNER JOIN-UNION:n keskimääräinen suoritusaika on alle 24 sekuntia:
Kuten tuloksista näkyy, kahdelle taulukolle, joissa on miljoonia tietueita, optimoitu LINQ-kysely on monta kertaa nopeampi kuin optimoimaton.
Jos ehdoissa on AND, LINQ-kysely muodossa:
LINQ-kysely
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 };
Oikea SQL-kysely luodaan melkein aina, ja se suoritetaan keskimäärin noin 1 sekunnissa:
Myös LINQ to Objects -manipulaatioille kyselyn sijaan, kuten:
LINQ-kysely (1. vaihtoehto)
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 };
voit käyttää kyselyä kuten:
LINQ-kysely (2. vaihtoehto)
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 };
jos:
Kahden taulukon määrittäminen
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" } };
, ja Para-tyyppi määritellään seuraavasti:
Para-tyypin määritelmä
class Para
{
public int Key1, Key2;
public string Data;
}
Siksi tutkimme joitain näkökohtia LINQ-kyselyiden optimoinnissa MS SQL Serverille.
Valitettavasti jopa kokeneet ja johtavat .NET-kehittäjät unohtavat, että heidän on ymmärrettävä, mitä heidän käyttämänsä ohjeet tekevät kulissien takana. Muuten niistä tulee konfiguraattoreita ja ne voivat istuttaa aikapommin tulevaisuudessa sekä ohjelmistoratkaisua skaalattaessa että pienillä muutoksilla ulkoisissa ympäristöolosuhteissa.
Myös lyhyt katsaus tehtiin .
Testin lähteet - itse projekti, taulukoiden luominen TEST-tietokantaan sekä näiden taulukoiden täyttäminen tiedoilla sijaitsevat .
Myös tässä arkistossa Suunnitelmat-kansiossa on suunnitelmia kyselyjen suorittamiseksi TAI-ehdoilla.
Lähde: will.com