Forum Discussion

jasbro's avatar
jasbro
Regular Visitor
8 years ago
Solved

Staging query is getting executed for every sub query

Hello,

 

 I'm using Excel to run Power Query. I followed Ken's design approach to use a Staging query that builds multiple re-shaped tables. I have one staging sql query (connection only) feeding 4 re-shape queries (load to table).

 

 When I refesh all, I notice that every sub query is contacting the database. Is that normal? Is there a way to point my sub queries to the staging query's result instead?

 

Or am I not understanding something? I thought the stagging query would only be executed once, then other queries would execute from those results.

 

 My queries are something like this:

SQL_Staging_Qry

let

  src=SQL.Database("srvnm", "catalog", [Query="SELECT * FROM foo"])

in

  src

 

Sub_Qry

let

  sql_src=SQL,

  // shape data with Table.SelectRows(sql_src, ...), Table.AddColumn(sql_src, ...), etc

  result = reshaped_data

in

  result

Need Help
  • jasbro's avatar
    jasbro
    8 years ago

    Hi, I think I understand. You're point comes from the perspective of what M language does and how the compiler interprets duplicate vs reference. 

     

    My perspective comes from SQL DB optimization and reducing the total number of queries to the database. As over the wire latency maters much more than processor time.

     

    What I found is... that Power Query does in fact send multiple SQL queries per sub query when the stagging query is set to 'Connection Only'.

    Proof / Repro

    1. Set your stagging query to 'Connection only' as Ken recomended.
    2. Use a SQL connection or some other remote data source. I used SQL for this test (and easier to test as you'll see).
    3. Create multiple sub queries as 'reference' queries. It actually doesn't matter what these queries do.
    4. Open up network monitor of your choice. I used WireShark. Set your capture filter to your remote destination.
    5. Hit Refresh all in your Excel spreadsheet and inspect the wire.

    I have detected exactly the same number of SQL Select statements hit the wire as there are sub queries.

     

    Potential Workaround Found

    1. Set your stagging query to 'Load to Table'
    2. Create a new query that loads from the table you just created
      let
          Source = Excel.CurrentWorkbook(){[Name="yourTableNameHere"]}[Content]
      in
          Source
    3. Set your sub queries to 'reference' the newly created query in step 2.
    4. Setup WireShark as before, and Hit Refresh All

    Only one SQL query will hit the wire and all other sub queries will now wait for completion.

     

    Further Discusion

    Ideally, resultant set should be used, but M Language cannot tell where the data comes from. Is it being cached, is it dynamically created, or is it coming from a remote system? Without that, M Language cannot effectively provide step optimization techniques as claimed with 'duplicate' vs 'reference'. The combination of missunderstanding of the capabilities with M Language optimziation and bad architectual advice lead me to this state. Stanging Query concept only works if you 'cache' the resultant set somewhere. Either in the DataModel or in a table.