Table.SelectRows causing extremely slow data query

[Anonymous]

I would really appreciate some help regarding how to optimize a query I have set up. From SQL it is pulling ~860 rows until I am applying a search and find function using Table.SelectRows. One table it is pulling from (CDI) is fairly large (~125M) and I think it runs that query for each of the 860 rows and causes such a slow load time. Can someone please look at my query and help me find a more efficient way to do this?

let
Source = Sql.Database("143.31.73.12", "Runtime", [Query="SET NOCOUNT ON#(lf)DECLARE @StartDate DateTime#(lf)DECLARE @EndDate DateTime#(lf)SET @StartDate = '20220201 08:00:00.000'#(lf)SET @EndDate = GetDate()#(lf)SET NOCOUNT OFF#(lf)SELECT * FROM (#(lf)SELECT History.TagName, DateTime, Value, vValue, StartDateTime#(lf) FROM History#(lf) WHERE History.TagName IN ('H02_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H03_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H04_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber',#(lf) 'H05_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H06_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H07_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H08_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber')#(lf) AND wwRetrievalMode = 'Delta'#(lf) AND wwResolution = 1000#(lf) AND wwVersion = 'Latest'#(lf) AND DateTime >= @StartDate#(lf) AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate"]),
#"Sorted Rows" = Table.Sort(Source,{{"TagName", Order.Ascending}, {"DateTime", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Sorted Rows1" = Table.Sort(#"Added Index",{{"TagName", Order.Ascending}, {"DateTime", Order.Ascending}}),
#"Renamed Columns" = Table.RenameColumns(#"Sorted Rows1",{{"Value", "OOL Feed Number"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"vValue", Int64.Type}, {"OOL Feed Number", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each try #"Added Index" [vValue] { [Index] } otherwise null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Custom.1]-[vValue]),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom.1", "Next Row Feed OOL"}, {"Custom", "Trigger Change OOL"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([OOL Feed Number] <> null and [OOL Feed Number] <> 0)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Custom", each #"Added Index" [DateTime] {[Index]}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"StartDateTime"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "CCA End Time"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"CCA End Time", type datetime}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type2", "Custom", each List.Count(Table.SelectRows(
FailTable,
(InnerTable)=>InnerTable[DateTime] >= [DateTime] and InnerTable[StartDateTime] <= [CCA End Time]
)[vValue])),
#"Renamed Columns3" = Table.RenameColumns(#"Added Custom3",{{"Custom", "CCA Fail"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns3",{{"CCA Fail", Int64.Type}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type3", "Custom.1", each Text.Start([TagName],3)),
#"Renamed Columns4" = Table.RenameColumns(#"Added Custom5",{{"Custom.1", "3CP GROUP"}}),
#"Added Custom4" = Table.AddColumn(#"Renamed Columns4", "Custom", each List.Max(Table.SelectRows(GCAS,
(InnerTableGCAS)=>
InnerTableGCAS[Start Time GCAS] <= [DateTime]
and InnerTableGCAS[End Time GCAS]>= [DateTime]
and InnerTableGCAS[3CP]= [3CP GROUP]
)[GCAS])),
#"Renamed Columns5" = Table.RenameColumns(#"Added Custom4",{{"Custom", "GCAS"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns5", each ([GCAS] <> null)),
#"Changed Type4" = Table.TransformColumnTypes(#"Filtered Rows1",{{"GCAS", Int64.Type}}),
#"Added Custom6" = Table.AddColumn(#"Changed Type4", "Custom", each List.Max(Table.SelectRows(CDI,
(InnerTableCDI)=>
InnerTableCDI[StartDateTime] <= [DateTime]
and InnerTableCDI[EndDateTime]>= [DateTime]
and InnerTableCDI[3CP]= [3CP GROUP]
and InnerTableCDI[FeedNum]=[OOL Feed Number]
)[vValue])),
#"Renamed Columns6" = Table.RenameColumns(#"Added Custom6",{{"Custom", "CDI Max"}})
in
#"Renamed Columns6"

[watkinnc]

Here you are:

