Forum Discussion
Why does my query run so slow? How do I read a diagnostic report? diagnostic report attached
Any CSV is text, and the issue isn't it is a text file, it is that it is a flat file not in a database, like SQL Server or SAP. So any Excel, CSV, TSV, TXT, or other flat file will always run slower because Power Query must do 100% of the work, whereas if the table is in a database, there is a good chance you can optimize your queries to put as much work back on the server.
For example, I am working on a project and this table has a few million rows, probably 3.5-4.0 million. This takes about 3 seconds to run, because all of the steps I did in Power Query (a few filters, column selections, etc) were sent back to the SQL Server as a SQL Statement:
select [_].[t0_0] as [Order Type], [_].[t1_0] as [Order Number], [_].[entered_dt] as [EnteredDateKey], [_].[ord_dt] as [OrderDateKey], [_].[t2_0] as [OE PO Number], [_].[t3_0] as [Customer Number], [_].[t4_0] as [AR Reference], [_].[tot_sls_amt] as [Total Sales Amount], [_].[inv_no] as [Invoice Number], [_].[inv_dt] as [InvoiceDateKey], [_].[t5_0] as [PaymentDateKey] from ( select [_].[entered_dt] as [entered_dt], [_].[ord_dt] as [ord_dt], [_].[tot_sls_amt] as [tot_sls_amt], [_].[inv_no] as [inv_no], [_].[inv_dt] as [inv_dt], ltrim(rtrim([_].[ord_type])) as [t0_0], ltrim(rtrim([_].[ord_no])) as [t1_0], ltrim(rtrim([_].[oe_po_no])) as [t2_0], ltrim(rtrim([_].[cus_no])) as [t3_0], ltrim(rtrim([_].[ar_reference])) as [t4_0], ltrim(rtrim([_].[user_def_fld_1])) as [t5_0] from ( select [_].[ord_type], [_].[ord_no], [_].[entered_dt], [_].[ord_dt], [_].[oe_po_no], [_].[cus_no], [_].[ar_reference], [_].[tot_sls_amt], [_].[inv_no], [_].[inv_dt], [_].[user_def_fld_1] from [dbo].[OEHDRHST_SQL] as [_] where [_].[cus_no] like 'SFN100%' ) as [_] ) as [_] where (([_].[t1_0] <> '00379675' or [_].[t1_0] is null) and ([_].[t1_0] <> '00379320' or [_].[t1_0] is null)) and ([_].[t1_0] <> '00379674' or [_].[t1_0] is null)That would take minutes to run, perhaps tens of minutes, if it was a text file because I would have had to load all 3.5M rows in Power Query to filter it down to the 7 records I need in this work in progress.
As to your question on the SUMIFS, I don't know. I'd have to test both ways. I suspect a merge will be faster, but I'd do it both ways to see.
You are doing a number of joins, which may or may not be the issue, except that one is a text file, which is absolutely breaking any folding your SAP connection may otherwise be doing. While I am certian there are optimizations that could be done, which could yield varying levels of improvement, there is no way I can begin to make suggestions with just one query from your model without understanding what is in your overall query model. Even with the PBIX file I couldnt help because none of the connections would work for me. Someone else perhaps more versed in M, especially with SAP data sources may be able to assist, but this is the kind of thing I'd need to get my hands on and play with.
These lines concern me, but again, no clue how much is actually going on here. 10 records, 10,000, or 1,000,000?
#"Calculate Sumif weight of Delivery" = Table.AddColumn(#"Changed Type", "Sumif weight of Delivery", each let _item = [Delivery.Delivery Level 01] in List.Sum(Table.SelectRows(#"Changed Type", each [Delivery.Delivery Level 01] = _item)[#"Weight Formula (Matr Wgt X Del Qty)"])),Your grouped rows can be a problem too if it is happening after folding has broken.
Thank you for your ideas.
You mention the text file could be a problem. Would would be a better format? I could change the format. Is .csv better?
For the sumif line...would it be better to group the data in a seperate query? I originally had the query group itself and then merge back, but I changed to a sumif because I thought merging would be too slow.
I'm dealing with about 100,000 lines of data to answer your other quesiton.
Any CSV is text, and the issue isn't it is a text file, it is that it is a flat file not in a database, like SQL Server or SAP. So any Excel, CSV, TSV, TXT, or other flat file will always run slower because Power Query must do 100% of the work, whereas if the table is in a database, there is a good chance you can optimize your queries to put as much work back on the server.
For example, I am working on a project and this table has a few million rows, probably 3.5-4.0 million. This takes about 3 seconds to run, because all of the steps I did in Power Query (a few filters, column selections, etc) were sent back to the SQL Server as a SQL Statement:
select [_].[t0_0] as [Order Type], [_].[t1_0] as [Order Number], [_].[entered_dt] as [EnteredDateKey], [_].[ord_dt] as [OrderDateKey], [_].[t2_0] as [OE PO Number], [_].[t3_0] as [Customer Number], [_].[t4_0] as [AR Reference], [_].[tot_sls_amt] as [Total Sales Amount], [_].[inv_no] as [Invoice Number], [_].[inv_dt] as [InvoiceDateKey], [_].[t5_0] as [PaymentDateKey] from ( select [_].[entered_dt] as [entered_dt], [_].[ord_dt] as [ord_dt], [_].[tot_sls_amt] as [tot_sls_amt], [_].[inv_no] as [inv_no], [_].[inv_dt] as [inv_dt], ltrim(rtrim([_].[ord_type])) as [t0_0], ltrim(rtrim([_].[ord_no])) as [t1_0], ltrim(rtrim([_].[oe_po_no])) as [t2_0], ltrim(rtrim([_].[cus_no])) as [t3_0], ltrim(rtrim([_].[ar_reference])) as [t4_0], ltrim(rtrim([_].[user_def_fld_1])) as [t5_0] from ( select [_].[ord_type], [_].[ord_no], [_].[entered_dt], [_].[ord_dt], [_].[oe_po_no], [_].[cus_no], [_].[ar_reference], [_].[tot_sls_amt], [_].[inv_no], [_].[inv_dt], [_].[user_def_fld_1] from [dbo].[OEHDRHST_SQL] as [_] where [_].[cus_no] like 'SFN100%' ) as [_] ) as [_] where (([_].[t1_0] <> '00379675' or [_].[t1_0] is null) and ([_].[t1_0] <> '00379320' or [_].[t1_0] is null)) and ([_].[t1_0] <> '00379674' or [_].[t1_0] is null)That would take minutes to run, perhaps tens of minutes, if it was a text file because I would have had to load all 3.5M rows in Power Query to filter it down to the 7 records I need in this work in progress.
As to your question on the SUMIFS, I don't know. I'd have to test both ways. I suspect a merge will be faster, but I'd do it both ways to see.