SQL Server table or subquery for join?

Question

I'm now using a rather cryptic DB (SAP) running on SQL server 2012, only SELECT permission, so some things are difficult to debug/optimize.

I have a big table 'table1', and smaller tables 'table2', 'table3'. The big table with millions of rows will be filtered to 100 rows at most in the "where" statement

I have to start from table1. Would you recommend:

select fields 
from table1
left join table2 
left join table3
where table1.field1 = 'x' 
  and table1.field2 = 'y'

Or

Select 
    fields 
from
    (select fields 
     from table1 
     where table1.field1 = 'x' and table1.field2 = 'y') as t1
left join
    table2
left join
    table3

And, why? I want to understand this a little better.

Thank you!

Do not use subqueries unnecessarily. Let the optimizer do its work. — Gordon Linoff, Apr 15 '18 at 16:14
if you use sqlserver, what does the reference to SAP mean in your application? — ScaisEdge, Apr 15 '18 at 16:38
@scaisEdge SAP is stored in MSSQL, is just to let you know that i have only SELECT permissions and the tables, names, indexes are not self explanatory, it's hard to understand some times. — Alejandro, Apr 15 '18 at 16:40

Raunak Thomas · Accepted Answer · 2018-04-16T06:01:40.977

1

Ideally

select fields 
from table1
left join table2
left join table3
where table1.field1 = 'x' and table1.field2 = 'y'

This code will first join all the tables and then apply the filter mentioned in the where condition.

Select fields 
from
     (select fields 
      from table1 
      where table1.field1 = 'x' and table1.field2 = 'y') as t1
left join table2
left join table3

Whereas this code will first of all filter the table based on the filter criteria, load it in a temporary location and only then join the filtered rows to the other tables.

If table1 has a lot of rows with very few rows satisfying the filter condition (Low Cardinality) the 2nd code would run faster.

If the filter does not reduce the number of rows much then the bottleneck would be the loading into the temporary space in code 2 due to which code 1 might be better

NOTE:: This answer would change based on your SQL engine and query optimizer. Many optimizers are smart enough to detect that in code 1 only filtered rows need to be joined so in that case code1 would be better

edited Apr 16 '18 at 06:01

answered Apr 15 '18 at 16:21

Raunak Thomas

1,393
1
12
28

Thank you, it's SQL SERVER 2012 – Alejandro Apr 15 '18 at 16:37
The querry optimizer works differently in different versions and also based on the size of the tables, indexes etc. Try the `Explain` functionality to understand the execution plan. https://stackoverflow.com/questions/3449814/sql-server-equivalent-to-mysqls-explain – Raunak Thomas Apr 15 '18 at 16:46
thank you, can't have permission to view execution plan on this server, that's why I want to know if there is a general rule or order of execution to improve the queries I make. – Alejandro Apr 15 '18 at 17:05
Ok, knowing that the huge table (table1) have millions of rows but, at most, 100 will be filtered in the "WHERE", I understand that maybe the best thing to do is to filter it with a subquery and then make the joins – Alejandro Apr 15 '18 at 18:07

SQL Server table or subquery for join?

1 Answers1