Recommended ways to improve 'Data on Demand' performance?

[Priyank Dwivedi]

Hi,

For a particular SQL server query, the time it takes to spit out results is less than 1 sec when used on SQL management studio but when connected to Spotfire as a data table via 'data on demand', the same query is taking more than 30 secs! In some cases, 50 sec..

Spotfire Version: 11.4

Are there recommended ways to enhance performance in this scenario?

[Fredrik Rosell]

Hello,

The key first step is understanding where the time is being spent - if it's e.g. a matter of execution time, or time spent transferring the data from the database.

How are you connecting to the database, are you e.g. using an Information Link? If that is the case, here's a Knowledge Base article that describes how to troubleshoot performance issues with Information Links:

https://support.tibco.com/s/article/Troubleshooting-Guide-Performance-issues-with-Information-Links

[Priyank Dwivedi]

Hi Fredrik,

I am not using the information link but a simple 'connect to ->Microsoft SQL Server' way to connect to the view.

[Fredrik Rosell]

As you said "For a particular SQL server query", should that be interpreted to mean that this works well in general, but you only observe issues with a particular query? If so, how is this one different?

Make sure that you are comparing the same exact queries (as you are using on-demand, which will of course make that dynamic).

Are you running Spotfire Analyst on the same machine as SQL Management studio? 

I would start by comparing queries that will return only a few rows (to minimize the impact of time of data transfer as all the data will be imported in Spotfire,

If you enable DEBUG logging in your Analyst (Help menu > Support Diagnostics and logging > Logging tab > Log Level = DEBUG - while you are there, click on "Open Folder" to open the logs directory), a log file called Spotfire.Dxp.QueryLog.log should be created, which e.g. logs the duration and number of rows for each query using connectors. That could be a good way to start gathering data for analysis.