Making Your SharePoint Input Tool Work Faster

[aatalai]

Given the configuration of our Alteryx Server, we require the SharePoint Input tool to bring in data into our workflows. When we had a version upgrade (Designer and Server upgrade from 2023.1 to 2024.2, and SharePoint Input tool upgrade from V2.4.2 to V2.7.2), we noticed that one of our workflows would take over 3 hours to run; after investigation, it was from reading an Alteryx database file (.YXDB) from SharePoint that was holding it up.

Started at 13:21:59 and still hadn’t finished by 18:37, well over 5 hours

After exploring the tool, we noticed that the download file was locally available for later processing options.

Note: the default file location is %TEMP%/SharePoint, however, it was suggested to use %Engine.TempFilePath% as that can resolve to a different location than where the Alteryx Server has been configured to use as its temp drive.

Reading the file(s)

There are 2 main ways we have come across to read files downloaded: a) using control containers and Input tools, b) using a batch macro with the file path of the temp file and opening it that way.

Control container method

We have selected the file that we want to download and saved it to the engine path. The Input tool is configured to read the file that was downloaded.

%Engine.TempFilePath%\[filename including extension]

If you are opening an Excel file, you will also need to specify which sheet name you want the tool to open.

Using a batch macro

The results from the SharePoint input tool look something like this:

The following step will be to pass the file path to a batch macro, updating the file it is opening.

Setting the action to update the file path:

Save the macro and insert it into the workflow, and set the control parameter to the save path.

This helped us reduce our runtime from over 3 hours to less than a minute.

Please note that this has been our most significant case; most cases are moving from a few minutes to less than 10 seconds for data import.

Conclusion

1. Reading files directly from the SharePoint input tool can take longer than downloading them and opening them in the workflow using an Input tool
2. Update the download file location to %Engine.TempFilePath%

[BS_THE_ANALYST]

@aatalai if we download the files, would this mean we have wider capabilities for reading? i.e. can you download any file type in the document library? If memory serves me correctly I couldn't read .json files with the Sharepoint Input tool. 

I've also found workflows to be a little less responsive when the Sharepoint Tools are on the canvas. Disabling autoconfigure helps a bunch for development!

Thanks for sharing this tip though! I'll be certain to use it ✅

[aatalai]

@BS_THE_ANALYST , you have beaten me to the punch, as one of my future blogs will be on other file types (though I have not tried JSON, so I can't comment on that specifically) 

[pilsworth-bulien-com]

@aatalai, I tried to investigate the difference between the two temporary locations, and it looks like the %Engine.TempFilePath% is a workflow-specific temporary directory, presumably generated at run time. I never realised that Alteryx did this, I always assumed everything went in the default Windows temp folder %TEMP%. I wonder why one is recommended over the other. Is it just because nothing else can access the workflow-specific section? 

Those time savings are very impressive, I can already think of some workflows that would benefit from this

[aatalai]

@pilsworth-bulien-com, not sure why one is recommended over the other tbh. 

[Gaurav_Dhama_]

Engine directory is session specific, so your memory is empty as soon as the workflow is done with its process. If same workflow is running twice at the same time, then if they use a common temp directory, there might be chances of overwriting or blocking flies. Keeping it in an engine directory temp folder will isolate the files to the process running.

For example,

If my workflow writes a file called abc.yxdb for intermediate step, into system temp location.

Now if two workflow runs at same time, then there are following possibilities:

1. The first workflow completes faster and deletes the abc.yxdb file (because every process is responsible for deleting their own temp files as per basic system process) then the second flow will not find it and fail.
2. The first workflow is still use it and second one fails because it is in use, or vice versa.

Let me know your thoughts.

[caltang]

Interesting. The SharePoint tool has been a nightmare for me and my users... the loading has been insanely slow. I'll be sure to test this, thanks @aatalai ! @Gaurav_Dhama_  raised good points as well.

[aatalai]

@BS_THE_ANALYST take a look at this for bringing in JSON files from sharepoint