Index suggestion for a table which has id columns
I have a table whose all columns store ids of other tables (huge tables).
CREATE TABLE #mytable ( Table1Id int, Table2Id int, Table3Id int, Table4Id int, Table5Id int, )
Now my select has join to all the tables whose ids are stored in the columns of my table.
select T1.col1, t2.Col1, T3.col1... from #mytable MyTable inner join table1 T1 on MyTable.Table1Id = T1.Id inner join table2 T2 on MyTable.Table2Id = T2.Id inner join table3 T3 on MyTable.Table3Id = T3.Id inner join table4 T4 on MyTable.Table4Id = T4.Id inner join table5 T5 on MyTable.Table5Id = T5.Id order by T1.Col1, T2.col1
At the moment I only have an index on Table1Id and on all the id columns of the other tables. Any suggestions to improve the performance.
You don't say which column your index is currently defined on, but based on your example query, you should create an index for all five columns;
Table1Id, Table2Id, Table3Id, Table4Id, Table5Id
This allows the SQL engine to resolve the query just by reading the index, which should be faster than reading the index, then reading the table.
If you run queries where you access some of the columns, then you need an index for those columns as well. Let's say you run a query on Table3Id and Table4Id. Then you need to create an index on;
I can't tell from the information you provided in your question if these indexes should be unique or non unique. You would have to make that determination.
Examine #mytable You have no search criteria on that table no where no order by no group by
You are just going to get those rows in no particular order. There is no use for any index on #mytable The index Table1Id is not used by that query and will slow down inserts
I suspect #mytable is just an output table and the where conditions are used to populate that table.
The join will use the ID on the table to be joined. So index ID on table1-x and index it as a PK (clustered) if you can. If that index is fragmented then defrag. That join should be an index seek and you can't do any better. Verify the query plan has index seeks on the joins. If you don have index seeks on those joins then post the query plan. You could experiment with hints on the join but I suspect the query optimizer will get it right - that may be a big query but it is not a complex query.
Since SQL will grab pages if you order the #mytable by the individual columns you have a better chance of that page being in memory. A PK is free IF you can insert in the order of the PK. In that case you would put the column with the most values in the last position. Actually would would put the column with tightest groupings of PK in the last position. And then sort by PK.