May 8, 2018 at 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
May 8, 2018 at 6:45 am
Imke Cronje - Tuesday, May 8, 2018 6:24 AMHi 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.column2Any ideas how I can optimize this query? Any help will be much appreciated.
Regards
Please post the DDL (create table) scripts for all the tables, some data samples as insert statement and the execution plan!
😎
May 8, 2018 at 6:53 am
Imke Cronje - Tuesday, May 8, 2018 6:24 AMHi 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.column2Any 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);
May 8, 2018 at 6:55 am
Eirikur Eiriksson - Tuesday, May 8, 2018 6:45 AMImke Cronje - Tuesday, May 8, 2018 6:24 AMHi 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.column2Any ideas how I can optimize this query? Any help will be much appreciated.
Regards
Please post the DDL (create table) scripts for all the tables, some data samples as insert statement and the execution plan!
😎
Hi,
All tables are temp tables. They are created with the select Into statement.
May 8, 2018 at 6:55 am
DesNorton - Tuesday, May 8, 2018 6:53 AMImke Cronje - Tuesday, May 8, 2018 6:24 AMHi 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.column2Any 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);
Thanks so much. Will try this.
May 8, 2018 at 7:21 am
Imke Cronje - Tuesday, May 8, 2018 6:55 AMEirikur Eiriksson - Tuesday, May 8, 2018 6:45 AMImke Cronje - Tuesday, May 8, 2018 6:24 AMHi 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.column2Any ideas how I can optimize this query? Any help will be much appreciated.
Regards
Please post the DDL (create table) scripts for all the tables, some data samples as insert statement and the execution plan!
😎Hi,
All tables are temp tables. They are created with the select Into statement.
Post the actual plan, not an image please!
😎
May 8, 2018 at 9:20 am
Eirikur Eiriksson - Tuesday, May 8, 2018 7:21 AMImke Cronje - Tuesday, May 8, 2018 6:55 AMEirikur Eiriksson - Tuesday, May 8, 2018 6:45 AMImke Cronje - Tuesday, May 8, 2018 6:24 AMHi 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.column2Any ideas how I can optimize this query? Any help will be much appreciated.
Regards
Please post the DDL (create table) scripts for all the tables, some data samples as insert statement and the execution plan!
😎Hi,
All tables are temp tables. They are created with the select Into statement.
Post the actual plan, not an image please!
😎
Agreed, when it comes to execution plans, a picture is just a single word that says nothing.
May 9, 2018 at 4:30 am
As everyone else has already said, it's almost impossible to tell you what to do without knowing the structures you're dealing with (beyond the fact that it's temp tables) and what the execution plans are (yeah, there's a pretty picture, but all the properties of the operators are where the details live and the details are how you understand what's going on).
From the picture you've painted so far, it looks like you're moving everything into temporary tables and then joining temporary tables together moving everything again. If there is no filtering occurring, there really are zero tuning opportunities. The only thing you can do to improve "move everything" is buy bigger and faster hardware. Instead, you maybe should focus on whether or not you really need to "move everything". What are the core business requirements driving "move everything"? Is there some way to provide filtering, and therefore indexes, constraints, all the things we can use to tune a query.
Please, post the details in order for us to help you better, but, if you're just "moving everything" there's probably very little we're going to be able to do to help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply