Join 4 different tables with one table

Question

I have a table 1 where I need to join with other 4 tables. Can I write the same query using subqueries? Because if I am using below query it is taking long time (more than 40 mins) for running. Is there any other way for this?

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

Be sure you have indexes on all the `id` columns. In fact, they should be declared as primary keys. — Gordon Linoff, Nov 30 '16 at 15:16
how can I include indexes for the above query? @GordonLinoff — , Nov 30 '16 at 15:22
joins are faster than subqueries.. so you should search for indexing — kapilpatwa93, Nov 30 '16 at 15:26

score 0 · Answer 1 · answered Nov 30 '16 at 15:50

this can give you a basic idea of indexes you will need. Try to look at the ones for your primary keys.

    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);

score 0 · Answer 2 · edited Nov 30 '16 at 22:53

0

Firstly, create 4 indexes in the table1:

IX_Table1_pro , IX_Table1_itype , IX_Table1_itatus , IX_Table1_Res using this script (XXX is column name)

CREATE NONCLUSTERED INDEX IX_Table1_XXX  ON Table1 (XXX)

Then create an index in the other tables using the column ID

edited Nov 30 '16 at 22:53

halfer

19,824
17
99
186

answered Nov 30 '16 at 15:55

Riadhovic

226
1
5

Thank you, but can you please tell me more in detail. @Riadhovic – Nov 30 '16 at 15:58
you need the sql statment to create all the indexes ? – Riadhovic Nov 30 '16 at 15:58
yes please and I need the sql statement with the indexes in the above query – Nov 30 '16 at 15:59

score 0 · Accepted Answer · edited Nov 30 '16 at 22:52

0

Here is the SQL statement:

-- 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)

edited Nov 30 '16 at 22:52

halfer

19,824
17
99
186

answered Nov 30 '16 at 16:05

Riadhovic

226
1
5

Thank you so much My question is where can I add the above indexes in the above select statement? @Riadhovic – Nov 30 '16 at 16:08
You don't have to change anything in the above query just execute the my sql code and then run your query – Riadhovic Nov 30 '16 at 16:10

Join 4 different tables with one table

3 Answers3