Do derived tables joins perform better than plain joins? We are using Parallel datawarehousing sql server

Question

Do derived tables joins perform better than plain joins? We are using Parallel datawarehousing sql server

kukr007

Old Hand

Points: 352
More actions
March 16, 2017 at 5:37 am

#319670

Hi All,
Which query performs faster given each table has 30-40 columns and each table has huge data. We had an argument about which would perform better. I insisted that first one would do but my colleague thinks otherwise. Please let me know your inputs.
select t1.col1, t1.col2,t2.col1.t2.col2 from table1 join table2 t2 on t1.col1=t2.col1
select t1.col1, t1.col2,t2.col1.t2.col2 (select col1,col2 from table1)t1 join (select col1,col2 from table2) t2
on t1.column1=t2.column1

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

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

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

The two will be identical, identical plans, identical 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

kukr007 Old Hand Points: 352 More actions · Answer 2

We are using PDW not traditional sql server. Will they have same execution plans?

kukr007 Old Hand Points: 352 More actions · Answer 3

GilaMonster - Thursday, March 16, 2017 5:45 AM
The two will be identical, identical plans, identical performance.

We are using PDW not traditional sql server. Will they have same execution plans?

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

kukr007 - Thursday, March 16, 2017 6:20 AM
GilaMonster - Thursday, March 16, 2017 5:45 AM
The two will be identical, identical plans, identical performance.
We are using PDW not traditional sql server. Will they have same execution plans?

Yes, I saw the title.

The two will be identical, identical plans, identical 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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Depending on complexity, the second one might be a bit slower to compile (nothing noticeable). But most of the time, Gail's answer will prevail.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sergiy SSC Guru Points: 110208 More actions · Answer 6

This will be different:

select t1.col1, t1.col2,t2.col1.t2.col2 from (select col1,col2 from table1 group by col1,col2)t1 join (select col1,col2 from table2 group by col1,col2) t2 on t1.column1=t2.column1

How different? It depends.

In some situatiuons may be slower, but in others performance gain may be huge.

_____________
Code for TallyGenerator