Η LINQ εισήχθη στο .NET ως μια ισχυρή νέα γλώσσα χειρισμού δεδομένων. Το LINQ to SQL ως μέρος του σάς επιτρέπει να επικοινωνείτε με το DBMS αρκετά άνετα χρησιμοποιώντας, για παράδειγμα, το Entity Framework. Ωστόσο, χρησιμοποιώντας το αρκετά συχνά, οι προγραμματιστές ξεχνούν να εξετάσουν τι είδους ερώτημα SQL θα δημιουργήσει ο πάροχος με δυνατότητα αναζήτησης, στην περίπτωσή σας, το Entity Framework.
Ας δούμε δύο βασικά σημεία με ένα παράδειγμα.
Για να γίνει αυτό, στον SQL Server θα δημιουργήσουμε μια δοκιμή βάσης δεδομένων και σε αυτήν θα δημιουργήσουμε δύο πίνακες χρησιμοποιώντας το ακόλουθο ερώτημα:
Δημιουργία πινάκων
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
Τώρα ας συμπληρώσουμε τον πίνακα 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
Ας συμπληρώσουμε τον πίνακα Πελάτης με τον ίδιο τρόπο χρησιμοποιώντας το ακόλουθο σενάριο:
Συμπλήρωση του πίνακα πελατών
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
Έτσι, πήραμε δύο πίνακες, ο ένας από τους οποίους έχει περισσότερες από 1 εκατομμύριο σειρές δεδομένων και ο άλλος έχει περισσότερες από 10 εκατομμύρια σειρές δεδομένων.
Τώρα στο Visual Studio, πρέπει να δημιουργήσετε ένα δοκιμαστικό έργο Visual C# Console App (.NET Framework):
Στη συνέχεια, πρέπει να προσθέσετε μια βιβλιοθήκη για να αλληλεπιδρά το Entity Framework με τη βάση δεδομένων.
Για να το προσθέσετε, κάντε δεξί κλικ στο έργο και επιλέξτε Manage NuGet Packages από το μενού περιβάλλοντος:
Στη συνέχεια, στο παράθυρο διαχείρισης πακέτων NuGet που εμφανίζεται, στο πλαίσιο αναζήτησης, πληκτρολογήστε τη λέξη "Entity Framework" και επιλέξτε το πακέτο Entity Framework και εγκαταστήστε το:
Στη συνέχεια, στο αρχείο App.config, αφού κλείσετε το στοιχείο configSections, προσθέστε το ακόλουθο μπλοκ:
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
Στο ConnectString πρέπει να εισαγάγετε τη συμβολοσειρά σύνδεσης.
Τώρα ας δημιουργήσουμε 3 διεπαφές σε ξεχωριστά αρχεία:
- Υλοποίηση της διεπαφής IBaseEntityID
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - Υλοποίηση της διεπαφής IBaseEntityName
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - Υλοποίηση της διεπαφής IBaseNameInsertUTCDate
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
Και σε ένα ξεχωριστό αρχείο, θα δημιουργήσουμε μια βασική κλάση BaseEntity για τις δύο οντότητες μας, η οποία θα περιλαμβάνει κοινά πεδία:
Υλοποίηση της βασικής κλάσης BaseEntity
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
Στη συνέχεια, σε ξεχωριστά αρχεία, θα δημιουργήσουμε τις δύο οντότητές μας:
- Εφαρμογή κλάσης αναφοράς
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Υλοποίηση της κλάσης Πελατών
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; } } }
Τώρα ας δημιουργήσουμε ένα περιβάλλον UserContext σε ένα ξεχωριστό αρχείο:
Υλοποίηση της κλάσης 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; }
}
}
Έχουμε μια έτοιμη λύση για τη διεξαγωγή δοκιμών βελτιστοποίησης με LINQ σε SQL μέσω EF για MS SQL Server:
Τώρα στο αρχείο Program.cs, πληκτρολογήστε τον ακόλουθο κωδικό:
Αρχείο 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();
}
}
}
}
Στη συνέχεια, ας εκτελέσουμε το έργο μας.
Στο τέλος της εργασίας, στην κονσόλα θα εμφανιστούν τα εξής:
Δημιουργήθηκε ερώτημα SQL
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])
Δηλαδή, σε γενικές γραμμές, το ερώτημα LINQ δημιούργησε ένα ερώτημα SQL στο DBMS του MS SQL Server αρκετά καλά.
Τώρα ας αλλάξουμε τη συνθήκη AND σε OR στο ερώτημα LINQ:
Ερώτημα LINQ
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 };
Ας τρέξουμε ξανά την εφαρμογή μας.
Η εκτέλεση θα διακοπεί με ένα σφάλμα που σχετίζεται με το χρόνο εκτέλεσης της εντολής που υπερβαίνει τα 30 δευτερόλεπτα:
Αν κοιτάξετε ποιο ερώτημα δημιουργήθηκε από το LINQ:
, τότε μπορείτε να βεβαιωθείτε ότι η επιλογή γίνεται μέσω του καρτεσιανού γινόμενου δύο συνόλων (πίνακες):
Δημιουργήθηκε ερώτημα SQL
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]
Ας ξαναγράψουμε το ερώτημα LINQ ως εξής:
Βελτιστοποιημένο ερώτημα 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 });
Στη συνέχεια λαμβάνουμε το ακόλουθο ερώτημα SQL:
Ερώτημα SQL
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]
Δυστυχώς, στα ερωτήματα LINQ μπορεί να υπάρχει μόνο μία συνθήκη σύνδεσης, επομένως είναι δυνατό να γίνει ένα ισοδύναμο ερώτημα μέσω δύο ερωτημάτων για κάθε συνθήκη, ακολουθούμενο από την ένωσή τους μέσω της Ένωσης για την κατάργηση των διπλότυπων μεταξύ των σειρών.
Ναι, τα ερωτήματα θα είναι γενικά μη ισοδύναμα, δεδομένου ότι ενδέχεται να επιστραφούν πλήρεις διπλότυπες σειρές. Ωστόσο, στην πραγματική ζωή, δεν χρειάζονται πλήρεις διπλές γραμμές και προσπαθούν να απαλλαγούν από αυτές.
Τώρα ας συγκρίνουμε τα σχέδια εκτέλεσης αυτών των δύο ερωτημάτων:
- για CROSS JOIN, ο μέσος χρόνος εκτέλεσης είναι 195 δευτερόλεπτα:
- για το INNER JOIN-UNION ο μέσος χρόνος εκτέλεσης είναι μικρότερος από 24 δευτερόλεπτα:
Όπως φαίνεται από τα αποτελέσματα, για δύο πίνακες με εκατομμύρια εγγραφές, το βελτιστοποιημένο ερώτημα LINQ είναι πολλές φορές ταχύτερο από το μη βελτιστοποιημένο.
Για την παραλλαγή με AND στις συνθήκες ενός ερωτήματος LINQ της φόρμας:
Ερώτημα LINQ
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 };
σχεδόν πάντα θα δημιουργείται ένα σωστό ερώτημα SQL, το οποίο θα εκτελείται κατά μέσο όρο για περίπου 1 δευτερόλεπτο:
Επίσης, για χειρισμούς LINQ to Objects αντί για ερώτημα στην προβολή:
Ερώτημα LINQ (1η επιλογή)
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 };
Μπορείτε να χρησιμοποιήσετε ένα ερώτημα όπως:
Ερώτημα LINQ (2η επιλογή)
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 };
όπου:
Ορισμός δύο συστοιχιών
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" } };
, και ο τύπος Para ορίζεται ως εξής:
Ορισμός τύπου para
class Para
{
public int Key1, Key2;
public string Data;
}
Έτσι, έχουμε εξετάσει ορισμένες πτυχές στη βελτιστοποίηση των ερωτημάτων LINQ στον MS SQL Server.
Δυστυχώς, ακόμη και οι έμπειροι και κορυφαίοι προγραμματιστές .NET ξεχνούν ότι είναι απαραίτητο να κατανοήσουν τι κάνουν οι οδηγίες που χρησιμοποιούν στα παρασκήνια. Διαφορετικά, γίνονται διαμορφωτές και μπορούν να τοποθετήσουν μια ωρολογιακή βόμβα στο μέλλον, τόσο κατά την κλιμάκωση μιας λύσης λογισμικού, όσο και με μικρές αλλαγές στις εξωτερικές περιβαλλοντικές συνθήκες.
Υπήρχε επίσης μια μικρή κριτική .
Πηγές για τη δοκιμή - βρίσκεται το ίδιο το έργο, η δημιουργία πινάκων στη βάση δεδομένων TEST, καθώς και η πλήρωση αυτών των πινάκων με δεδομένα .
Επίσης σε αυτό το αποθετήριο στο φάκελο Plans υπάρχουν σχέδια για την εκτέλεση ερωτημάτων με συνθήκες OR.
Πηγή: www.habr.com