Блог

У меня проблемы с производительностью при использовании нескольких объединений в T-SQL, и если кто-нибудь может помочь мне превратить эти объединения в подзапросы, было бы неплохо.

Всякий раз, когда я пытаюсь изменить соединение в подзапрос, я теряю объявление имени для конкретной таблицы. Например, если я попытаюсь превратить объединение альбомов (которое является первым объединением в приведенном ниже коде) в подзапрос, я потеряю псевдоним «КАК a», а «a.Title КАК альбом» перестал работать, поэтому я понятия не имею, как это можно сделать. Если кто-нибудь даст мне пример, как это должно работать для одного из случаев, я полагаю, я смогу восстановить их все.

SQL

 SELECT
    t.TrackId, 
    t.[Name] AS Track, 
    a.Title AS Album, 
    aa.[Name] AS Artist, 
    p.[Name] AS Playlist,
    m.[Name] AS MediaType,
    il.UnitPrice AS InvoicePrice,
    CONCAT(c.FirstName, ' ', c.LastName) AS CustomerName,
    CONCAT(e.FirstName, ' ', e.LastName) AS ResponsibleEmployeeName
FROM dbo.Track AS t
INNER JOIN dbo.Album AS a
    ON t.AlbumId = a.AlbumId
INNER JOIN dbo.Artist AS aa
    ON a.ArtistId = aa.ArtistId
INNER JOIN dbo.PlaylistTrack AS plt
    ON t.TrackId = plt.TrackId
INNER JOIN dbo.Playlist AS p
    ON p.PlaylistId = plt.PlaylistId
INNER JOIN dbo.MediaType AS m
    ON t.MediaTypeId = m.MediaTypeId
INNER JOIN dbo.InvoiceLine AS il
    ON t.TrackId = il.TrackId
INNER JOIN dbo.Invoice AS i
    ON il.InvoiceId = i.InvoiceId
INNER JOIN dbo.Customer AS c
    ON i.CustomerId = c.CustomerId
INNER JOIN dbo.Employee AS e
    ON c.SupportRepId = e.EmployeeId
WHERE m.[Name] LIKE '%audio%'
ORDER BY t.[Name] ASC
 

Включение соединения в подзапрос может быть не лучшим решением

предполагая, что у вас уже есть индекс для внешнего ключа для каждой повторно введенной таблицы

 table  Artist index on column (ArtistId)
table  PlaylistTrack index on column (TrackId)
table  Playlist index on column (PlaylistId)
table  MediaType index  on column ( MediaTypeId )
.....
 

для повышения производительности убедитесь, что у вас есть index на

 table  track a composite index  on column  (AlbumId, TrackId, MediaTypeId )
table  Album  a cmposite index  on column ( AlbumId, ArtistId )
 

Какой подзапрос вы имеете в виду? Что-то вроде:

 SELECT t.TrackId, 
    t.[Name] AS Track, 
    (SELECT title FROM dbo.Album WHERE AlbumId = t.AlbumId) AS AlbumTitle
 

Этого не произойдет, если вы удалите соединение с Album , потому что вам нужна ссылка на альбом, чтобы перейти к исполнителю. Если вы хотите присоединиться к подзапросу, вы можете это сделать и сохранить псевдоним:

 SELECT
    t.TrackId, 
    t.[Name] AS Track, 
    a.Title AS Album, 
    aa.[Name] AS Artist, 
    p.[Name] AS Playlist,
    m.[Name] AS MediaType,
    il.UnitPrice AS InvoicePrice,
    CONCAT(c.FirstName, ' ', c.LastName) AS CustomerName,
    CONCAT(e.FirstName, ' ', e.LastName) AS ResponsibleEmployeeName
FROM dbo.Track AS t
INNER JOIN (SELECT * FROM dbo.Album) AS a
   ON t.AlbumId = a.AlbumId
   -- rest of joins
 

