SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query optimization


Query optimization

Author
Message
Imke Cronje
Imke Cronje
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4628 Visits: 982
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
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)

Group: General Forum Members
Points: 180322 Visits: 24706
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

Please post the DDL (create table) scripts for all the tables, some data samples as insert statement and the execution plan!
Cool

DesNorton
DesNorton
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21865 Visits: 9553
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);



How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags.
Imke Cronje
Imke Cronje
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4628 Visits: 982
Eirikur Eiriksson - Tuesday, May 8, 2018 6:45 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

Please post the DDL (create table) scripts for all the tables, some data samples as insert statement and the execution plan!
Cool

Hi,

All tables are temp tables. They are created with the select Into statement.

Attachments
Query plan.PNG (50 views, 29.00 KB)
Imke Cronje
Imke Cronje
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4628 Visits: 982
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);

Thanks so much. Will try this.

Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)SSC Guru (180K reputation)

Group: General Forum Members
Points: 180322 Visits: 24706
Imke Cronje - Tuesday, May 8, 2018 6:55 AM
Eirikur Eiriksson - Tuesday, May 8, 2018 6:45 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

Please post the DDL (create table) scripts for all the tables, some data samples as insert statement and the execution plan!
Cool

Hi,

All tables are temp tables. They are created with the select Into statement.

Post the actual plan, not an image please!
Cool

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (437K reputation)SSC Guru (437K reputation)SSC Guru (437K reputation)SSC Guru (437K reputation)SSC Guru (437K reputation)SSC Guru (437K reputation)SSC Guru (437K reputation)SSC Guru (437K reputation)

Group: General Forum Members
Points: 437649 Visits: 44175
Eirikur Eiriksson - Tuesday, May 8, 2018 7:21 AM
Imke Cronje - Tuesday, May 8, 2018 6:55 AM
Eirikur Eiriksson - Tuesday, May 8, 2018 6:45 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

Please post the DDL (create table) scripts for all the tables, some data samples as insert statement and the execution plan!
Cool

Hi,

All tables are temp tables. They are created with the select Into statement.

Post the actual plan, not an image please!
Cool


Agreed, when it comes to execution plans, a picture is just a single word that says nothing.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)

Group: General Forum Members
Points: 391448 Visits: 34705
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search