Skip to Main Content
Spotfire Ideas Portal
Status Already exists
Product Spotfire
Categories Filters
Created by Guest
Created on Nov 11, 2016

Change the way Spotfire generates the inefficient query when filter is applied from one table to another table being used in the visualization.

The way Spotfire is designed, it generates a very inefficient SQL query in a scenario where a filter values from one table (having in-memory connection) are passed to main table (having in-database connection) which is being used in a visualization. There is a relationship defined between these two tables using a common key. Even though the filter panel (a list box) has 'All values' selected by default which essentially means no filter, Spotfire generates a SQL query that sends all the values (for an example, product ids) to the Where clause of the Main table. This should not be the case. It should simply return all records from the Main table. This would drastically improve the performance of the SQL query and the response time of Spotfire analysis.
Example, It generates the below query.
Select sum(SalesDollar) from Sales where product_id in (1,2, 3,.......................n)
instead of
Select sum(SalesDollar) from Sales

 

 

  • Attach files
  • Guest
    Reply
    |
    Feb 27, 2020

    Please try to change the Include Filtered Rows to Exclude Filtered Out in the filtering in related data tables setting.