January 28, 2015 at 10:54 pm
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
January 28, 2015 at 11:46 pm
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)...
January 29, 2015 at 12:40 am
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
January 29, 2015 at 1:28 am
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
😎
January 29, 2015 at 11:45 am
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".
January 29, 2015 at 2:38 pm
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 29, 2015 at 5:07 pm
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 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