Query optimization

  • 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

  • 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!
    😎

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

  • 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!
    😎

    Hi,

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

  • 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.

  • 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!
    😎

    Hi,

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

    Post the actual plan, not an image please!
    😎

  • 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!
    😎

    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.

  • 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