Foreign Key Lookup Best Practice

[mutuelinvestor]

I'm reaching out in hopes of learning how other deal with foreign key lookups in Alteryx.

I have workflows that are destined for destined for a Microsoft Azure SQL Database table.  However, before I can execute the insert, I often need to look up a foreign key value from another table in my Azure SQL Database.   Currently, I'm using two different methods to perform this task.

1. Dynamic Input tool with a dynamic where clause.  - This approach is straight -forward, but I find it extremely slow and is just not viable for a large dataset.

2.  My second approach is to stream data out to a temporary in-database table, use the In-database connect tool to query the table containing the foreign key.  Then I perform an in-database join to get my foreign key and finally I stream the data out of the in-database tool back to the original workflow.

The second approach is faster than the first, but far from elegant.   

If anyone has an alternative approach that works well I would be very interested in hearing from you. 

Thanks!

[HenrietteH · Accepted solution]

Hi @mutuelinvestor

Both queries are being executed on the database, so the slowness is probably related to how the queries are constructed. 

The dynamic input tool gives you an option to use an IN clause in your sql query which will be faster than using Select * from table where key = 'value'. 

If you update a query such as that one, Alteryx will send individual queries for each value to the database, with an IN clause it will only send one query. 

In order to do that, set up your query in the tool with an IN clause, for example: 

Then select 'Update WHERE clause' as the option under Modify SQL Query: 

and select the portion of the WHERE clause you would like to udpate making sure the "Group replacement value for IN clause" option is checked: 

Since my example has a very short WHERE clause, Alteryx pre-selected it for me. 

Running this setup in parallel with a standard setup you can see that Alteryx runs fewer queries: 

(Dynamic Input (1) is running the IN clause, Dynamic Input (4) is updating the query Select * From PROD.dbo.CO_STORES_TEST where StoreNum = '12345')

[mutuelinvestor · Accepted solution]

Hi @HenrietteH

Thank you for the perfect solution.  I can't tell you how much time this shaved off my workflow. 

Many thanks,

Jim

[HenrietteH]

You're welcome Jim @mutuelinvestor! 

[mutuelinvestor]

Hi Henriette, 

I thought you might be interested in knowing that your suggested change to the Where In() clause resulted in the follwoing peformance improvement:

• Where Clause Time:  13 Minutes 13 Seconds
• Where In() Clause Time:  53 Seconds. 

That's Huge -  Thanks again!

Jim