August 10, 2011 at 1:36 pm
I have 2 tables (tableA and tableB). TableA has about 50 records. TableB has about 3 million records. I want to update 1 column1 in TableA if TableA.keyfield = TableB.keyfield. If there is no match on keyfield, will the update still try to run and not update anything? I know updates can be slow and I don't want to use up a lot of resources on the server if there is not keyfield match. What is the best way to approach this?
August 10, 2011 at 1:45 pm
GrassHopper (8/10/2011)
I have 2 tables (tableA and tableB). TableA has about 50 records. TableB has about 3 million records. I want to update 1 column1 in TableA if TableA.keyfield = TableB.keyfield. If there is no match on keyfield, will the update still try to run and not update anything? I know updates can be slow and I don't want to use up a lot of resources on the server if there is not keyfield match. What is the best way to approach this?
Well, it shouldn't be that slow against 50 records (maximum), unless there's a LOT of matches over in TableB. As a matter of fact, it SHOULd be near instantaneous unless there's a lot of scanning on your 3 million row TableB.
As to will it try to update still? That will depend on how you write it, and not if you join it properly.
What you'll want is something like:
UPDATE a
SET col1 = b.col1
FROM
tblA AS a
JOIN tblB AS b
ON a.keyfield = b.keyfield
Please note, that if there are multiple matches, tblA will get b.Col1 that was the last produced by whatever detection method the optimizer found the most useful, and you can't control the order by here, at least not that easily. I'd also doublecheck that you've got an index on keyfield in tblB.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 10, 2011 at 2:02 pm
I'd bet on blocking or accidental cross join.
can you post your query and estimated execution plan?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply