I have a table 1 where I need to join with other 4 tables. Could you please help me out with the below query. 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
Here is the SQL Statment (I can't add it in the comment area)
-- 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)