Forum Discussion

JeffWeir's avatar
JeffWeir
Advocate V
7 years ago
Solved

Understanding why Table.Buffer makes a difference in dependency chain

I understand from Google that using Table.Buffer is often a good idea when multiple steps within a query will be referencing the same Table. But it seems from my experimentations that it's also a good idea when there are other downstream queries in the query dependency chain.

 

As per the question I posted here, I have a bunch of queries in a chain that are somewhat recursive in nature: I am finding matching between two data sources where the date doesn't always match, so I progressively  match the tables on larger values of a 'tolerance' factor X using this condition:

Table1.Date = Table2.Date +/-

 

And I'm effectiviely running this in a loop for values of X between 1 to 7, and removing successful matches on each pass leaving just unsuccessful ones. This lets me progressively increase the 'mismatch' tolerence on those dates, and remove matches at each pass leaving just the unmatched rows to do increasingly desperate matches on.

 

My  query performs the following steps:

  1. Loads data from Table1 and Table2, and does an inner join on unique ID and Date
  2. Effectively removes these matches from Table1 and Table2 via an anti-join against each referenced table.
  3. Does an inner join on the remaining records, but this time offsets the dates in the Port table by +/- 1 day. This finds some more matches.
  4. Performs steps 2 and 3 over and over, increasing the date offset by 1 each time until I have captured all date mismatches up to a week.

 

The query dependency tree that results looks like so:

 

 

I've found that using Table.Buffer on both Tables whenever I do a join dramatically increases (whoops) decreases execution time.

 

In this file, with no Table.Buffer, it takes about 17 seconds to run through the chain:

Match to closest day_20190722 No Buffer.xlsx

 

In this file, with a Table.Buffer on every Table that gets joined on every step where a join occurs, it takes just 6 seconds:

Match to closest day_20190722 buffer3 Revised.xlsx

 

Can anyone shed light on why the Buffer works? Perhaps another interesting question for ImkeF 

 

 

7 Replies