SQL input taking a long time to load

[katie]

Hi,

I have a workflow with around 10 oracle SQL inputs. They are all OCI inputs and the workflow takes around 2 hours to run. Once the inputs are cached it takes under 10 seconds so it's purely just loading the inputs. It's not an overly complicated workflow I've had other workflows with more complicated tools and more inputs but for some reason this one is getting stuck.

None of the inputs are more than 500,000 rows or more than 30 columns and when ran in oracle itself each query takes under 15 seconds to run. Any thoughts? I am on a VDI as required by my company but this has been the case for the last 4 years and hasn't been an issue. Turning of the AMP engine seems to help a bit but its still taking an unusually long time.

[caltang]

Are you using the standard input tool or the in-db tools? 

[caltang]

Some content here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Reading-Data-from-SQL-Running-... 

[apathetichell · Accepted solution]

In my experience the main reasons for this could be happening:

1) Memory limits on the local system -> you can look at memory monitoring your query and see if it's peaking or nearing peaking. Alteryx might not tell you this. Check out resource monitor to see it.

2) Data limits -> is your data on OIC (Oracle Cloud Infrastructure?) and your VDI is on OIC? If so this shouldn't be the case - but resource monitor can also point to issues here. If you see them - flag them upstream because they'd point to issues in the underlying VPC/connections in Oracle. If your VDI and your databases are in two different locations look at the VPN connecting them and any bandwidth limits there.

3) Hanging queries - sometimes in some situations (frequently changing databases) the Query may hang. Alteryx isn't sure how much data there is and keeps waiting. and waiting. add a limit 1,000,000 clause to each query and see if that helps.

Keep in mind that input data is an in-memory operation so the query results are being pushed to local memory as part of execution. Use In-DB to avoid this and only retrieve results after transforming/aggregating as needed.