Which is faster? Using a lookup or joining on the server?

Question

Post reply

Which is faster? Using a lookup or joining on the server?

dndaughtery

SSChampion

Points: 11290
More actions
September 29, 2010 at 8:34 am

#238925

Have an update to perform on a table with data from a warehouse. There are approximately 8 million records in the table on which I'm performing an update causing the lookup to load all 8 million records into memory. I've thought about instead of loading all of that data into memory I can perform an inner join with the tables from the warehouse and the one I'm checking updates on with a where clause to check if any of the columns form the source doesn't match the column on the table needing updates and directly load the records that need to be updated into a table and running an update statement. Would this be quicker than using a lookup?

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

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

The joining option will probably be faster.

You should try both just to see what the difference will be.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

You're essentially doing the same thing. Whether or not you load it into memory first, SQL will still pull all data into the cache..I'd be willing to bet you'd have very similar update times either way. This is assuming that there are enough rows to be updated that it would be doing a full scan on the data in question anyway, and not just a seek...if you're seeking, just joining will be faster.l

dndaughtery SSChampion Points: 11290 More actions · Answer 3

dndaughtery

SSChampion

Points: 11290

September 29, 2010 at 8:59 am

#1229118

Good to know. Thanks guys.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

dndaughtery (9/29/2010)
Have an update to perform on a table with data from a warehouse. There are approximately 8 million records in the table on which I'm performing an update causing the lookup to load all 8 million records into memory. I've thought about instead of loading all of that data into memory I can perform an inner join with the tables from the warehouse and the one I'm checking updates on with a where clause to check if any of the columns form the source doesn't match the column on the table needing updates and directly load the records that need to be updated into a table and running an update statement. Would this be quicker than using a lookup?

"It Depends". In fact, it depends a lot here. How many rows out of that 8 million are you actually updating?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)