Соедините 4 разных стола за одним столом

Question

У меня есть таблица 1, где мне нужно объединиться с другими 4 таблицами. Могу ли я написать тот же запрос, используя подзапросы? Потому что, если я использую запрос ниже, это займет много времени (более 40 минут) для запуска. Есть ли другой способ для этого?

select T1.ID as IID, T2.Prokey as Pro, T3.pname as IType, T4.pname as Status, T5.pname as Res
from table1 T1
INNER JOIN Table2 T2 on T1.pro = T2.ID
INNER JOIN table3 T3 on T1.itype =T3.ID
INNER JOIN table4 T4 on T1.itatus = T4.ID
INNER JOIN Table5 T5 on T1.Res = T5.ID
group by T2.ID, convert(nvarchar(max), T2.ID), T1.ID, T1.Pkey, T3.pname, T4.pname, T5.pname

Answer 1

Вот оператор SQL:

-- CREATE TABLE 1 INDEXES 

CREATE NONCLUSTERED INDEX IX_Table1_pro
ON Table1 (pro) 

CREATE NONCLUSTERED INDEX IX_Table1_itype 
ON Table1 (itype) 

CREATE NONCLUSTERED INDEX IX_Table1_itatus
ON Table1 (itatus) 

CREATE NONCLUSTERED INDEX IX_Table1_Res
ON Table1 (Res) 

-- CREATE TABLE 2 INDEXES 

CREATE NONCLUSTERED INDEX IX_Table2_ID
ON Table2 (ID) 

-- CREATE TABLE 3 INDEXES 

CREATE NONCLUSTERED INDEX IX_Table3_ID
ON Table3 (ID) 

-- CREATE TABLE 4 INDEXES 

CREATE NONCLUSTERED INDEX IX_Table4_ID
ON Table4 (ID) 

-- CREATE TABLE 5 INDEXES 

CREATE NONCLUSTERED INDEX IX_Table5_ID
ON Table5 (ID) 

Answer 2

Во-первых, создайте 4 индекса в таблице1:

IX_Table1_pro , IX_Table1_itype , IX_Table1_itatus , IX_Table1_Resиспользуя этот скрипт (XXX это имя столбца)

CREATE NONCLUSTERED INDEX IX_Table1_XXX  ON Table1 (XXX)

Затем создайте индекс в других таблицах, используя идентификатор столбца.

Answer 3

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

    SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS 

[SQLServer]
    ,db.[database_id] AS [DatabaseID]
    ,db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
    ,gs.[avg_user_impact] AS [AvgUserImpact]
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
    ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
    ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK)
    ON db.[database_id] = id.[database_id]
WHERE id.[database_id] > 4 -- Remove this to see for entire instance

ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);