Forum Discussion

lbendlin's avatar
lbendlin
Super User
4 years ago

Nested functions - unclear on scope impact

I have to do a complex fuzzy join between two tables involving multiple columns and weird conditions. As you can imagine performance is not great, and I am trying to find ways to optimize the step.  ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

I bet it's all of those "Or" statements slowing you down; it's gotta look for those strings for each column, each iteration. Instead of the "or"s, you could use List.Contains, and buffer the lists:

 

 each Table.SelectRows(A, each List.Contains(List.Buffer({"*", [L]}}, [L])) and List.Contains(List.Buffer({"*", [P]}, [P])) and List.Contains(List.Buffer({"*", [S]}, [S])))

 

--Nate

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    The or statements are selected specifically to encourage lazy streaming.  If the field is a wildcard (represented by the "*"  value) then evaluation should ideally stop for that comparison.  Not sure if List.Contains is optimized in a similar way, but I'll give it a try.

     

    My actual question was about a different aspect.

     

    Table.Addcolumn(LeftTable, (scope)=> Table.SelectRows(RightTable, each complex condition involving scope))

     

    or

     

    Table.Addcolumn(LeftTable, each Table.SelectRows(RightTable, (scope)=> complex condition involving scope))

     

    As I understand it buffers only make sense when you re-use the data. That's not applicable for this join.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      Oh—if your question is “why is the second query slower than the first?”, then I’d bet that in the second query, the (context) => is calculated for every iteration of “each Table.SelectRows(RightTable, complex filter statement)”
       
      As opposed to the first query, where (context) has to be calculated before it can be part of “each complex filter statement”.
       
      Regarding List.Contains, using a buffered list as the first parameter will yield an underlying SQL statement of IN (ABC, 123, Xyz, etc). Filtering SQL using a buffered list in List.Contains is a wonderful thing to behold, friend.
       
      Finally, and please forgive me if I’ve forgotten, but can’t you just join these tables and be done with it?!
       
      --Nate
      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        Nate,

        neither source is foldable so the change in SQL statement caused by List.Buffer doesn't matter in this scenario (but I'll keep that in mind for other scenarios).

        None of the standard Table.NestedJoin etc functions can be used for multiple join columns and wildcard joins - Table.AddColumn is the only option as far as I know.

        The actual business logic is even crazier with partially ragged hierarchies and recursions.

         

        I ended up implementing the logic in SQL Server directly after grudgingly importing my second source into SQL Server (it is originally a CSV file in a sharepoint). Same idea as in Power Query but performance is acceptable (low tens of minutes for 2.2M resulting rows, with continuous spooling of results) - even applying your List.Contains() approach a little bit 🙂

         

         

        Select [Key], COALESCE([uid],'Unassigned') [U]
from  k
left join  a on a.[L] in ('*',k.L) and a.[P] in ('*',k.[P]) and a.[S] in ('*',k.[S])
where not [Key]='*|*|*'