0

Consider 5 tables with more than 1 billion records each.

A query needs to join them. And I know that less than 10% of their records will be require. Say they all have a Date dimension, and only data from current month is needed.

What should be faster:

1) Use a simple SELECT: join all tables, then filter (WHERE) each table's dimension for current month.

2) create 5 temp tables, filtering each source table for current month records, here we can also take the opportunity to select only required columns, then join these temp tables.

Extra possibility:

3) Maintain secondary tables, having only current month/year worth of data. These tables are maintained by same ETL that feeds main ones.

  • show the table DDL along with the actual proposed queries. also include any indexes you have. – Max Vernon Mar 24 '16 at 18:23
  • 2
    Instead of guessing what's best, have you actually tried it? – James Z Mar 24 '16 at 18:31
  • What type of joins, inner or outer? – Antoine Hernandez Mar 24 '16 at 18:37
  • #2 should be slower than #1 (logically JOIN is done before WHERE, but the optimizer tries to filter as soon as possible. Of course #3 might be the fastest, but is additional overhead (CPU/IO/disk space) – dnoeth Mar 24 '16 at 18:40
  • @dnoeth indeed I've made a test and query plans for #1 and #2 are the exactly the same. For #2, I used subqueries to filter data before joining them in the main query, and indeed even on #1 the query plan filtered their data before doing the joins! – Hikari Apr 1 '16 at 14:05
2

Build up the query and see.
The query optimizer is often smart enough to filter early.
SQL is logical - in the where does not mean it will process last.

Clearly you want indexes on join and filter.

When you get to 5 or more joins the optimizer will often get defensive and go into a loop join.
Do I have a citation - no. It is an observation.

When it gets to 5 or more pulling the conditions into the join can help the optimizer.
Do I have a citation - no. It is an observation.

select * 
from tableA 
join tableB  
      on tableA.fkB = tableB.id 
     and tableA.date1 >= @date1start   
     and tableA.date1 <  @date1end 
join tableC  
      on tableA.fkC = tableC.id 
     and tableC.filter1 = 'do me early'  

Build it up one join at a time and see when it goes stupid.
Optimize one join at at time.

If you are going to materialize then put a pk on the #temp.
Start with where you think you are going to get the most bang.

OR condition in where / join are the most trouble and often lead to a loop join.
Those should be the first to materialize.

You can force hash join but that is a slippery slope that can go bad.

It seems kind of odd all tables have a date dimension, you would typically have some lookup type tables with more static type data.

If you don't need output from the table then a where exists may preform better.

  • Nice tips tnx a lot! I usually don't like to place filters inside JOIN ON, but in this case where a query can go very complex I see this aproach might even make it clearer. If needed, always comment parts of the code explaining what it's meant to do. – Hikari Apr 1 '16 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.