Ich möchte eine komplexe Linq-Abfrage in eine Stored Procedure konvertieren

Question

Ich habe also eine MS SQL-Datenbank, die ich ausschließlich mit LINQ to Entities verwendet habe, weil ich LINQ und nicht SQL verstehe. Ich spiele mit dem Gedanken, mehr SQL zu lernen und möglicherweise gespeicherte Prozeduren zu verwenden, um die Dinge zu beschleunigen. Allerdings habe ich keine Ahnung, wie man das Folgende in SQL schreibt, also schaue ich mir an, was LinqPad als Ausgangspunkt generiert.

Die folgende Abfrage ermittelt erfolgreich die beliebtesten Titel, basierend darauf, wie oft der Titel in den letzten 7 Tagen in einem bestimmten "Kanal" oder in bestimmten Kanälen in die "Warteschlange" gestellt wurde, sowie die 3 wichtigsten "Akteure" dieses Titels, basierend auf ihrer Reihenfolge.

var dateStart = DateTime.Now.AddDays(-7);
var dateNow = DateTime.Now;
var channels = new[] { 1 };

var query = (from p in TitleQueues
                    where p.QueueTime >= dateStart
                          && p.QueueTime <= dateNow
                    group p by p.TitleId
                    into grouped
                    from t in Titles
                    let cast = (from tc in t.TitlePeoples
                    from td in tc.TitlePersonTitlePeopleDepartments
                    where td.Department.Type == "Actors"
                    orderby tc.Order
                    select new { tc.Person.Name, tc.Person.Id }).Take(3)
                    where t.Id == grouped.Key
                    from g in t.TitleSources
                    where
                        channels.Contains(g.TitleProviderId) && g.AvailableFrom <= dateNow &&
                        g.AvailableTo >= dateNow
                        && t.Rank != null
                    orderby grouped.Count() descending , t.Rank
                    select new {
                        t.Id,
                        t.Name,
                        t.ReleaseYear,
                        t.ShortSynopsis,
                        t.TitleTypeId,
                        t.MPAARating,
                        t.Runtime,
                        t.IMDbTop250Rank,
                        t.CombinedRating,
                        cast
                    }).Take(10);

                    query.Dump();

Dies ist das SQL, das LinqPad auf der Grundlage der obigen Linq-Abfrage erzeugt:

-- Region Parameters
DECLARE @p0 DateTime2 = '2011-12-28 12:13:29.4306979'
DECLARE @p1 DateTime2 = '2012-01-04 12:13:29.4306979'
DECLARE @p2 Int = 1
DECLARE @p3 DateTime2 = '2012-01-04 12:13:29.4306979'
DECLARE @p4 DateTime2 = '2012-01-04 12:13:29.4306979'
-- EndRegion
SELECT TOP (10) [t2].[Id], [t2].[Name], [t2].[ReleaseYear], [t2].[ShortSynopsis], [t2].[TitleTypeId], [t2].[MPAARating], [t2].[Runtime], [t2].[IMDbTop250Rank], [t2].[CombinedRating]
FROM (
    SELECT [t0].[TitleId]
    FROM [TitleQueues] AS [t0]
    WHERE ([t0].[QueueTime] >= @p0) AND ([t0].[QueueTime] <= @p1)
    GROUP BY [t0].[TitleId]
    ) AS [t1]
CROSS JOIN [Titles] AS [t2]
CROSS JOIN [TitleSources] AS [t3]
WHERE ([t3].[TitleProviderId] IN (@p2)) AND ([t3].[AvailableFrom] <= @p3) AND ([t3].[AvailableTo] >= @p4) AND ([t2].[Rank] IS NOT NULL) AND ([t2].[Id] = [t1].[TitleId]) AND ([t3].[TitleId] = [t2].[Id])
ORDER BY (
    SELECT COUNT(*)
    FROM [TitleQueues] AS [t4]
    WHERE ([t1].[TitleId] = [t4].[TitleId]) AND ([t4].[QueueTime] >= @p0) AND ([t4].[QueueTime] <= @p1)
    ) DESC, [t2].[Rank]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 130323
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 127948
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 90578
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 129887
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 130546
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 121981
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 121957
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 125377
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 91239
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]
GO

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Actors'
DECLARE @x1 Int = 121903
-- EndRegion
SELECT TOP (3) [t3].[Name], [t3].[Id]
FROM [TitlePeople] AS [t0]
CROSS JOIN [TitlePeopleDepartments] AS [t1]
INNER JOIN [Departments] AS [t2] ON [t2].[Id] = [t1].[DepartmentId]
INNER JOIN [People] AS [t3] ON [t3].[Id] = [t0].[PersonId]
WHERE ([t2].[Type] = @p0) AND ([t0].[TitleId] = @x1) AND ([t1].[TitlePersonId] = [t0].[Id])
ORDER BY [t0].[Order]

Ist dies also ein guter Ausgangspunkt für eine gespeicherte Prozedur? Wenn Sie eine Prozedur dafür schreiben würden, wie würde sie aussehen?

Answer 1

Versuchen Sie, wenn möglich, den SQL Profiler zu verwenden, um das tatsächliche SQL zu beobachten, das an die Datenbank gesendet wird. Vielleicht finden Sie so einen besseren Ausgangspunkt.