Hi Guys,
I have to optimize a query that takes hours to run. I cancelled the query because it takes a long time to run. The query makes us of temp tables and makes a left join to another temp table. Both table contains 19 millions records.
The query is as follows:
IF object_id ('tempdb..#table2') IS NOT NULL DROP TABLE #table2
select
a.*, b.column1,b.column2,b.column3,b.column4
into #table2
from #table1 a left join #table3 b on a.column1= b.column1 and a.column2= b.column2
Any ideas how I can optimize this query? Any help will be much appreciated.
Regards
Things to try ...
1 - Use proper tables, not #Temp tables
2 - Add indexes
* ALTER TABLE #table1 ADD PRIMARY KEY CLUSTERED (column1,column2); -- Used PK as we do not know how many columns there are.
* ALTER TABLE #table3 ADD PRIMARY KEY CLUSTERED (column1,column2); -- Used PK as we do not know how many columns there are.
OR
* CREATE NONCLUSTERED INDEX IX_table3 ON #table3(column1,column2) INCLUDE (column3,column4);