Но логически это точно то же самое, что у вас есть прямо сейчас, и в плане, сгенерированном оптимизатором запросов, не будет никакой разницы. Даже это:

 SELECT
    t.TrackId, 
    t.[Name] AS Track, 
    aa.Title AS Album, -- note change here
    aa.[Name] AS Artist, 
    p.[Name] AS Playlist,
    m.[Name] AS MediaType,
    il.UnitPrice AS InvoicePrice,
    CONCAT(c.FirstName, ' ', c.LastName) AS CustomerName,
    CONCAT(e.FirstName, ' ', e.LastName) AS ResponsibleEmployeeName
FROM dbo.Track AS t
INNER JOIN (SELECT alb.Title
                 , art.Name
                 , alb.AlbumId
              FROM dbo.Album alb
        INNER JOIN dbo.Artist art
                ON art.ArtistId = alb.ArtistID) AS aa
   ON t.AlbumId = aa.AlbumId
INNER JOIN dbo.PlaylistTrack AS plt
   ON t.TrackId = plt.TrackId
-- rest of them
 

Создаст точно такой же план. Возможно, мы переместили Album <-> Artist join в объединенный подзапрос, но, по сути, это все то же самое — внутренние объединения.

Ничто подобное не поможет вашей производительности. Что может вам помочь, так это создать индексы для этих таблиц. Если вы часто выполняете запросы такого типа, вы также можете создать индексированное представление, что-то вроде:

 CREATE VIEW BoughtTracks
WITH SCHEMABINDING
AS
SELECT
    il.InvoiceLineId, -- I'm guessing here, we need a unique ID
    t.TrackId, 
    t.[Name] AS Track, 
    a.Title AS Album, 
    aa.[Name] AS Artist, 
    p.[Name] AS Playlist,
    m.[Name] AS MediaType,
    il.UnitPrice AS InvoicePrice,
    CONCAT(c.FirstName, ' ', c.LastName) AS CustomerName,
    CONCAT(e.FirstName, ' ', e.LastName) AS ResponsibleEmployeeName
FROM dbo.Track AS t
INNER JOIN dbo.Album AS a
    ON t.AlbumId = a.AlbumId
INNER JOIN dbo.Artist AS aa
    ON a.ArtistId = aa.ArtistId
INNER JOIN dbo.PlaylistTrack AS plt
    ON t.TrackId = plt.TrackId
INNER JOIN dbo.Playlist AS p
    ON p.PlaylistId = plt.PlaylistId
INNER JOIN dbo.MediaType AS m
    ON t.MediaTypeId = m.MediaTypeId
INNER JOIN dbo.InvoiceLine AS il
    ON t.TrackId = il.TrackId
INNER JOIN dbo.Invoice AS i
    ON il.InvoiceId = i.InvoiceId
INNER JOIN dbo.Customer AS c
    ON i.CustomerId = c.CustomerId
INNER JOIN dbo.Employee AS e
    ON c.SupportRepId = e.EmployeeId
WHERE m.[Name] LIKE '%audio%'

CREATE UNIQUE CLUSTERED INDEX ux ON BoughtTracks (InvoiceLineId);
 

Это замедлит вставки в эти таблицы, но выбор вкл BoughtTracks будет быстрым (вы также можете создать дополнительные индексы в этом представлении), что-то вроде:

 SELECT * 
  FROM BoughtTracks WITH (NOEXPAND) -- NOEXPAND is important
 WHERE CustomerName = 'Joe Smith'
 

Может выполняться на порядки быстрее, чем ваш текущий запрос, в зависимости, конечно, от размера ваших данных. Особенно если вы создаете на нем индекс

 CREATE INDEX ix_CustomerName ON BoughtTracks (CustomerName) 
INCLUDE (...) -- maybe include some columns you know you will need when querying for CustomerName
WHERE (...) -- maybe there are alsways accompanying predicates when querying for CustomerName