I solved a similar issue with a 6 millions rows table.
2 options:
1) get all info by blocks:
=let
// Define the function to get a page of data
GetPage = (Offset as number, Limit as number) as table =>
let
Source = Odbc.DataSource("dsn=[NAME]", [HierarchicalNavigation=true]),
Query = Odbc.Query("dsn=[NAME]", Text.Format("SELECT * FROM [TABLE] LIMIT #{0} OFFSET #{1}", {Limit, Offset}))
in
Query,
// Define the block size
BlockSize = 100000,
// Define the number of parallel queries
ParallelQueries = 10,
// Function to get data in parallel
GetParallelData = (StartOffset as number, EndOffset as number, Step as number) as list =>
let
Offsets = List.Generate(() => StartOffset, each _ < EndOffset, each _ + Step),
Tables = List.Transform(Offsets, each GetPage(_, Step))
in
Tables,
// Get data in parallel
DataBlocks = List.Transform({0..(ParallelQueries-1)}, each GetParallelData(_ * BlockSize, 3000000, BlockSize)),
// Combine all tables into one
CombinedResult = Table.Combine(List.Combine(DataBlocks))
in
CombinedResult
2) get only needed fields at once:
=let
Source = Odbc.DataSource("dsn=[NAME]", [HierarchicalNavigation=true]),
Query = Odbc.Query("dsn=[NAME]", "SELECT column1, column2, column3 FROM [TABLE]")
in
Query
