Need to increase query performance

Question

Post reply

Need to increase query performance

prasadau2006

SSC-Addicted

Points: 408
More actions
January 28, 2015 at 10:54 pm

#314831

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

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

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

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 3

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

😎

ScottPletcher SSC Guru Points: 100262 More actions · Answer 4

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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

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]

Dwain Camps SSC Guru Points: 86913 More actions · Answer 6

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