topic Re: Significant Workflow Run Time Difference between Designer and Server in Alteryx Server Discussions

Significant Workflow Run Time Difference between Designer and Server

Akash2093 — Fri, 16 Apr 2021 16:33:53 GMT

I have a workflow that reads multiple tables from a 2 difference MS SQL server DBs and processes approx 10M records. After all the transformations write 2 outputs to MS SQL Server DB table as well. (Using the bulk write connection)

It is very weird that workflow takes 6-7 mins to finish in designer whilst its taking 45-50 mins on Gallery server.

I understand there is generally run time difference between designer and gallery due to server location and all. Our server is situated in UK, so in other workflows we have experienced 5-6 mins of latency in a workflow that used to take 15 mins on designer.

Any suggestion what I can keep an eye for to improve the run time on gallery? (anything around 15-20 mins will be acceptable)

Re: Significant Workflow Run Time Difference between Designer and Server

ScottG — Thu, 22 Apr 2021 15:49:25 GMT

I don't have a silver bullet for you but I would first turn on Performance Profiling to make sure that the delay is actually in reading/writing from SQL Server. It may be a different portion of the workflow.

Also, I would recommend using the In-Database Tools as much as possible to reduce the movement of data between the database and Server.

Re: Significant Workflow Run Time Difference between Designer and Server

Akash2093 — Wed, 28 Apr 2021 10:27:00 GMT

Sorry for delayed response, i observed that this delay happens sporadically. Out of 5 times, 1 time it will take ~50 mins and the other times it will complete by 13-15 mins (which is acceptable). I can also confirm this delay is not due to queueing.

When i turned on performance profiling on gallary, turned out a tool/process with id "-1" is taking 95% of overall process time. While checked in designer there is no process or tool with this ID, any suggestion what is might be?

Re: Significant Workflow Run Time Difference between Designer and Server

ScottG — Wed, 28 Apr 2021 21:27:52 GMT

Hmm. Given your description, I would look very closely at the resources that are available on the Server, in particular the amount of available memory and the amount of available disk space. You might even use something like perfmon to see resource usage while this workflow is running.

Tool ID -1 messages are typically messages that are unable to be related to a particular tool, for example, often memory limit warnings.

Given that this is happening inconsistently, that also lends itself to an issue that is happening because of other load on the Server. When there is less load, it runs in a reasonable amount of time. When there is more load, it takes longer.

I would also make sure that the CReW Runner Macros are not running on the Server. They kick off workflows that aren't added to the queue.