How to bring the database data faster in Spotfire?
Hello, I have few SQL's which takes more than 40 minutes to load the data into Spotfire. Database is SQL server.
The tables used in SQL's has a lot of data which is required to show on dashboard. I cannot avoid/manipulate the results.
Is there a way to bring the data faster in Spotfire? Below are the problems i am going through,
1) Performance Issue
2) Admin team needs to wait for long time for dashboard to open to apply schedule updates to quick access of the report in Web player.
3) SQL's execution time may impact other dashboard sitting on Prod environment.
Please suggest if there is a way to bring in the results quicker into dashboard.
Note: I have heard about ADS (Advanced Data Services). Per my knowledge, this would connect to database and create a view for Spotfire to leverage but i am not sure if this gives results quicker than normal appraoch i.e. Info link having SQL which connects to database to load the results in dashboard.
If the reports will be accessed in the Web Player you can use Scheduled Updates to load the report during a given schedule. It will be retained in the RAM and/or file cache and all requests will only use that cached version while that schedule is active. This way the SQL execution only needs to be run once at the start of the schedule.
Alternatively, you could also enable Information Link caching so that when the IL is executed, the results are cached for X amount of time (as configured on the TIBCO Spotfire Server Configuration Tool > Attachment Manager settings). That way you could reuse your SQL results multiple times without executing again.
Beyond that, it might just be that the database takes 40 minutes to return your data, in which case not much else can be done 'downstream' to improve that time. How long does it take to execute that query outside of Spotfire? How long does it take when using the same JDBC driver like in a tool like DbVisualizer?
Perhaps speak with your DBA to see if any indexing could help improve the query execution time, etc....
Thanks so much Sean for your valuable inputs.
1st one, i am aware of however does admin team needs to wait till dashboard open and then apply schedule updates or do we have an option to apply schedule updates without opening the dashboard?
2nd one, this sounds interesting, i never tried but have below questions around this,
a) If i set timeout as 86400 which is one day, after that, does it reset or expires completely. If it expires then do i need to manually go and reset? could you give me more info on Validation SQL query?
b) Does it Attachment Manager settings timeout over ride my time out i.e. 86400 as you mentioned (as configured on the TIBCO Spotfire Server Configuration Tool > Attachment Manager settings).
3) Third, i'll take a look at this and get back to you if i have any questions. Thanks.
IL caching is cached on disk on the TIBCO Spotfire Server temp directory (attachment manager)
"If not then do we have an option to apply schedule updates without opening the dashboard?"
nobody needs to open the dashboard to set up Scheduled Update. For setting up a new dxp to be cached with Scheduled Updates you can use the relevant configuration page which doesn't require you open the report first - you just open the configuration page, add the new report to be cache, set up the active times and the refresh frequency. As soon as you enter the active time, the report will start to load. If you need the report to be ready by 9AM and it usually takes ~40 minutes to load then you can set the active time from 8AM per say.
"does it immediately re-cache the results for another 24 hours after it expires or it re-caches when IL is triggered again after 24 hours and fetch results."
With IL caching it will not get automatically re-cached after the previous cache entry has expired - unless you combine this feature with automation services or scheduled updates. If you just think about IL caching on its own, all it does is that it check whether this has query has been cached before (cacheable checkbox) and if that cache entry is still valid (expiration time and/or validation query). If cacheable but no cache entry or only expired cache entry then it will fire it to the database, otherwise serve the result from cache.
Regarding your initial post about query performance, you didn't specify what is slow exactly, so just a couple of suggestions
- is it a view or a table?
- - If it's a view, doesn't it take long to generate the result set of the view and start streaming the data? If so, check the execution plan of the query and try to see if it can be optimised: hints, different ways of joining the data, with statements or temp tables. If the view is still not quick enough you can consider building a materialised view from it
- - If it's table, do you use filters which would make the query slower to response? If so, make sure you have an index on that column (index could be useful for columns used in joins in a view too)
- is it streaming the data which is slow?
- - consider using data connectors instead of infolinks. Infolink data will flow through the spotfire server, whereas data connector will download the data from the client directly
- - are the spotfire server(/client in case you already use data connectors) and the database in the same data centre or at least in the same geographical locations? what's the bandwidth and latency between the endpoints? Needless to say, the closer the servers are to each other or the bigger the bandwidth is, the faster the data stream will be
0 Comments+ Add a Comment
Response to comment on earlier answer (I meant to post it there):
For Scheduled Updates, you just define the schedule and it will cache with no user interaction. For example, you can tell it to cache from 9am-5pm. At 9am it will trigger a load automatically and will keep it in cache until 5pm.
For the IL caching, there is no scheduling, so it will only check/cache when you try and execute the IL. So if you open it the first time it will execute and tak 40 minutes but will cache the results. Then if you try again 1 hour later, the cache will still be valid and so the cached results will be returned. If you then try and access it again 25 hours after, the cache will be invalid and a new execution will occur, and those results will be cached again.
For the IL and Attachment manager timeouts, they are both active. So which ever is shorter will be hit first.
The validation query is just a way you to validate if a new execution should occur. For example your validation query could be somethin like "select count(*)" to see if more rows have been added since the cache results were created.
For Scheduled Updates, no action is required. It caches the reports on a schedule. You define the schedule in the Web Player GUI as an admin.
For IL caching, it will only recache when a new execution is requested. There is no 'scheduling' or 'automatic' recaching. The caching is only triggered by IL execution.
Thanks again for detailed information. Appreciated your patience and support here. Could you please clarify my last set of questions.
For schedule updates, I agree with you but i am just wondering, for very 1st time, does admin team needs to wait till dashboard open and then apply schedule updates? If yes then eventually they need to wait 40 minutes, once dashboard is opened then you set the timings. If not then do we have an option to apply schedule updates without opening the dashboard?
For IL caching, its clear however can you provide more info on this "If you then try and access it again 25 hours after, the cache will be invalid and a new execution will occur, and those results will be cached again." does it immediately re-cache the results for another 24 hours after it expires or it re-caches when IL is triggered again after 24 hours and fetch results.
Thanks for the clarifiation on Validation query.