Performance Issues with SAS Data Load in Spotfire 10.3

[Terence Carder]

I'm currently experiencing some seriousperformance issues when adding SAS datasets to my dashboard from a network drive!It appears as though the issue is related to the number of datasets in the source folder.

In my folder I have 135 SAS datasets and if I try to add any one of them it takes between 20-30 minutes to import each dataset. However, if I remove all but one of the datasets I can import it in a matter of seconds.

Has anyone got any suggestions on how I can improve performance, or,better still, any suggestions on smarter ways of importing/replacing large volumes of SAS datasets

Cheers,

Terence

[Eric Weise - Revvity]

Hi Terence,

I know this is an old thread but I am wondering if you found a solution. I am facing the same issue --- performance degrades relative to the number of files in the folder on the network share, and also there also may be some relationship to the geographical distance between the location of the Analyst Client and the location of the files.

I believe it has something to do with how the SAS Providers for OLE DB works. It's very fast when the Analyst client and the folder of datasets are local to each other (regardless of the number of files in the folder), but if I take that same folder of datasets and put it on a remote network drive, performance is miserable and in severe cases, the process times out before it even starts the import.

The only solution I have found so far is to move or copy the folder of datasets to a location very close to the running Analyst client, which is often neither practical nor desirable to do.

Any insights you have would be highly appreciated!

Cheers,

Eric

[Olivier Keugue Tadaa]

Hi Eric

I recall that SAS Providers for OLE DB do not support multi-threading. So maybe you. could try it from Spotfire  Analyst with a single data table first.
Then disable the "parallel data loading" from the Analysis and the preferences to load all your data tables sequentially.

You can find these settings following these guidances

Let me know if this helps

Quote

 

Using the installed client, it is possible to switch off the parallel data loading entirely under Tools > Options > Document > Data loading, 
or, for a single analysis, through File > Document properties > Compatibility Settings.

An administrator can also control the preferences for a group in Administration Manager > Preferences > Application > DataImportPreferences > ForceSerialDocumentDataLoading.

The ForceSerialDocumentDataLoading preference overrides the Tools > Options preference.

Custom data sources can be specified to execute on the application thread by overriding the API property AlwaysLoadOnApplicationThread.

 

ForceSerialDocumentDataLoading

Set to True to force loading of all imported data to be done in a sequence, instead of letting data from multiple sources load simultaneously,
 on different threads. Default is False.

LoadDocumentDataInParallel

Lets you specify whether the Allow data to be loaded in parallel when opening documents check box in Tools > Options > Document should be enabled by default. This setting allows
 data from multiple sources to load simultaneously, on multiple threads. Default is True.

By switching to False, the default setting is to clear the check box, but the end users can still change this setting themselves

 

 

[Eric Weise - Revvity]

Hi Olivier,

Thanks for your reply! Unfortunately this doesn't seem to be related to the parallel data loading option, as the problem occurs even when trying to import a single dataset into a new, empty DXP.

Today, I tried to open a 512Kb SAS data file that is in a folder on a remote drive containing 89 other SAS files, both with and without the parallel data option enabled at the Analyst level. In both cases, the import process timed out after 10 minutes without even getting so far as to display the SAS Data Import screen. I then made a copy of that file and saved it in its own folder on that remote drive, and when I tried to open that copy, the entire import took 15 seconds (5 seconds to display the SAS Data Import screen and 10 seconds to actually import the data). So disabling the parallel data loading option made no difference.

However, I have noticed an odd behavior in a different scenario that may give a pointer to the underlying cause. In our environment, we also save files in Microsoft OneDrive. Files that are not often used do not get automatically synched to our laptops, Instead, they get synched only when they are called from an application. In every normal case, if an application accesses a OneDrive file, only the needed file gets synched to the laptop. This includes when accessing something like a CSV or XLS file from the Spotfire Analyst client.

However, this is not what happens when the Spotfire Analyst client accesses SAS files in OneDrive. If I have multiple SAS files in a OneDrive folder and I try to access just one of them from the Analyst, every single SAS file in that folder gets synched to my laptop. So if I have 50 SAS files in a OneDrive folder and try to open just one of them with the Analyst, all 50 get synched. This implies that the other 49 files are all somehow also being touched, interrogated and/or queried through the SAS Providers for OLE DB in one way or another, even though they are not in any way involved in the Spotfire import.

Assuming this behavior is not specific to OneDrive, it goes a long way toward explaining the symptom that Terence and I observe. If every SAS file in a folder gets interrogated every time just one of them is accessed from Spotfire, performance is certainly going to degrade in relation to the number of SAS files in the target folder. The questions then are, what is the root cause of this behavior, and what can be done to address it (other than relocating the source files as I described earlier)?

Any ideas or help you can provide would be greatly appreciated!

Cheers,

Eric

[David Boot-Olazabal]

Hi Eric,

Can you confirm if you're connecting to SAS with an OLE DB connection (see image below)?

If that is the case, I would advise to create a support ticket for this issue, via https://support.tibco.com. It would be good if you could share the ticket with us, so we can also look at it.

Kind regards,

David

[Eric Weise - Revvity]

Hi David,

Thanks for your reply!

No, we are not using an OLE DB Connection for this. We have installed the SAS Providers for OLE DB, which is essentially a driver that adds the ability to import a SAS file directly via Browse Local File:

 

     

Cheers,

Eric

[David Boot-Olazabal]

Hi Eric,

Thanks for clarifying. 
In the case, it is also advised to create a support ticket and let our support team have a look at the issue.

Kind regards,

David