Workflow with SQL having temp tables executes on local but keeps spinning in Gallery

Question

Hi All,

I have a workflow with a complex SQL which executes well in Alteryx desktop and completes in 40 mins. The same workflow when deployed in Gallery keeps spinning without completing.

The SQL logic comprises of temp tables and the workflow basically pulls data from SQL and populates a SQL table. A single run considers around 100k rows. The workflow gets executed in gallery for few number of rows. I am hoping someone might have gone through this issue earlier and can help me get through this.

Ashwin_Chavan · Answer

Hi, Thank you for your inputs. I checked on below and had not luck.

1. DB Driver - They are the same on my local and server

2. Firewalls does not looks like a issue coz if the workflow gets executed for 15-20 records then it should work for rest

3. The server is taken care by admins so we do not have much control - but i believe i should have received a Out of Memory instead of infinite spinning if it was a memory issue

4. I tried In-DB tool which also is not working out on local to start with

5. The In-DB tool stream out fails with a generic error...

joshuaburkhow · Answer

Hey @Ashwin_Chavan

There are a whole bunch of areas you need to look at here:

* DB Drivers and DB connections - I run into this a lot! Users drivers and versions don't always match what's on the server, also the data connections (OLEDB vs ODBC) 
* Make sure the server is configured correctly - I still find this to be the problem for smaller or new installations (don't know your situation) ensure that the right amount of jobs, memory, etc is setup per requirements
* Line of sight - If you have a relatively secure environment you will need firewalls that open up the connection from the server to the database. Make sure those are in place. 
* Ensure you are using in-db tools. Your use case seems perfect for this and you should see some serious improvements if you aren't using them yet. 
* Look at the Service Logs, and the Worker logs - they will tell you what is happening most of the time fairly clearly

This is just a start but let me know if you have more questions!