[IGNITE-6877] Improve behaviour for non-correlated subqueries

• Type: Improvement
• Status: Reopened
• Priority: Minor
• Resolution: Won't Fix
• Affects Version/s: 2.3
• Component/s: sql
• Assignee: Unassigned
• Reporter: Alin Andrei Corodescu
• Created: 13/Nov/17 08:29
• Updated: 05/Jul/19 15:43
• Resolved: 18/Feb/19 07:14

Description

Ignite behaves poorly in terms of performance when given queries which contain IN or = operators followed by a non-correlated subquery. My guess is that the query is actually run for each row of the table in order to test the condition.
A possible solution is to store the results of the subquery in a temporary table, and use it from there.
A workaround at the moment is to use joins instead of IN or = operators, but this makes the query much more complicated.

Example:
SELECT name
FROM Employees
WHERE age IN (SELECT age FROM Customers)

The performance of this query is very slow for small amounts of data (about 20 seconds for 4000 rows in each table last time I tried)