Improving Information Link Data Retrieval

I have a data table whose source is an information link.  The data retrieved by this information link is dependent on 5 separate variables.
1. Start Date - A document property defined by an input field in a text area.
2. End Date - A document property defined by an input field in a text area.
3. Location - A multiple selection list box based on another table.  It is not a filter.  The selections in this box get appeneded to the end of the information link's query.
4. Product - A multiple selection list box based on another table.  It is not a filter.  The selections in this box get appeneded to the end of the information link's query.
5. Test - A multiple selection list box based on another table.  It is not a filter.  The selections in this box get appeneded to the end of the information link's query.

So, if my query is:
 

SELECT *
  FROM INFO A
 WHERE A.DATE BETWEEN ?BeginDate AND ?EndDate

After a user makes selections in the location, product, and text boxes as well as enters a date range, Spotifre edits the query and it becomes:
 

SELECT *
  FROM INFO A
 WHERE A.DATE BETWEEN '01-JUN-2017' AND '08-JUN-2017'
   AND A.LOCATION IN ('TX','MA','FL')
   AND A.PRODUCT IN ('ABC','DEF')
   AND A.TEST IN ('1V','2C','3C')

The query runs quickly when the user selects only a few items from the location, product, and test boxes as shown in the query above.

However, if the user selects all of the items in each of the location, product, and test boxes, Spotfire edits the queries to have huge lists and the query becomes very slow.  If one of the list boxes has more than 1000 items, Spotfire creates a cartesian type condition listing since there can't be more than 1000 items in a list.  This query almost always times out.

Is there a better, more efficient way to retrieve data from the database if the user wants to pull back all data instead of just a few items in each list box?

Thanks!

(1) Answer

Login