Need to increase query performance

  • Hello Friends,

    I need help with the following query, this query is taking more than 20mins to complete. I'm using a left join to

    pull the data.

    Query:

    Insert into dbo.SecondTable

    Select b.* From dbo.FirstTable a Left Join dbo.SecondTable b on a.Id=b.Id where b.Id is null

    it inserts approximately 1.5million records.

    Thanks for your help in advance

  • Correct if you are looking for the below one

    The select is of a.* not b.*

    if this is one you are at...

    Delete the matched records from source table(Second) with First table

    and then Insert into Source table with all records from First Table..

    Avoid * and mention column names...

    Check the rowsize also( approximate mamory of single row, multiply with 1.5 million ..cross check your space)...

  • prasadau2006 (1/28/2015)


    Hello Friends,

    I need help with the following query, this query is taking more than 20mins to complete. I'm using a left join to

    pull the data.

    Query:

    Insert into dbo.SecondTable

    Select b.* From dbo.FirstTable a Left Join dbo.SecondTable b on a.Id=b.Id where b.Id is null

    it inserts approximately 1.5million records.

    Thanks for your help in advance

    The SELECT query will return NULLs in every column – it is selecting all the rows in 'a' which have no matching row in 'b' and returning only the (NULL) values from table 'b'.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • prasadau2006 (1/28/2015)


    Hello Friends,

    I need help with the following query, this query is taking more than 20mins to complete. I'm using a left join to

    pull the data.

    Query:

    Insert into dbo.SecondTable

    Select b.* From dbo.FirstTable a Left Join dbo.SecondTable b on a.Id=b.Id where b.Id is null

    it inserts approximately 1.5million records.

    Thanks for your help in advance

    Quick thought, use the MERGE statement

    😎

  • You could try a nonclustered index on a that contains id,

    and a nonclustered index on b that contains id.

    That may or may not help, depending on the total sizes of the tables.

    Other than that, I don't see much that you can do.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • prasadau2006 (1/28/2015)


    Hello Friends,

    I need help with the following query, this query is taking more than 20mins to complete. I'm using a left join to

    pull the data.

    Query:

    Insert into dbo.SecondTable

    Select b.* From dbo.FirstTable a Left Join dbo.SecondTable b on a.Id=b.Id where b.Id is null

    it inserts approximately 1.5million records.

    Thanks for your help in advance

    Can you post the Actual execution plan for the SELECT part of the query? 1.5 million rows is a cup of tea. The rate limiting part is the SELECT and how it is, or isn't, supported by indexes.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Could this be a case of the Halloween problem?

    Read: The Halloween Problem – Part 2 by Paul White (there are 4 parts linked in at the bottom).

    Look for an "Eager Table Spool" in your INSERT's execution plan.

    If it is there, divide and conquer might help.

    It could also help to break the 1.5m row INSERT into six that INSERT 250K each.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply