Strange Iterative Macro behaviour

[jevon]

Hello,

Coming across some weird behaviour regarding iterative macros. I've used the same macro in two different cases. First case I've attached a select tool to the output of the iterative macro. The second case, I've attached a sort and select tool to the output of the same macro as shown in the picture below. The top stream (without the sort tool) takes way way longer than the bottom stream. And it's not just for the sort tool. The same happens for the summarise and unique tool, so I'm guessing this behaviour occurs for any tool which performs any sorting (sorting tools: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Sorting-Processes-in-Alteryx/ta-p/37336). I would've thought the sorting occurs after the iterative macro and therefore would make it slower not faster... Is there a reason why this happens? I've attached the workflow if that helps. Any insight would be greatly appreciated!

@AdamR_AYX 

[danilang]

Hi there @jevon 

I'm not seeing the same results as you are.  

ItMacro(3) 6305ms

itMacro(26) 6221ms

There is no significant time difference between the 2.

I went a step further and split your workflow into 2 thinking that there may be some caching involved.  The 1st workflow has the top branch, the second has the bottom.  Again the results were almost identical.  6312ms vs   6496ms 

One thing I noticed in the combined workflow is that the bottom branch runs before the top branch.  Could the difference in run time on your machine be related to resource issues.  When the top branch runs, does your machine have to swap to disk?  This could also account for the overall speed difference between your machine and mine

BTW.  Since your re-reading the yxdb on every iteration of your macro and it's the same file as your main macro input, you could improve the performance by doing a self-join on the input data and avoid re-reading the data

Dan

[Thableaus]

Hi @jevon 

From what I see the first block is having trouble in the Join Tool of the iterative macro.

Iteration #1: Tool #14: Profile Time: 13.49ms, 81.54%

Iteration #2: Tool #14: Profile Time: 12.94ms, 88.06%

Iteration #3: Tool #14: Profile Time: 13.44ms, 86.88%

Iteration #4: Tool #14: Profile Time: 13.21ms, 87.95%

Tool #14 is the Join tool. It's taking 80-90% of time of each iteration.

When you run the second block, you don't see this kind of impact.

Maybe the sort tool accelerates the join process of the iterative macro at some point? I'm not so sure, I think an Alteryx engineer could help to clarify this question.

Cheers,

[danilang]

That's interesting @Thableaus 

If I look at my output, I have the same(approx) impact for the join tool in both branches

Info: ItMacro (26): Iteration #1: Tool #24: Profile Time: 6.84ms, 47.87%
Info: ItMacro (26): Iteration #2: Tool #24: Profile Time: 3.34ms, 41.59%
Info: ItMacro (26): Iteration #3: Tool #24: Profile Time: 3.08ms, 41.33%
Info: ItMacro (26): Iteration #4: Tool #24: Profile Time: 1.76ms, 43.51%
Info: ItMacro (26): Iteration #5: Tool #24: Profile Time: 1.57ms, 42.69%

Info: ItMacro (3): Iteration #1: Tool #24: Profile Time: 1.53ms, 37.96%
Info: ItMacro (3): Iteration #2: Tool #24: Profile Time: 1.49ms, 40.79%
Info: ItMacro (3): Iteration #3: Tool #24: Profile Time: 1.37ms, 40.92%
Info: ItMacro (3): Iteration #4: Tool #24: Profile Time: 1.36ms, 40.23%
Info: ItMacro (3): Iteration #5: Tool #24: Profile Time: 1.49ms, 41.98%

Dan 

[Thableaus]

@danilang 

That's interesting. I actually put each block in containers and ran the test separately. Did you do that as well?

Cheers,

[danilang]

@Thableaus 

I actually put each branch in its own workflow as well as running the original.  In all cases the Join uses about 35 to 45% in the first 10 or so iterations

Dan

[danilang]

@Thableaus , @jevon 

How much Sort/Join Memory do you have allocated.  I have 8GB.  Maybe this is the resource that needs to be recycled

Dan 

[Thableaus]

@danilang 

I think it's 4GB (default). 

Didn't change this configuration.

This might be relevant, but the weird thing is both blocks do the same join process. That's the point.

Cheers,

[danilang]

@Thableaus 

On my machine, the bottom branch, with the attached sort, runs first.  Maybe by the time the top branch runs all the dedicated sort join memory has been allocated and needs to be reallocated, slowing the process down.

How much total RAM do you have?  I have 32GB with only about 10GB allocated at the moment.

Dan

[Thableaus]

@danilang 

I think that's not the case. I disabled one container while running the other one.

So basically each process runs separately. There's no need to reallocate memory.

Cheers,