none
Query Folding while Merging two PQ queries does not happen RRS feed

  • Question

  • Hi there!

    I am trying to merge PQ queries but Query Folding does not happen.
    WHat's wrong?
    ----
    let
        Source = Table.Join(#"leb_x_deal",{"code"},#"leb_x_warehouse",{"deal_code"},JoinKind.Inner),
        #"Removed Columns" = Table.RemoveColumns(Source,{"deal_code"})
    in
        #"Removed Columns"
    ----

    PQ queries for merge are here: 
    ----
    let
        Source = MySQL.Database("10.8.0.10", "leb"),
        leb_x_warehouse = Source{[Schema="leb",Item="x_warehouse"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(leb_x_warehouse,{"deal_code", "active"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"deal_code"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([active] = true)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"active"})
    in
        #"Removed Columns"
    ----
    let
        Source = MySQL.Database("10.8.0.10", "leb"),
        leb_x_deal = Source{[Schema="leb",Item="x_deal"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(leb_x_deal,{"code", "type", "status", "begin"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([type] = "default") and ([status] = "enabled")),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [begin] >= #datetime(2015, 1, 31, 0, 0, 0) and [begin] < #datetime(2015, 2, 1, 0, 0, 0)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"type", "status"})
    in
        #"Removed Columns"




    Sunday, February 1, 2015 6:51 AM

Answers

  • Hi Paul,

    I just realized that you use the same data source for the two queries, so the folding should not be prevented due to privacy settings.

    Regarding #datetime: I double-checked, and saw that #datetime is folded. So you can keep using it. It seems however that join operations using separate queries may not fold on certain scenarios, but do fold when written as a single query (e.g. Here). We will check your query and get back to you soon with a confirmed answer.

    You can check this blog to learn how to create a join operation as a single M query that will support folding.





    Sunday, February 1, 2015 2:06 PM
  • Could you post the "create table" statements for these tables along with any indexes and foreign key constraints between them? At first glance, this should definitely be run on the server.

    One thing to note is that we sometimes do things differently in the editor than when we fill to the data model or worksheet. If you're investigating whether this executes server-side, the most correct way to run the test is on refresh and not from the editor.

    Monday, February 2, 2015 1:53 PM

All replies

  • Assuming that your queries share the same privacy level, or that you enabled Fast Combine, I would guess that the use of #date, which is equivalent to Date.From function, prevents the folding.

    Similar issue was discussed here.

    Sunday, February 1, 2015 7:51 AM
  • Hi Gil,

    Thanks for quick reply.

    Actually I don't fully understand what is your suggestion.

    In the post which you mentioned the problem was with Date.From function and 
    solution was to use scalar comparison operator. 
    I am using just scalar comparison operator datetime <= datetime. 

    So, what should I do?

    -- Paul
    Sunday, February 1, 2015 1:12 PM
  • Hi Paul,

    I just realized that you use the same data source for the two queries, so the folding should not be prevented due to privacy settings.

    Regarding #datetime: I double-checked, and saw that #datetime is folded. So you can keep using it. It seems however that join operations using separate queries may not fold on certain scenarios, but do fold when written as a single query (e.g. Here). We will check your query and get back to you soon with a confirmed answer.

    You can check this blog to learn how to create a join operation as a single M query that will support folding.





    Sunday, February 1, 2015 2:06 PM
  • Could you post the "create table" statements for these tables along with any indexes and foreign key constraints between them? At first glance, this should definitely be run on the server.

    One thing to note is that we sometimes do things differently in the editor than when we fill to the data model or worksheet. If you're investigating whether this executes server-side, the most correct way to run the test is on refresh and not from the editor.

    Monday, February 2, 2015 1:53 PM
  • Hi Curt,

    (1) You can download CREATE TABLE statements from here: https://www.dropbox.com/s/nn24qx8wwnwn9l3/create_tables.txt?dl=0

    (2) I have small question. I suspect that problem with MySQL .Net Connector. Is there some way to configure it before or during PQ connection?

    Looking forward to your reply.

    -- Paul


    Tuesday, February 3, 2015 8:03 AM
  • What kind of configuration information are you looking for? There are a few settings on the MySQL connection string that we let you control -- see the online help for details -- but for the most part we just use whatever values are the default.

    I see from your other thread that these queries are, in fact, folding to a SQL statement but that the generated SQL is slow.

    Wednesday, February 4, 2015 4:55 PM
  • Hi Curt,

    (1) While connecting to MySQL via ODBC driver I use such connections settings:

    -- Allow big result sets,
    -- Use compression,
    -- Return matched rows instead of affected rows

    Connection Timeout is also desirable option.

    (2) Could you explain how can I use connection string if 
    when I use MySQL .Net Connector I can put only Server and Database parameters in UI.

    -- Paul



    Thursday, February 5, 2015 8:17 AM