Update with inner join - Taking more than 13 hours

Question

Update with inner join - Taking more than 13 hours

Varun R

Ten Centuries

Points: 1171
More actions
December 8, 2011 at 1:54 am

#251765

Hi ,
I have two tables, lets consider TestA and TestB. Table TestA contain 9 million records, and Table TestB contains 2 Million records.I need to update a column in the table TestA with a columns in the TestB. It is taking around 13 hours now.Is there is any way to reduce the execution time?. Index creation on TestA also takes 35 Min.
Sample query:
update TestA set TestA.Column5 = TestB.Column5
from TestA inner join TestB
on TestA.Column1 = TestB.Column1
and TestA.column2 = TestB.Column2
and TestA.Column3 = TestB.Column3
Any one has an Idea to optimize this query logic?Any help is appreciated.
December 8, 2011 at 2:36 am

This was removed by the editor as SPAM

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Not sure table partitioning would really be necessary with these volumes, although it's difficult to give any practical advice without more information.

Have a read through this article and provide at the very least, some DDL, sample data and the actual execution plan:

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Even without indexes on fairly normal hardware, that sounds like a very long time for such a simple update (unless you have very wide data types) - are you really just joining on 3 columns and updating one column? Do you have any triggers on the target table? Are statistics up to date? I'll stop here as I'm just guessing randomly until you provide more information

Varun R Ten Centuries Points: 1171 More actions · Answer 2

Thanks man. we already has a partition. There are 96 columns in the Table A.No triggers are there.DataTypes are not fairly designed.All Statistics are update.Is Merge operator will be usefull?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

-- change this

update TestA set TestA.Column5 = TestB.Column5

from TestA inner join TestB

on TestA.Column1 = TestB.Column1

and TestA.column2 = TestB.Column2

and TestA.Column3 = TestB.Column3

-- to this

SELECT <<primary key from table a>>, b.Column5

INTO #Temp

FROM TestA a

INNER JOIN TestB b

ON a.Column1 = b.Column1

AND a.column2 = b.Column2

AND a.Column3 = b.Column3

CREATE UNIQUE CLUSTERED INDEX [CX<<primary key from table a>>] ON #Temp (<<primary key from table a>> ASC)

UPDATE a SET Column5 = b.Column5

FROM TestA a

INNER JOIN #Temp b ON b.<<primary key from table a>> = a.<<primary key from table a>>

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

HowardW One Orange Chip Points: 29109 More actions · Answer 4

Varun R (12/8/2011)
Thanks man. we already has a partition. There are 96 columns in the Table A.No triggers are there.DataTypes are not fairly designed.All Statistics are update.Is Merge operator will be usefull?

So your update statement updates all 96 columns? Yes, I could imagine that being slow. A MERGE statement may end up giving you a better plan, but I'll pass on commenting further until we have a realistic example of what you're trying to run and an execution plan.