Connect In-DB tool

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

Database Connection

Accepted answers

All comments

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?

amnac

thank you all, I have somehow managed to get desired output by not using DB Join in my query. like this:

apathetichell

stupid question. is one of your DBs (the upper most) housed on a different server than the others? Or did you change a field type in the Join? Basically the way you solved your issue is downloading all of your data. Glad it worked - but not really sustainable...

amnac

No question is stupid
All DBs are same server from what I know. I tried with DB tool with same setting DB Joins it gives blank output.

amnac

I have the following workflow and it takes 8-10 hours to run, is there any way I can make it fast

I jave to use data stream out because all the processing tools I need are not in DB-Tools

apathetichell

Do as much processing as possible In-DB. You can use summarize in-db in group-by mode as a unique tool. You should not be using a Join on canvas if both tables are in the same DB.

amnac

how do we use summarize in-db as a unique tool?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703