let
Source = Sql.Database("143.31.73.12", "Runtime", [Query="SET NOCOUNT ON#(lf)DECLARE @StartDate DateTime#(lf)DECLARE @EndDate DateTime#(lf)SET @StartDate = '20220201 08:00:00.000'#(lf)SET @EndDate = GetDate()#(lf)SET NOCOUNT OFF#(lf)SELECT * FROM (#(lf)SELECT ROW_NUMBER() OVER(ORDER BY History.TagName ASC, DateTime ASC) as RowNumber, History.TagName, DateTime, Value, vValue, StartDateTime#(lf) FROM History#(lf) WHERE History.TagName IN ('H02_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H03_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H04_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber',#(lf) 'H05_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H06_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H07_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber','H08_CTRL.Concentrations.BMT_ConcentrationOOL_FeedNumber')#(lf) AND wwRetrievalMode = 'Delta'#(lf) AND wwResolution = 1000#(lf) AND wwVersion = 'Latest'#(lf) AND DateTime >= @StartDate#(lf) AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate"]),
#"Renamed Columns" = Table.RenameColumns(Source, {{"Value", "OOL Feed Number"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"vValue", Int64.Type}, {"OOL Feed Number", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each try #"Added Index" [vValue] { [Index] } otherwise null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Custom.1]-[vValue]),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom.1", "Next Row Feed OOL"}, {"Custom", "Trigger Change OOL"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([OOL Feed Number] <> null and [OOL Feed Number] <> 0)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Custom", each #"Added Index" [DateTime] {[Index]}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"StartDateTime"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "CCA End Time"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"CCA End Time", type datetime}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type2", "Custom", each List.Count(Table.SelectRows(
FailTable,
(InnerTable)=>InnerTable[DateTime] >= [DateTime] and InnerTable[StartDateTime] <= [CCA End Time]
)[vValue])),
#"Renamed Columns3" = Table.RenameColumns(#"Added Custom3",{{"Custom", "CCA Fail"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns3",{{"CCA Fail", Int64.Type}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type3", "Custom.1", each Text.Start([TagName],3)),
#"Renamed Columns4" = Table.RenameColumns(#"Added Custom5",{{"Custom.1", "3CP GROUP"}}),
#"Added Custom4" = Table.AddColumn(#"Renamed Columns4", "Custom", each List.Max(Table.SelectRows(GCAS,
(InnerTableGCAS)=>
InnerTableGCAS[Start Time GCAS] <= [DateTime]
and InnerTableGCAS[End Time GCAS]>= [DateTime]
and InnerTableGCAS[3CP]= [3CP GROUP]
)[GCAS])),
#"Renamed Columns5" = Table.RenameColumns(#"Added Custom4",{{"Custom", "GCAS"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns5", each ([GCAS] <> null)),
#"Changed Type4" = Table.TransformColumnTypes(#"Filtered Rows1",{{"GCAS", Int64.Type}}),
#"Added Custom6" = Table.AddColumn(#"Changed Type4", "Custom", each List.Max(Table.SelectRows(CDI,
(InnerTableCDI)=>
InnerTableCDI[StartDateTime] <= [DateTime]
and InnerTableCDI[EndDateTime]>= [DateTime]
and InnerTableCDI[3CP]= [3CP GROUP]
and InnerTableCDI[FeedNum]=[OOL Feed Number]
)[vValue])),
#"Renamed Columns6" = Table.RenameColumns(#"Added Custom6",{{"Custom", "CDI Max"}})
in
#"Renamed Columns6"

I added the SQL index and sort, and removed your first sort function and first index function.  Give that a try.

Thanks!---Nate

[watkinnc]

A few things to consider:

Your very first M function is the sort, which has to stream in the whole table. That could be done in your SQL statement instead.

Next, as soon as you put the index column in, no more query folding takes place. That also can be done on the SQL statement via the SQL statement as this statement at the beginning of the SQL:

SELECT

ROW_NUMBER() OVER(ORDER BY FIELD1 ASC, FIELD2 ASC) as RowNumber, THE REST OF THE SQL...

This will actually take care of the sort and index in one step.

[Yes folks, that is how you make an index column in SQL so you don't have to break folding!]

Give that a shot.

--Nate

[Anonymous]

Complete newbie here, where do I make this change?