Power Query - Query Folding Help

[Matt Allington]

I am trying to bring in data from SQL Server into Power Pivot (Excel 2013) using Power Query. I want to use Query folding to make the process more efficient. I understand this should be automatic, however the behaviour suggests it is not working.

I have a list of (say 10) product codes that I want to bring sales into Power Pivot. I have loaded a table containing 10 product codes (create connection only) in Power Query.

I then create a new connection to SQL server. I did not write any SQL, but instead browsed to the table from within Power Query Edit. I then created an inner join between this SQL table and the 10 product codes mentioned above. I would expect a query to be pushed to SQL and the small table returned. But instead I see the Power Query status bar cranking through 20 million + rows.

Am I doing something wrong? If I write this query natively, it refeshes in 20 seconds.

 

[anvg]

Hi
I am sorry. I did not read your question to end and did not understand fully. I read about PQ query folding but that PQ property is not really documented. Thank a lot ImkeF for that reference about folding problem. Is there a reason to trust a successful work of folding?
I think that it is better to use batch query to SQL Server for executing such task using upload of sheet table data to a temporary table on a SQL Server side instead IN statement if your sheet data is big.

let
    neededProduct = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][ProductId],
    toText = List.Transform(neededProduct, each Text.From(_)),
    //create sequence of insert [for <=1000 can use a simplistic version Insert Into #needed_products values (1),(22),(34);]
    insValsQueryPart = " Insert Into #needed_products values (" 
    & Text.Combine(toText,
        "); Insert Into #needed_products values ("
    ) 
    & ");",
//=============
    startQueryPart = 
"
if OBJECT_ID('tempdb..#needed_products') is not null  drop table #needed_products;
Create table #needed_products(prodId int);
",
//=============
    finishQueryPart =
"
create index temp_inn_idx on #needed_products (prodId);
SELECT *  from dbo.Checks dc Inner Join #needed_products np On (dc.ProductId=np.prodId)
",
    source = Sql.Database("(localdb)\mssqllocaldb",
    "SqlServerStudy", 
    [Query= startQueryPart & insValsQueryPart & finishQueryPart
//baseQueryPart & toQueryPart & ")"
    ])
in
    source

Indeed it is not automatically executing and it is required manual input a batch code for different database engine.
Regards,

 

[ImkeF]

Wow, brilliant - that's advanced!
But we're entering danger-zone here! Take care not to accidentally destroy your DB!
Have a look here: https://youtu.be/KXxUDWwo0pg?t=625 shows some things to watch out when doing native DB queries.

 

[anvg]

Hi, ImkeF
If you want more safety then you can use a table variable instead a temporary table. They wrote, the table variable is more effective than temporary table if its row count <1000 Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance - CodeProject But I did not test on 2012, 2014 that.
On my notebook (I have it 4 years) it loads data of 34000 rows into a Powerpivot model from 1000000 rows of SQLLocalDb 2014 table using 10000 sheet table rows about 6 seconds in both cases (the SQLLocalDb data table contains 4 columns with integer types).

let
    neededProduct = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][ProductId],
    toText = List.Transform(neededProduct, each Text.From(_)),
    //create sequence of insert [for <=1000 can use a simplistic version Insert Into @needed_products values (1),(22),(34);]
    insValsQueryPart = " Insert Into @needed_products values (" 
    & Text.Combine(toText,
        "); Insert Into @needed_products values ("
    ) 
    & ");",
//=============
    startQueryPart = 
//clustered index is allowed from 2014 version
"
Declare @needed_products as table (prodId int Not Null Index temp_prod_idx Clustered);
",
//=============
    finishQueryPart =
"SELECT *  from dbo.Checks dc Inner Join @needed_products np On (dc.ProductId=np.prodId)",
    source = Sql.Database("(localdb)\mssqllocaldb",
    "SqlServerStudy", 
    [Query= startQueryPart & insValsQueryPart & finishQueryPart
    ])
in
    source

Regards,