ODBC Connection Performance

[suppahc]

How can I find out what is driving slow run times when connecting to an ODBC SQL database? I'm using 03.52 ODBC driver. When pulling in 10GB worth of data from ODBC, it can take over an hour. Is this normal?

[TheOC]

hey @suppahc 

I am unsure of the typical time to pull 10GB worth of data from ODBC - it can be highly dependent on internet speed, the processing speed of the server, and any potentially throttling on the server-side of things.

One thing I would recommend is using the in-db tools to first clean up and prepare your data prior to extracting, you may be able to reduce the amount of data you need to extract. You can find more about this here:
https://help.alteryx.com/20221/designer/database-overview

Cheers,
TheOC

[suppahc]

Can this tool be used with dynamic inputs? I'm using a configuration file to read in dates and then use the dynamic input tool to substitute dated tables with the dated tables I need based on a configuration file. 

[suppahc]

You are correct. It is my VPN. I used a machine directly connected to the network and it pulled the data in 3.5 minutes.

[TheOC]

hey @suppahc 
The in-db tool suite does contain a dynamic input in-db. This may be what you need for this process if you want to use the in-db route:
https://help.alteryx.com/current/LockInDynamicInput.htm

And yeah, anything that would slow a connection (like a VPN) will heavily impact your download speed. 3.5minutes is much more reasonable for 10gb of data.

Cheers,

TheOC

[suppahc]

So I'm testing performance and I'm having issues with the In-DB portion. I was able to run everything and it took 1.5 hours in total without using any In-DB tools. Now I'm trying to do a portion of the work In-DB and see if it cuts down on runtime.

I'm getting an error for a join: left and right side must have the same in-db connection.

My left input to the join comes from a dynamic input tool where the connection is New Server.

My right input to the join is a local file I'm using data stream in and creating a temporary file with the connection name new server. I've tried to rename it so that its New Server and still getting issues with this error.