none
best strategy to enforce one query execution before another one (independant queries) RRS feed

  • Question

  • Let us say there are 2 queries: Query1 and Query2.
    The 2 queries are independant, namely Query1 does not need Query2, and Query2 does not need Query1.
    I need to force Query1 to fully execute first, each time Query2 runs.

    Due to M lazyness, I am using this function:

    (query_1 as nullable table, query_2 as nullable table) as nullable table => 
    	let
    		fn_2 = ()=>query_2		//to avoid 2 executions
    	in
    		if query_1=null		//forces execution of query_1
    		then fn_2()		//gives result of query_2
    		else fn_2()
    I wonder if someone has a better solution

    • Edited by anthony34 Tuesday, March 3, 2020 9:50 AM
    Tuesday, March 3, 2020 9:50 AM

Answers

All replies

  • Hi Anthony. Why does the order of execution matter? Do these queries cause some kind of external-to-M side effect to occur?

    Ehren

    Friday, March 6, 2020 12:36 AM
    Owner
  • Query1 saves a csv.file then Query2 reloads the csv.file and use it.

    Why not simply reference  Query1 directly in Query2 ?

    Because according to my experience, in specific cases when queries are complex, big and time consuming, it is far quicker to process using the save and reload step, due to the limits of buffering strategies and the duplication of execution in chained queries.

    When queries are quick and simple, direct reference is obviously the best way and then there is no question about the order of execution


    • Edited by anthony34 Friday, March 6, 2020 8:07 AM
    Friday, March 6, 2020 7:56 AM
  • Ok. PQ by itself isn't really intended for these kinds of scenarios. The design of M is intended to be "functional" (i.e. no side-effects). Have you considered using PQ in Dataflows (loading into a data lake) for your scenario? That way you would get full control over the workflow instead of relying on hacks.

    Ehren

    Friday, March 6, 2020 8:04 PM
    Owner
  • Ehren,
    I do not know what you are talking about
    Wednesday, March 11, 2020 7:21 AM
  • Hi Anthony. If you do a web search for Power Platform Dataflows, you'll find some resources explaining what it is and how to use it (including some videos). Here's one example:

    https://docs.microsoft.com/en-us/power-platform-release-plan/2019wave2/cdm-data-integration/power-platform-dataflows

    Hope that helps.

    Ehren

    Thursday, March 12, 2020 8:12 PM
    Owner