DesNorton - Tuesday, May 8, 2018 6:53 AM

Imke Cronje - Tuesday, May 8, 2018 6:24 AM

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