Index on query with multiple joins

Question

Index on query with multiple joins

MrRobot

Old Hand

Points: 368
More actions
February 25, 2023 at 9:39 pm

#4154167
Hello,
I know that it is good to create index for column on where and join conditions. Secondarily on any group by. Last is Order by.
I'm trying the know what's the best way to create an index for a table that used multiple time with different column of join
For example on the query as bellow the table1 use those columns on joins : t1_Reference ,Date_Key ,t1_Key,PP_key and each column with different table
```
SELECT
 t1.t1_Key
,t3.t3_Type
,t1.Client_key
,t2.ClientType
,t4.AG_Key
,FORMAT(t1.Date, 'yyyy-MM') AS 'Mois'
,abs(DATEDIFF(DAY, t1.dts_Date,  t1.dtt_Date)) as TimeDay
,count(1) as nb
INTO #temp_table
FROM #table1 t1
INNER JOIN #table2 t2 ON t2.t2_Reference = t1.t1_Reference AND t2.Date_Key = t1.Date_Key
INNER JOIN #table3 t3 ON t1.t1_Key = t3.t1_Key 
INNER JOIN table4  t4 ON t1.PP_key = t4.PP_key
GROUP BY
 t1.t1_Key
,t3.t3_Type
,t1.Client_key
,t2.ClientType
, t4.AG_Key
,FORMAT(t1.Date, 'yyyy-MM')
,abs(DATEDIFF(DAY, t1.dts_Date,  t1.dtt_Date))
```
The size of the tables are :
#table1 (temp table) : 64 450 048 Rows
#table2 (temp table) : 2 227 414 Rows
#table3 (temp table) : 6 Rows
table4 (physical table) : 579 628 194 Rows
The datatype of all columns in join are Int except t2_Reference and t1_Reference are varchar
I would like to know what is the best way to create the index and Why ?
Thanks for help !

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18287 More actions · Answer 1

If t1_key is a primary key to #table1 then when it joins to #table3 on t1_Key it can only return up to 6 rows from #table1 as #table3 only has 6 rows.

INNER JOIN #table3 t3 ON t1.t1_Key = t3.t1_Key

It would be nice to know how many rows are in the join conditions.

MrRobot Old Hand Points: 368 More actions · Answer 2

Hello Jonathan,

The table 1 is a fact table and the table 3 is a dimension table the t1_Key is a foreign key of the table 3 , like a said on the post the table 1 have 64 450 048 Rows and this the value that is returned

Jonathan AC Roberts SSCoach Points: 18287 More actions · Answer 3

That's a bit of a strange naming convention. You would normally call the column t3_Key not t1_Key.

Jonathan AC Roberts SSCoach Points: 18287 More actions · Answer 4

I think the first index to try is indexing table4

You could give it a clustered primary key on (PP_key, AG_key)

or

CREATE NONCLUSTERED INDEX [IX_table4_1] ON [dbo].[table4] ([PP_key]) INCLUDE ([AG_Key])