[teiid-issues] [JBoss JIRA] (TEIID-2249) Enable the use of temporary tables for those data sources that support them instead of IN criteria for EDS

Enable the use of temporary tables for those data sources that support them instead of IN criteria for EDS ---------------------------------------------------------------------------------------------------------- Key: TEIID-2249 URL: https://issues.jboss.org/browse/TEIID-2249 Project: Teiid Issue Type: Feature Request Components: Query Engine Reporter: Debbie Steigner Assignee: Steven Hawkins [WoCollab -RFE] 2. Who is the customer behind the request? Account name: Royal Bank of Scotland Plc SRM customer: yes Strategic Customer: yes 3. What is the nature and description of the request? Our proposal is to allow for the more efficient use of large ad-hoc result-sets by rather than creating a long 'IN' list, inserting them in to a temporary table - for example a # table in Sybase and SQL Server - and then generating an SQL join to that instead. One of the difference to materialized views (or at least my understanding), is that this work happens at a data-source rather than within the Teiid server. 4. Why does the customer need this? (List the business requirements here) There are a number of possible optimizations, for example using bulk-loading, if available & appropriate and the creation of indexes. The secondary benefit of this approach is that it makes composite key-handling more straightforward and elegant. Curiously, SQL still doesn't appear to support composite 'IN' clauses, something like 'WHERE {currency,bookId} IN ( {'GBP',123}, {'USD,234'} ... ) 5. How would the customer like to achieve this? (List the functional requirements here) Creating ad-hoc temp tables on the database. 6. For each functional requirement listed in question 5, specify how Red Hat and the customer can test to confirm the requirement is successfully implemented. Check Query plan to see that the query is using the temp table instead of IN criteria list 7. Is there already an existing RFE upstream or in Red Hat bugzilla? No 8. Does the customer have any specific timeline dependencies? As soon as possible.