Which JOIN style is better from performance point of view?

Question

Post reply

Which JOIN style is better from performance point of view?

Sachin Vaidya

Ten Centuries

Points: 1273
More actions
November 29, 2010 at 5:16 am

#227662

Hi,
If I do,
SELECT col1, col2....
FROM table1 AS A
INNER JOIN
(SELECT col3, col4....
FROM table2) B
ON A.col1 = B.col3
Or, I first take the inner query in a temp table first and then use that temp table directly in the JOIN? Consider that both the tables are really really huge (over 100 million records, and they have proper indexes).
Regards,
Sachin

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

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

This is going to be unanswerable here.

You will have to try both methods on YOUR data , on YOUR system to find the answer.

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

What's wrong with the straightforward approach?

SELECT a.col1, a.col2, b.col3, b.col4, ....

FROM table1 AS A INNER JOIN table2 AS B ON A.col1 = B.col3

If you use a temp table here you'll get the cost of inserting all 100 million rows into a temp table, then joining to an unindexed temp table, plus all the tempDB impact. Unless that subquery is significantly more complex than what you've shown, using a temp table will hinder performance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Unless there's something you're not listing here, there's no reason to go with temp tables in the query you've shown.

Just piling on to this one to reinforce the message.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Sachin Vaidya (11/29/2010)
Hi,
If I do,
SELECT col1, col2....
FROM table1 AS A
INNER JOIN
(SELECT col3, col4....
FROM table2) B
ON A.col1 = B.col3
Or, I first take the inner query in a temp table first and then use that temp table directly in the JOIN? Consider that both the tables are really really huge (over 100 million records, and they have proper indexes).
Regards,
Sachin

Seems like interview/quiz question.

Proper indexing will be required to get result faster ( with use of least resources) .Execution plan would be good tool to decide which query will be better ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sachin Vaidya Ten Centuries Points: 1273 More actions · Answer 5

@Bhuvanesh: This is not an interview or quiz question. I am reviewing a code where this kind of style is being used. SO I thought wheather this is better from performance perspective or we first need to create temp table.

Regards,

Sachin

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

create the exec plan for both the approaches.and post it here along with index/table definition.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

himanshumarwaha SSC Enthusiast Points: 166 More actions · Answer 7

With your description temp table is not a good idea; certainly it will take more time and use more resource; unless you are missing any detail.