Multiple Excel-file input stalls using AMP engine

[StefanW]

Hi,

I often need to load data from multiple Excel-files that have the same data structure. I've tried the AMP engine in order to speed up the load process, but the process stalls and does not finish within the expected time (see MultipleExcelfilesWithAMPengine_12files.png).

Without AMP engine, the process finishes correctly (see MultipleExcelfilesWithoutAMPengine.png).

Oddly enough, if I limit the input to 11 files, the process runs through even using the AMP engine (see MultipleExcelfilesWithAMPengine_11files.png). But the year has twelve months :-(

Does anybody have an idea why this behaviour occurs? What could be a solution or (less preferably) a workaround to this behaviour?

Many thanks in advance!

Stefan

[mceleavey]

Hi @StefanW ,

the obvious problem I can see immediately is you don't have enough RAM to run AMP. You can see the warning you're getting in the results window.

If it runs without AMP, then that's your answer, otherwise get some more RAM and run the test again.

Hope this helps,

M.

[TheOC]

hi @StefanW 
I've noticed with AMP engine, as @mceleavey states, it limits you to 8Gb - however, you may need more for this use case.

In the Runtime tab, you can adjust the amount of ram to give the workflow:

Give this a try with a higher amount of ram - if this does not work, let me know and I'll have a deeper look.

Cheers,
TheOC

[StefanW]

Hi TheOC,

Thanks for your suggestion.

I tried processing the 12 files of a total file size of a bit less than 1 GB using 16 and 21 GB of RAM, but to no avail. I reduced the size of the 12 files with the sample tool and N=2 to a total of 360 MB, but again the process stalls using 16 GB of RAM for the AMP engine. I then tried the load process with the file sizes reduced by 4 to a total of 180 MB. The process finishes correctly, even when limiting the available memory for the AMP engine to 2 GB of RAM. 

It appears to be a memory issue, but I don't see a pattern that would help in finding a solution or understanding the precise limits for file sizes. Do you see any solution?

Kind regards,

Stefan

[TonyaS]

Hi there, 

Have you by chance tried using a Block Until Done tool at all so that all the files are not trying to read at the same time? With AMP you would need to take note of the Input Tool Ids and make sure to put the BUD tool after the one with the lowest Tool Id.  Its usage is described on this page: https://help.alteryx.com/20223/designer/alteryx-engine-and-amp-main-differences 

Parallel Branch Execution and Tool Run Order

Some workflows read from a file and then write back to it. This requires sequencing to ensure that the read is complete before the write can start. Similarly, a workflow that wants to write several sheets in one XLSX file needs to write the sheets one at a time. Alteryx Designer provides a Block Until Done (BUD) tool to help partition the work into phases that won’t get in each other’s way.
Same workaround is applicable for Email tool when you use output file(s) from previous branch(es) as attachment. You need to wait once data processing is complete and then add as attachment to the Email tool.

When you work on a workflow with multiple branches (largely separate streams from inputs to outputs), place the BUD tool in the workflow branch with the lowest numbered Input tool ID. This ensures every subsequent branch waits to run until the previous branch is done and the tool works as expected.