Connect In-DB tool

[amnac]

I'm trying to run this subquery in alteryx design using Connect In-DB and Browse In-DB tool as follow: 

my query is like that

I have used visual query builder to develop this subquery. Now the problem is when I run it, it takes 40 minutes to run and nothing shows up on the browser result window

I need guidance with this problem and how to optimize this workflow

[apathetichell]

Trunc this into 3 separate Connect In-Dbs. Use Join In-DBs to join. Add browses after each In-DB.  See where the workflow lags.

Key questions are:

1) How large is your table

1a) How large is your Compute Warehouse/Cluster if applicable

2) Have you changed the config of the Browse-In-DB tool to go beyond 100 records?

[Felipe_Ribeir0]

Hi @amnac 

It seems that you query is not returning data because you have some of these 2 situations (maybe):

1)Are you sure that you are using the right keys to do the JOINS? If some of these keys are not the correct keys, it could cause the situation where your join return no data because some of the keys do not match (like SECLOC_SWC_CODE = FA_CS, maybe it is correct, but it would be good to check out)

2)Are you sure that you have data on the DB for the WHERE condition that you made? And this WHERE condition makes sense based on the context of the DB? Vendor_NAME Like %ABC% for example

I suggest you to try to run just the subquery and see if you have some rows on the response. You gonna need to debug it, the problem seems to be the query itself, something is not correct and the DB does not have rows that match the condition that you created.

[amnac]

I have tried with 3- separate Connect In Dbs and joining them like this:

It is now taking 6 hours to run and still no output after the last joint

[amnac]

I have verified the key, still nothing
also I did what somebody else suggested to look for the issue:

I have outputs at the first joint and from the above connection, it is somehow not joining at the end 

[apathetichell]

get ride of all of the joins. get rid of all of the browses. add summarize in -dbs. summarize on your join fields. count the number of times they occur.

My hunch is you think you are joining on a unique id which is either null  - or not unique. If it is null/empty - there are other nulls/empty it is trying to join. Basically your join is becoming multi-dimensional and what should be say 50,000,000 rows is becoming 500,000,000,000 rows. Or something like that.

[Felipe_Ribeir0]

Hi @amnac 

Can you see rows at these two browse tools?

f you can, you must understand why your last join is not working. Pick just one row of the above part before the last join and see why the key do not match with the part before and bellow the last join

[apathetichell]

My hypothesis is that the join is trying to join too many records- which would happen if there is a join which is joining non-unique fields.

[amnac]

So I break my query into 2 queries, one of them is working when I join my subquery to one table. but when I join the same subquery with another table I get empty output.

this is the subquery

this is the main query calling subquery

this is SQL for it

Output: Orange fields come out from thw subquery Query1 but after join no values in new fields

[apathetichell]

You need to look at your underlying data. Whatever is happening is most likely not because of Alteryx. Some issues:

1) Your datatypes are different.

2) You have case/formatting differences creating no matches.

3) Something you think is unique - is not unique.

You have access to your data - we don't - so you really need to be the one checking this and taking samples. I'd use a summarize in group by mode to compare the values in your match fields... Do they look the same?