Speeding Up Queries: Stopping Repeated Queries?

Charley

Member
Joined
Jul 29, 2017
Messages
64
Reaction score
0
Points
6
Excel Version(s)
365
Here's my situation:

Step 1: I hit a web site 2 times to build a table with the 105 categories of pages to retrieve.
Step 2: I hit it 105 times to build a table with the 2393 page IDs to retrieve.
Step 3: I hit it 2393 times to build a staging table with data from each of the pages.
Step 4: I hit each staging table with two queries to generate my final results.

In all, I use 11 queries and custom functions. Most of the queries are Connection Only.

I've sprinkled Table.Buffer throughout the code, with no apparent success. To illustrate, the queries in Step 4 continually report that they're waiting on the source site. But because they're supposedly working on a buffered table generated by Step 3, I wouldn't expect them to EVER have that problem. Instead, Step 3 is the one that's hitting the source site, not Step 4.

(I know that M code isn't procedural, so that might have something to do with it.)

Question: Suppose QueryA returns tabular data from a web site and then queries B and C return results from Query A's table. Which should produce faster results for queries B and C?...

a. QueryA returns unbuffered Results and queries B and C each begin with: let Source=QueryA (Logically, this should give me the worst performance.)

b. QueryA ends with: Buffer=Table.Buffer(Results) in Buffer -- and queries B and C each begin with: let Source=QueryA

c. QueryA returns its results to a worksheet and then B and C each query the worksheet's Excel Table to generate their results? (That is, does a worksheet Table work as a RELIABLE buffer?)

Question: In general, what's the best strategy to ensure that this series of queries is hitting each page only once?

I'm about ready to split this solution into four separate workbooks, and then copy and paste values between them so I can reliably buffer each step.

What's the best strategy here?
 
Back
Top