Optimizing Query

[Anonymous]

I am trying to get some statistics about my DB and my code seems to work perfect, but I got a real big DB and after trying to run this script on it, I ended up alway with Timeout failure, doesn't matter if I removed some unnecessary rows or not , I still getting the same error and the script is the following :

let
Source = Sql.Database("DBTEST","DB_TST",[CreateNavigationProperties=false]),
#"Filtered Rows" = Table.SelectRows(Source , each ([Kind] = "Table")),
#"Added Custom" = Table.AddColumn( #"Filtered Rows", "Profile",
each Table.Profile([Data])),

#"Expanded Profile" = Table.ExpandTableColumn( #"Added Custom" ,
"Profile",
{"Column", "Min", "Max", "Average", "StandardDeviation", "Count", 
"NullCount", "DistinctCount"},
{"Column", "Min", "Max", "Average", "StandardDeviation", "Count", 
"NullCount", "DistinctCount"})
in
#"Expanded Profile",
#"Entfernte Spalten" = Table.RemoveColumns(Tables_profile,{"Data"}),
#"Gefilterte Zeilen" = Table.SelectRows(#"Entfernte Spalten", each true)
in
#"Gefilterte Zeilen"

[Anonymous]

HI @Anonymous,

I think the issue may related to your 'expanded profile' steps. 

If each cell has store a table with huge amount of data, these expand tables will cross join with current row.
These operations will try to load data from database and increase the table size to the count of total expand table rows.
In addition, it will also add columns from expand tables to current table.

Regards,

XIaoxin Sheng