Information about Joins and Cache on Alteryx server/Designer

[Rizwan]

Hi,

So, as introduction I'm 2-3 weeks old to Alteryx environment, I was creating a workflow which included 3-4 files coming from oracle and 7-8 files are text files on a network drive. 

Oracle files are big in size and text files are more like a lookup file to description of codes in oracle table.

The problem is on server this flow is failing due to unknown error, which after going through forum I realised is possibly due to memory exception.

Because when I try to run the same workflow on system it is giving me memory exception which is due to the big Cartesian join it is creating.
So I also tried to use "Find and Replace" tool but it is the same thing.
I'm attaching the flow, it's possible I'm so much wrong here or maybe another way to do it.
Also, I would like to know how the Alteryx create Cache, would it Cache the big table or small table when executing join and where this cache will be created in Alteryx server or in oracle?

Also, I'm moving text files to oracle to see how it would work out, but still I'm curious to know what all the things I did wrong here.

Any help would b e great.

Thanks!

[joshuaburkhow]

I see the screenshot of the workflow but doesn't look like you attached the workflow. 🙂 

[danilang · Accepted solution]

Hi @Rizwan 

This looks like the perfect use case for using the In-DB tools, specifically the In-DB join.  The In-DB tools perform the operations on the Oracle server, thereby side-stepping the issue of memory allocation.  Well...not so much side stepping as transferring the issue to your DB server, which is probably better equipped to handle the load.  Your look-ups are probably already in the DB so you should be able to process the entire thing in DB and just export the results.

If you can't use In-DB tools, you can look at is the use of filters, i.e. only certain years, regions, etc. Try to apply them as early as possible in the chain to minimize the memory explosion.

Dan   

[Rizwan]

Hi, 

Thanks for the In-DB suggestion, this looks great, i'll definitely try that.

However, one question for you, in input tool we can also join the data using query builder, so that join happens on Alteryx side and when I do same thing with In-Db then it will be happening at oracle end, am I correct?

[danilang]

Hi @Rizwan 

When you use the Query builder in the Input tool, you're creating a query string that's executed by the DB server.  If the query includes joins, the joins are performed on the DB server and only the results are returned.

Dan