Spotfire SQL Server Query Performance
I have been tuning our data warehouse database to improve performance of some Information Links. One particular fact table was taking over 5 minutes to load 90,000 rows of aggregated data. I applied a columnstore index to the fact table, and using SQL Server Management Studio (SSMS), the query now takes 2 seconds. Yet the Information Link still takes over 5 minutes to load the data into Spotfire.
The SQL queries are identical. Is this a SQL Server driver issue? The database is SQL Server 2014.
Any help, tips appreciated.
- Are you using the native sql driver or the TIBCO data direct?
- You can use a tool like DbVisualizer (https://www.dbvis.com/) to configure a connection using the exact same jdbc jar driver that the Spotfire server is using. Does it take the same amount of time then? This will tell you if it is a driver issue. (This is ideally run on the TIBCO Spotfire Server machine to ensure the network connection is the same that Spotfire is using. But note that for ILs, the data is first returned to the TSS, and then it is transferred to the client, so there is another hop in the data transfer)
- What is the execution time of the IL as seen in the \tomcat\logs\sql.log on the TSS? That will be the time it takes the query to be executed and data returned to the TSS. You can see the exact SQL that Spotfire is running there (based on your IL configuration), so you can now run that same query in DbVisualizer of SSMS to compare.
I would start with checking those points and based on that we should know where to look deeper.
Last try here before someone takes over in your support case : )
runtime query validation
One possibility is that it is the runtime query validation that is causing the delay. By default the cache lifetime is 5 seconds, but that can be increased in the TSS config (export as .xml, make modifications, import to dnb, and restart)
Increase cache lifetime in server settings, by inserting:
<information-services> ... <jdbc> ... <cache-lifetime-seconds>20</cache-lifetime-seconds> </jdbc>
And you can also completely disable runtime query validation for most cases, by inserting:
Right before the close of </information-services>. Like:
RAM/CPU Utilization and Data Importing
And finally, are you using the default data source template with no modifications? These types of delays could be seen if you were importing the whole data set instead of streaming it, causing the JVM to be strained
An example of when we import instead of stream data is when the SQL runtime is removed from a data source template:
<sql-runtime> com.spotfire.ws.im.ds.sql.mysql.MySQLDriverSQLRuntime </sql-runtime>
Did you check your RAM utilization? Is the tomcat process RAM utilization pegged at the JvmMx value? is CPU high?
Sean ... the query is not very complex. Three tables, SUM functions, and Group By. I will open a Service Request with TIBCO. Appreciate all the help.
I observed SQL Activity Monitor and found a lot of waits and other activity when loading the Info Link
That is interesting. The waits are only seen when you execute that query via IL, and not when executed via DbVisualizer?
Do you have a lot of joins in your SQL or other more advanced elements? Can you share the SQL from your IL?
I might suggest you open a case with TIBCO Support at https://support.tibco.com, but you should include all the tests we went through here so they start at the same point : )
Thanks for another helpful reply, Sean. I reduced the Min and Max connections both to 0 and restarted the TSS service, and found no difference in performance, the Info Link is still taking over 7 minutes. As for server load, this is a Test server that is mostly idle except for my activity.
I observed SQL Activity Monitor and found a lot of waits and other activity when loading the Info Link. I also observed SQL Activity Monitor when using the Data Connection and found clear indication that it is using the columnstore index.
Also noticed that with the Info Link, SQL Server sees the incoming 'Application' as Microsoft JDBC Driver for SQL Server, and when using the Data Connection, SQL Server sees the incoming 'Application' as .Net SqlClient Data Provider.
OK, that is strange then... There are 2 other things I can think of:
- Try disable connection pooling on your data source in Spotfire and see if that makes a difference. In the Information Designer, edit the data source used for this IL. Set the Min and Max Connections to 0, and Save. You shouldn't need to restart anything, but it might not be a bad idea to restart the TSS, and then test the IL execution time again (in the sql.log)
- Is your TSS overloaded at the time of the test (RAM, CPU, disk space and or I/O)? Are there a number of other users using the system at that time? By default the JvmMx for Tomcat is 1.5GB, which is normally sufficient but if there are a lot of large ILs being opened and transformations performed, it is sometimes required to increase that. For example, if your TSS has 16GB of RAM, the TSS tomcat7.exe might take up to 1.5GB (with default configuration). Assuming you had 2.5GM of RAM free at all times, you could increase the JvmMx to 4gb (or 4096 specifically, ref: https://docs.tibco.com/pub/spotfire_server/7.6.0/doc/html/TIB_sfire_serv...). You don't want to make this setting arbitrarily large since that will mean the GC might start taking much longer which can cause performance issues, so you want this only as large as needed.
Sean, I followed your steps and installed dbVisualizer on the Spotfire Server. I set up a custom driver template using the sqljdbc4 driver from the /tomcat/lib/ directory, and the query returned results in about 2 seconds for 90k rows of aggregated data. That's the same performance I'm getting in both SSMS, and with the SQL Server 2012 jdbc driver.
Yet still, opening the info link in the Spotfire client takes over 7 minutes. We are on Spotfire 7.5 HF-005. I'm stumped.
Sorry, I meant sqljdbc4.jar! I would try a custom data source using that exact same driver then (pointing to the jar in tomcat\lib).
And your DbVisualizer is on the same machine as your TSS?
I used the SQL Server 2012 driver in dbVisualizer, and the query returned 90,000 rows almost instantaneously. I can try the custom data source idea you suggested, but in my /tomcat/lib/ folder I only have sqljdbc4, not sqljdbc6 ... could that be the root of my problem? If so, is it as simple as replacing the sqljdbc4.jar with the sqljdbc6.jar?
I'm running SSMS on my PC, not the same machine as Spotfire Server. Are you suggesting I install SSMS on the Spotfire Server and run the query there?
Hi, Ok, so that time difference is significant, and points to a driver difference being the root cause. I am not sure which datasource in DbVisualizer matches best with Spotfire there, but you can try the SQL Server 2012 (MS JDBC Driver) for your test. Even better, you can create a new data source using the exact same sqljdbc6.jar driver that Spotfire has in \tomcat\lib\. Here are some (now awfully formatted instructions for that):
Where is SSMS installed by the way? On the same machine as the Spotfire Server? That would be the best test (to eliminate any network connection differences between the tests)
Thank you very much for the prompt reply, Sean.
We use the SQL Server (2005 or newer) data source template. The SQL log shows the query taking 424 seconds using the Information Link. I then tried DataDirect, and it took 448 seconds. I paste that same query into SSMS, and it takes 2 seconds.
In the list below, does SQL Server 2012 (Microsoft JDBC Driver) match up to Spotfire's SQL Server (2005 or newer)?