Speeding up a macro with API calls

Hello clever people,

I've built a macro that goes to a 3rd party website and fetches a considerable amount of data (we're talking millions of rows, dozens of columns). It is very stable and has delivered great value to a lot of people. The users input their API key, the model ID, the table ID, and click the run button.

It is, however, slow. It takes about 9-10 minutes to fetch about 1M rows, and it's not uncommon for me to get as much as 10 M rows. The data changes frequently, so I do need to update the whole thing every time.

The 3rd party has a page limit of 10 K rows and 10 MB, and they can't change that. However, they have recently been two new pieces of information that I learnt:

1) the 3rd party developed a new API endpoint

2) the 3rd party can issue multiple API keys for service accounts

So with that new info: can I finally make my macro faster?

Now I will explain how the version of the endpoint works.

This new endpoint returns a unique page key given the target page size, table ID and model ID. So if we have a table with 15000 rows and we set the page size to 5000, we can call the data using these three URLS:

https://api.blablabla/pageSize=5000&key=[###]

https://api.blablabla/pageSize=5000&key=[###1]

https://api.blablabla/pageSize=5000&key=[###C]

My macro is an iterative macro within a standard macro. The iterative macro fetches all the URLS that are needed to get the data from the target dataset. The standard macro takes those URLs and sticks them into a download tool, then parses out the JSON.

I'm looking for creative ideas on how to make this whole thing work faster, considering that I can use several different API keys. I want to get a substantial speed improvement (x2 at least).

What I have tried so far (and nothing produced results):

1) In the standard macro piece, tile the output of the iterative macro and parallel the downloads

2) Increasing the number of connections in the download tool - cannot go over 32

Many thanks in advance for the help to this wonderful community!

API

Download

Accepted answers

All comments

simonaubert_bd

Hello @m_v Very interesting topic. Question : have you identified where it is slow exactly?

m_v

It's the iterative piece (URL fetching) that takes a long time.

m_v

Specifically (just ran a test): current version which I describe in the post took 7 min for 1 M records, and 2 min 18 sec with a static list of URLs.

KGT

I don't have a clear answer for you but hoping my commentary helps see another avenue.

Looks to me like there are 3 specific avenues

Speed up each call in the iteration
- This could be checking if multiple calls can be made at once, or testing what the fastest method is to work with this particular API.
Speed up each iteration
- The workflow looks pretty simple, I don't think there's anything you can move outside the iterative macro from after the call... Might just want to check that there's no throttling happening. Try it with 100k, and see if the time is somewhat linear. Also, Try feeding it 9-10k and then wait 3 seconds, and another 9-10k. Just to check for throttling at 10k requests.
Change the iteration
- I'm not sure exactly how, and maybe the only options are related to the above.
- Can you construct the first call of each group into one iteration (assuming that there is groups and it's not that every call except the first one needs the response to generate the next row), and then all the responses for the 2nd pages into the next iteration etc? Rather than one call, one response, one iteration.

Basically, each iteration is going to run in series, rather than parallel, so the number of iterations will affect the run time, but API's differ in the way they process/respond and so there may be ways to get quicker responses.

m_v

Thanks a lot for the detailed response.

1) Yes, multiple calls can be made at once. I just don't know how to use this to my benefit.

2) I never do 10K rows as there is always a danger of exceeding 10 MB. I use 9000 and 5000 for certain tables (my logic is embedded in the macro). I experimented with throttling and did not see any difference.

3) Unfortunately I can only get the page keyes in sequence. "Generic" request returns current key and next key only, so I construct the URL to include next key in the call. Perhaps there are ways around it, but I'm not aware of those.

KGT

I might be at my limit then... hopefully someone else has another idea to try.

By multiple calls at once, I meant asking for multiple returns in one call. Some API's have a way to request multiple Customer ID's on one request for instance. Doesn't sound like that's what this is though as all your calls have to be one after the next.

apathetichell

A few things:

1) As long as your process is stateful (ie on your personal machine) - you will be limited by your personal machine resources. That means memory and bandwidth in this case are limiting you more so than Alteryx performance - I would expect that these (particularly bandwidth) would limit you seeing a 2X imporvement.

2) What is your primary goal here - improve this process - or improve this process in Alteryx? Can you tell us a) is the API endpoint you are hitting in a VPN? Do you need any special access beyond crednetials for it. b) what are you doing with your data after processing?

m_v

1) I understand this. Moving the process to the server is possible, but not in scope of what I'm focusing on right now.

2) My goal is to improve the process in Alteryx since I have no other means of getting the data in an automated manner.

a) I do not know anything a VPN present in the endpoint. I only need an API key to access the data.

b) There are multiple workflows where I join the data with other data sources, summarize, output to google sheets, etc.

m_v

Does anybody know if scripting this in python would make the process faster?

apathetichell

Probably depends upon how you'd write it. Like in Alteryx - I'd recommend using multiple rows fed into a single download tool vs an iterative macro or something like that. a batch macro would be useful is core memory is an issue.

A Python script (outside of Alteryx) could be faster depending upon how you code this. You are stlil going to face memory and bandwidth issues. If this is written poorly - it will not be faster. I do not love gsheets native support for Python - and expect this might be an issue for you down the line. Python in Alteryx will not work.

If this was my project - I'd dump the macros and do this entirely in a standard workflow. I do not believe an iterative macro can ever help performance. A batch macro can because of memory allocation (at least on some versions of Alteryx).

You're current performance sounds decent - and my next step would be to do this via python in a containerized cloud environment on schedule (ie ECS/EC2) - and then push the data into my DB/S3.

m_v

The bottleneck of my process is getting the page keys, and I really don't see a way to do that using anything besides an iterative macro. The API call returns current key and next key, that's why I'm looping through the list one by one. Perhaps there is a better way? I've never used batch macros before.

apathetichell

So many of these apis use a rowcount/offset system - with call 1 returning the total count. This allows you to use generate rows and then use floor/ceil division to create the offset commands needed to create the syntax needed for successive calls. If you do not have that row count - you will not be able to do that and you would have to use an interative macro. Is the API documentation public - and if so - can you link to it?

m_v

The documentation isn't public but - I used this exact approach in the previous version of my macro: getting the total count of rows and splitting it into "batches". Then for every batch (record ID) I would run the same iterative macro (green on this screenshot) getting the data. The batches could run in parallel and then the outputs would get unioned together.

Are you saying that I can use a batch macro instead of an iterative macro here?

m_v

I figured it out - many thanks, @apathetichell !!

All this time I did not realize that the download tool can ingest multiple URLs at the same time.

So I completely removed the iterative piece and instead created a formula that assembles urls based on the total page count:

api.blablabla/pageindex=1&pagezie=9000
api.blablabla/pageindex=2&pagezie=9000
api.blablabla/pageindex=3&pagezie=9000

etc

And this whole thing goes in a download tool, no problem.

I was able to decrease my macro run time almost x5 times!!

apathetichell

Great to hear @m_v - can you mark my suggestion as a solution? I'm a big of the multiple call/offset/limit approach.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703